”Sivusto takkuaa” päätyy lähes aina tietokantaan. Tarkistus vie muutaman minuutin, jos se tehdään oikeassa järjestyksessä: ensin se, mikä näkyy juuri hidastumisen aikana, sitten se, mikä on kertynyt viikkojen mittaan.

Katsoa pitää ongelman hetkellä

Pääsääntö: arvokkain tieto on saatavilla juuri silloin, kun kaikki on hidasta. Ensimmäinen komento MySQL:lle:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

PostgreSQL:lle:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Näet, mitä suoritetaan juuri nyt ja kuinka kauan. Vastaus näkyy yleensä heti: yksi raskas kysely pidättelee muita, tai sata samanlaista kyselyä odottaa lukon vapautumista.

Yhteydet

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Jos Max_used_connections on aivan lähellä arvoa max_connections, sovellus saa ajoittain vastauksen ”liian monta yhteyttä”. Rajan nostaminen ei ole ensimmäinen tehtävä: jokainen yhteys vie muistia, ja rajan nostaminen palvelimella, jolta muisti jo loppuu, vain nopeuttaa siirtymistä sivutukseen. Ensin kannattaa ymmärtää, miksi yhteyksiä ei vapauteta: yleensä syynä on pitkä kysely tai altaan puuttuminen sovelluksen puolelta.

Tässä on hyödyllistä katsoa asiaa myös toiselta puolelta: yhteyksien jyrkkä kasvu voi johtua siitä, että botit käyvät joukolla raskaalla sivulla — haussa, luettelon suodattimessa, raporttigeneraattorissa — eikä suosion kasvusta.

Hitaat kyselyt

Ilman hitaiden kyselyiden lokia eteenpäin ei pääse. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Vuorokauden kuluttua:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, tiedostossa postgresql.conf:

log_min_duration_statement = 1000

Sitten laajennus pg_stat_statements, joka näyttää heti kunkin kyselyn kokonaisajan:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Huomaa: tärkeä ei ole pisin kysely vaan se, jolla on suurin kokonaisaika. 50 millisekunnin kysely, joka suoritetaan tuhat kertaa minuutissa, haittaa enemmän kuin kymmenen sekunnin raportti kerran vuorokaudessa.

Asetus, joka useimmin unohtuu

MySQL:ssä ja MariaDB:ssä se on InnoDB-puskurialtaan koko. Oletusarvo on 128 megatavua eikä se ole muuttunut vuosikymmeniin. Palvelimella, jolla tietokanta on useita gigatavuja, se tarkoittaa jatkuvaa lukemista levyltä muistin sijaan.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Järkevä mittapuu omistetulle tietokantapalvelimelle on puolet työmuistista; palvelimelle, jolla vieressä toimivat verkkopalvelin ja PHP, neljännes, silmällä pitäen ettei sivutus käynnisty. Tuo yksi asetus antaa yleensä enemmän kuin viikko kyselyiden optimointia.

Kaksi asiaa, jotka syövät levyä huomaamatta

MySQL:n binäärilokit. Niitä tarvitaan replikointiin ja ajanhetkeen palautukseen, ne kasvavat keskeytyksettä eikä niitä oletuksena toisinaan poisteta lainkaan. Tarkistus:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Jos replikointia ei ole eikä ajanhetkeen palautusta käytetä, säilytysaika asetetaan järkeviin muutamaan vuorokauteen.

PostgreSQL:n WAL-lokit unohtuneen replikointipaikan takia. Vähemmän tunnettu ja vaarallisempi tapaus: jos replikointipaikka on luotu eikä kuluttaja enää yhdistä siihen, PostgreSQL on velvollinen säilyttämään kaikki lokit katkaisusta alkaen — ja säilyttää ne, kunnes levy loppuu.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Epäaktiivinen paikka, jota kukaan ei tarvitse, on poistettava. Tämä on yksi harvoista tilanteista, joissa tietokanta varmuudella ajaa levyn nollaan.

Koko ja kasvu

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Hyödyllinen ei ole niinkään nykyinen luku kuin sen muutosvauhti. Tietokanta, joka on kaksinkertaistunut kuukaudessa ilman vastaavaa kävijämäärän kasvua, tarkoittaa yleensä että jokin lokitaulu kirjoittuu eikä sitä siivota — istunnot, tehtävähistoria, laajennuksen tapahtumaloki.

Turvallisuus kahdella rivillä

Kun kerran avasit tietokantakonsolin, kaksi asiaa kannattaa tarkistaa. Ensimmäinen: onko tietokanta tavoitettavissa ulkoa (ss -tulpn | grep 3306) — ulospäin sen ei pidä näkyä lähes koskaan. Toinen: tilit, joilla on pääsy mistä tahansa osoitteesta:

mysql -e "SELECT user, host FROM mysql.user"

Merkinnät, joissa on host = '%', tarkoittavat ”yhdistää voi mistä tahansa”. Jos portti on lisäksi auki, tietokannan ja internetin välissä on vain salasana.

Varmuuskopiot

Viimeinen mainitsemisen arvoinen asia. Varmuuskopiotiedoston olemassaolo ei takaa mitään — vain läpiviety palautus takaa. Vedos, jota ei ole koskaan palautettu, voi yhtä hyvin olla katkennut, lukitusvirheellä otettu tai sisältää väärän tietokannan. Tämä on tarkistettava etukäteen ja erillisellä koneella, ei sinä päivänä kun kopiota tarvitaan.

Päivittäiseen seurantaan riittää kolme lukua: yhteyksien määrä, hitaiden kyselyiden määrä vuorokaudessa ja tietokannan koko. Kaikki kolme muuttuvat hitaasti ja ennustettavasti, ja jokainen jyrkkä muutos ansaitsee katseen. Miltä ne näyttävät yhdellä sivulla, näkyy alla olevassa esittelyssä.