Database Infrastructure SOP

From FedoraProject

(Difference between revisions)
Jump to: navigation, search
(Imported from MoinMoin)
 
(redirect page to new infra-docs)
 
(11 intermediate revisions by 4 users not shown)
Line 1: Line 1:
= Database - SOP =
+
{{header|infra}}
 +
{{shortcut|ISOP:DB}}
  
== Contact Information ==
 
Owner: Fedora Infrastructure Team
 
  
Contact: #fedora-admin, sysadmin-main, sysadmin-dba group
+
This SOP has moved to the fedora Infrastructure SOP git repo. Please see the current document at: http://infrastructure.fedoraproject.org/infra/docs/database.txt
  
Location: Phoenix
+
For changes, questions or comments, please contact anyone in the Fedora Infrastructure team.
  
Servers: db1, db2
 
  
Purpose: Provides database connection to many of our apps.
+
[[Category:Infrastructure SOPs]]
 
+
== Description ==
+
 
+
db1 and db2 are our primary database servers.  db1 contains the MySQL instance, db2 contains postgresql.  Each database server replicates to itself and the other through a dump style backup.  In a normal situation, db1 runs only MySQL, not postgresql.  While db2 runs only postgresql not MySQL.  Which is running on which is defined in the puppet configs, specifically the node manifest for each server (nodes/db1.fedora.phx.redhat.com.pp and nodes/db2.fedora.phx.redhat.com.pp)
+
 
+
== Troubleshooting and Resolution ==
+
 
+
=== Connection issues ===
+
There are no known outstanding issues with the database itself.  Remember that every time either database is restarted, services will have to be restarted (see below).
+
 
+
=== Some useful queries ===
+
 
+
==== What queries are running ====
+
This can help you find out what queries are cuurently running on the server::
+
<pre>
+
select datname, procpid, query_start, backend_start, current_query from
+
pg_stat_activity where current_query != '<IDLE>' order by query_start;
+
</pre>
+
 
+
This can help you find how many connections to the db server are for each individual database::
+
<pre>
+
select datname, count(datname) from pg_stat_activity group by datname
+
order by count desc;
+
</pre>
+
 
+
==== Seeing how "dirty" a table is ====
+
We've added a function from postgres's contrib directory to tell how dirty a table is.  By dirty we mean, how many tuples are active, how many have been marked as having old data (and therefore "dead") and how much free space is allocated to the table but not used.
+
<pre>
+
\c fas2
+
\x
+
select * from pgstattuple('visit_identity');
+
table_len          | 425984
+
tuple_count        | 580
+
tuple_len          | 46977
+
tuple_percent      | 11.03
+
dead_tuple_count  | 68
+
dead_tuple_len    | 5508
+
dead_tuple_percent | 1.29
+
free_space        | 352420
+
free_percent      | 82.73
+
\x
+
</pre>
+
 
+
Vacuum should clear out dead_tuples.  Only a vacuum full, which will lock the table and therefore should be avoided, will clear out free space.
+
==== XID Wraparound ====
+
Find out how close we are to having to perform a vacuum of a database (as opposed to individual tables of the db).  We should schedule a vacuum when about 50% of the transaction ids have been used (approximately 530,000,000 xids):
+
<pre>
+
select datname, age(datfrozenxid), pow(2, 31) - age(datfrozenxid) as xids_remaining
+
from pg_database order by xids_remaining;
+
</pre>
+
Information on [http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND wraparound]
+
 
+
=== Restart Procedure ===
+
If the database server needs to be restarted it should come back on it's own.  Otherwise each service on it can be restarted:
+
 
+
<pre>
+
service mysqld restart
+
service postgresql restart
+
</pre>
+
 
+
==== Koji ====
+
Any time postgreql is restarted, koji needs to be restarted.  Please also see [[Infrastructure/SOP/BuildSystem|  Restarting Koji]]  
+
 
+
==== Mirror Manager ====
+
Anytime postgresql is restarted Mirror Manager will need to be restarted, no SOP currently exists for this
+
 
+
==== Bodhi ====
+
Anytime postgresql is restarted Bodhi will need to be restarted no sop currently exists for this.
+
 
+
==== Smolt ====
+
Anytime MySQL is restarted, Smolt will need to be restarted.  no SOP currently exists for this
+
 
+
== Note about TurboGears and MySQL ==
+
 
+
{{ Template:message/note |  There's a known bug in !TurboGears that causes MySQL clients not to automatically reconnect when lost.  Typically a restart of the !TurboGears application will correct this issue.
+

Latest revision as of 03:35, 19 December 2011

Infrastructure InfrastructureTeamN1.png
Shortcut:
ISOP:DB


This SOP has moved to the fedora Infrastructure SOP git repo. Please see the current document at: http://infrastructure.fedoraproject.org/infra/docs/database.txt

For changes, questions or comments, please contact anyone in the Fedora Infrastructure team.