[MySql] Hoge serverload en IO reads / writes

[aOk]

We zijn al een behoorlijke tijd bezig met het optimaliseren van een MySQL-5 database, maar hebben hier weinig ervaring mee aangezien we meestal met MSSQL werken. De optimalisatie bestaat vooral uit het maken indexes en keys.

Maar het optimaliseren van de instellingen voor de database hebben we weinig kaas van gegeten.

De website heeft ondertussen 80000 unieke bezoekers en 2500000 hits in de maand.
Per pagina zijn er ongeveer 10 queries nodig, maar er word zoveel mogelijk gecached door IIS om zo databaseload te voorkomen en de pagina's snel te serveren voor de bezoekers.

De IIS server heeft het rustig met 5% CPU-load en 250.000 K memory usage, maar mysql heeft een CPU-load tussen de 50 en heel regelmatig 85% en heeft een enorm hoge I/O reads en writes namelijk 18.000.000.000 en 1.000.000.000 .

Verder is deze MySQL server een slave en word om het half uur gesynchroniseerd met z'n master.

De systeemspecificaties van de server zijn:
Microsoft Windows Server 2003 R2
Standard Edition
Service Pack 2

Intel(R) Xeon(R) CPU
E5335 @ 2.00 GHZ
2.00 GHz, 2,00 GB of RAM

De config file van MySql-5 bestaan onder andere uit:

code:

default-storage-engine=MYISAM
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=200
query_cache_size=256M
query_cache_limit=20M
table_cache=1600
tmp_table_size=267M
thread_cache_size=120

Verder komt de volgende foutmelding vaak naar voren in de error-logs van de server:
[ERROR] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Can't open file: '.\decontent\vw_zm_url.frm' (errno: 24)

(Dit betekend dat er teveel bestanden open zijn)

Wat zouden we kunnen optimaliseren aan de MySQL-settings om ervoor te zorgen dat de CPU-load en I/O reads en writes lager worden? Ook andere suggesties zijn welkom.

[ Voor 1% gewijzigd door aOk op 22-06-2009 13:10 . Reden: beschrijving uitbreiden ]

[BarthezZ]

Slow query logging enablen en kijken welke query's _geen_ indexes gebruiken en dergelijke. En aan de hand daarvan proberen te optimaliseren. Ook vooral proberen meer file descriptors te assignen (errno 24)

http://gert.sos.be/nl/articles/mysql_tuning/
Slow Queries

Om de meest problematische queries te identificeren biedt MySQL de mogelijkheid om deze te loggen naar een bestand. Standaard is een query slow als hij meer dan 10 seconden nodig heeft om uitgevoerd te worden, maar dit kan je wijzigen.

Door volgende regels aan je my.cnf bestand toe te voegen in de mysqld sectie, activeer je de slow query log en definieer je een query als slow indien hij meer dan 8 seconden duurt.

log-slow-queries=/var/log/mysql-slow.log
set-variable = long_query_time=8