Page 1 of 1
MySQL Performance Problem
Posted: 2011-05-24 20:44
by niemandwichtiges
Hi Zusammen,
ich habe gerade ein extremes Performanceproblem mit einer MySQL Datenbank.
Ich verwende innoDB und habe Tabellen, die ziemlich klein sind (die grösste mit 200 Einträgen).
Ich habe Indexe gesetzt, und die select Queries laufen auch super schnell.
Aktuell ist der Load auf dem System 0 (Testumgebung) und ich bin der einzige Nutzer.
Das Problem ist bei Updates/Inserts/Deletes - also schreibender Zugriff.
Mache ich ein einfaches
dauert der Query rekordverdächtige 3,8 Sekunden ! In dieser Tabelle sind aktuell nur 183 Einträge mit einem Index auf der Spalte y.
Während des Queries geht der Load auch auf etwa 0.5 hoch.
Führe ich den selben Query anschliessend erneut aus, dauert er nur wenige Millisekunden und wird scheinbar gecached.
Laufen andere Update Queries zwischendurch, komme ich bei dem Query wieder auf meine knappen 4 sekunden.
Auch interessant: Führe ich die Queries nicht über mein PHP Script, sondern über PHPMyAdmin aus, dauern sie ebenfalls nur wenige Millisekunden.
Hat jemand Rat, wie ich das Problem angehe bzw. den Fehler finde ?
MySQL Version: 5.0.51a-24+lenny5-log
Re: MySQL Performance Problem
Posted: 2011-05-24 22:21
by Joe User
Wie sieht denn Deins entsprechende PHP-Funktion/Klasse aus? URL zur phpinfo?
Re: MySQL Performance Problem
Posted: 2011-05-25 07:43
by niemandwichtiges
Guten Morgen,
es handelt sich um einen vServer, von daher komme ich leider nichts auf die Platte, um den Durchsatz zu messen.
Normalerweise mache ich sowas ja mit
allerdings bekomme ich da ein Permission denied (als root).
Auf der Kiste läuft ein Apache2 Mod SSL, Postfix, Cyrus und policy-weightd.
Das wars dann eigentlich schon.
Mich wundert es am meisten, dass der PHP Code auf diesem Server seit ca. 1 Jahr problemlos und schnell lief, und nun erst solche Probleme auftreten, was tatsächlich für einen Engpass auf dem System sprechen könnte.
Was ich gestern bei einigen Versuchen noch herausgefunden habe:
Verwende ich nicht meine MySQL Zugriffsklasse, sondern mache die Verbindung manuell im Code auf, so bekomme ich ab dem 2.Versuch wieder eine normale Performance.
Aktuell setze ich meine Statements vom Script folgendermassen ab:
Code: Select all
$db=new mysql();
$res=$db->sql_query("delete from x where y='z'");
$db->close();
Die Zugriffsklasse sieht vereinfacht etwa so aus:
Code: Select all
class mysql {
var $verbindung;
function mysql() {
$this->verbindung = @mysql_connect('localhost','xx','yy');
if ($this->verbindung === FALSE) {
$this->error("CONN.OPEN");
die();
}
if (@mysql_select_db('xx', $this->verbindung) === FALSE) {
$this->error("DB.SELECT");
die();
}
}
function sql_query($sql) {
if (!$this->connected) {
$this->error("NO CONN");
die();
} else {
$result = mysql_query($sql, $this->verbindung);
if ($result===FALSE) {
// Fehlerbehandlung
}
return $result;
}
}
function close() {
@mysql_close($this->verbindung);
}
Re: MySQL Performance Problem
Posted: 2011-05-25 20:28
by niemandwichtiges
Ich habe eben den Benchmark der Festplatte gemessen.
Diese hatte lesend wie schreibend etwa 50 MB/s. Ich denke das sollte also nicht der Flaschenhals sein.
Ich habe inzwischen auch ein wahnsinnig unstetes Verhalten:
Manchmal dauert ein schreibender Query wenige Millisekunden, und kurz darauf dauert der selbe Query über 30 Sekunden !
In den Wartezeiten bis der Query fertig ist, steigt auch der Load auf dem Server stark an (von 0.10 auf ca. 1.00).
Ich bin völlig ratlos... :-?
Re: MySQL Performance Problem
Posted: 2011-05-25 20:47
by Joe User
Externes Backup anlegen und anschliessend per Rescue-System die Filesysteme prüfen. Wenn diese nicht Fehlerhaft sind, Deinen Anbieter darum bitten, Deine VM auf einen weniger belasteten Host zu verschieben.
Re: MySQL Performance Problem
Posted: 2011-05-25 20:55
by jan10001
Wieso verwendest du InnoDB, wenn du keine Transaktionen nutzt? Es könnte sein das einfach die Pfuffer sowie die Logs überlaufen, das würde solch ein Verhalten erklären.
Re: MySQL Performance Problem
Posted: 2011-05-25 22:44
by jan10001
@matzewe01
Eine Datenbank die Transaktionen unterstützt speichert alle Updates, Insert oder Delete Anweisungen normalerweise zwischen und führt diese erst engültig aus wenn man dies mit commit bestätigt. Können die Anweisungen bei einen Commit nicht alle ausgeführt werden, wird ein Rollback gemacht. Nun stellt sich hier die Frage was passiert eigentlich bei MySQL's InnoDB arbeitet die DB nur noch mit dem "Zwischenspeicher" oder führt sie ein autocommit aus und wenn ja wann eigentlich?
Re: MySQL Performance Problem
Posted: 2011-05-25 23:13
by Joe User
Re: MySQL Performance Problem
Posted: 2011-05-26 07:37
by niemandwichtiges
innoDB benötige ich, da nachts einige Batches auf den Tabellen laufen, die Transaktionen benötigen. Ich überlege aber gerade, die Tabellen am WOchenende mal testweise auf myIsam umzustellen.
Die Eckdaten des vServers sind gar nicht soo schlecht:
- Prozessor: 3GhZ
- Arbeitsspeicher garantiert: 1.024 MB
- FlexRAM garantiert: 2.048 MB
Re: MySQL Performance Problem
Posted: 2011-05-26 17:22
by niemandwichtiges
Ich habe die letzten Tage etwas an der my.cnf runexperimentiert, und bin inzwischen bei folgender Configuration, die am performantesten (sofern man das Wort dafür überhaupt benutzen sollte) läuft, gelandet:
Code: Select all
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
skip-bdb
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
key_buffer = 128M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 32
thread_concurrency = 2
#max_connections = 100
table_cache = 512
table_cache = 64
query_cache_limit = 512K
query_cache_size = 8M
#log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
skip-bdb
[mysqldump]
quick
quote-names
max_allowed_packet = 4M
[isamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
Re: MySQL Performance Problem
Posted: 2011-05-26 18:25
by Joe User
Sauberes Backup aller DBs anlegen:
Code: Select all
mysqlcheck --check --auto-repair --extend --all-databases -uroot -p
mysqlcheck --optimize --all-databases -uroot -p
mysqldump --flush-logs --master-data --lock-all-tables --delete-master-logs --all-databases -uroot -p > /root/mysqldump.sql
MySQLd stoppen, /var/lib/mysql leeren, neue my.cnf anlegen (Pfade anpassen!):
Code: Select all
[client]
port = 3306
[mysql]
prompt = \u@\h [\d]>\_
no_auto_rehash
[mysqld]
user = mysql
port = 3306
bind-address = 127.0.0.1
basedir = /usr/local
datadir = /var/db/mysql
tmpdir = /var/tmp
slave-load-tmpdir = /var/tmp
log-bin = /var/db/mysql/mysql-bin
relay-log = /var/db/mysql/relay.log
relay-log-index = /var/db/mysql/relay.index
relay-log-info-file = /var/db/mysql/relay.info
master-info-file = /var/db/mysql/master.info
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = 3306
#auto_increment_increment = 10
#auto_increment_offset = 1
server-id = 1
back_log = 50
sync_binlog = 1
binlog_cache_size = 1M
max_binlog_size = 100M
binlog-format = MIXED
expire_logs_days = 7
slow-query-log = 1
slow-query-log-file = /var/db/mysql/slow-query.log
slave_compressed_protocol = 1
lower_case_table_names = 1
safe-user-create = 1
delay-key-write = ALL
myisam-recover = FORCE,BACKUP
key_buffer_size = 16M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 16M
max_allowed_packet = 16M
max_heap_table_size = 16M
tmp_table_size = 16M
table_cache = 512
table_definition_cache = 512
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 2M
thread_concurrency = 2
thread_cache_size = 8
max_connections = 16
ft_max_word_len = 20
ft_min_word_len = 3
long_query_time = 3
local-infile = 0
log-warnings = 2
log-slave-updates
log-queries-not-using-indexes
skip-external-locking
innodb_thread_concurrency = 2
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 8M
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:200M;ibdata2:10M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_file_size = 64M
innodb_log_buffer_size = 4M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
[mysqldump]
max_allowed_packet = 16M
quote_names
quick
[isamchk]
key_buffer_size = 16M
[myisamchk]
key_buffer_size = 16M
[mysqlhotcopy]
interactive_timeout
MySQLd starten und ein paar Minuten warten bis die InnoDB-Files vollständig erzeugt wurden. mysql_install_db und mysql_secure_installation ausführen und anschliessend das Backup zurückspielen.
Code: Select all
mysql_install_db
mysql_secure_installation
mysql -uroot -p < /root/mysqldump.sql
Danach sollte der MySQLd etwas ruhiger laufen, da nun auch InnoDB konfiguriert ist. Wenn möglich circa 48h durchlaufen lassen und dann mittels
mysqltuner.pl und
tuning-primer.sh Feedback geben. Sollte es vorher wieder laut knallen, würde ich Dir erstmal zu einem anderen VServer raten.
Hoffe, ich habe keinen Schritt vergessen :-ss
Re: MySQL Performance Problem
Posted: 2011-05-26 20:29
by niemandwichtiges
Hallo Joe User,
vielen Dank für Deine ausführliche Anleitung, die ich Schritt für Schritt befolgt habe.
Leider hat es keine Besserung gebracht und ich habe nach wie vor zwischen 0-30 Sekunden je Query.
Ich habe die Tabellen eben spasseshalber mal auf MyISAM umgestellt - und hui, jeder schreibende Query dauert nur Millisekunden.
Es scheint also tatsächlich ein Problem mit innoDB bzw. innoDB auf dem vServer zu sein :-?
Re: MySQL Performance Problem
Posted: 2011-05-27 17:13
by niemandwichtiges
Ich habe meinen Serveranbieter um einen Umzug auf einen weniger frequentierten Server gebeten.
Die Antwort ist, dass wohl eine Datei /proc/user_beancounters existiert, die Überschreitungen der zugeteilten Ressourcen anzeigt.
Bei mir seien folgende Werte überschritten gewesen:
resource held maxheld barrier limit failcnt
lockedpages 0 104 344 344 12
tcpsndbuf 280624 2453448 2449232 3598712 6165
numfile 2129 6000 6000 6000 14816461
Die gesamte Ausgabe sieht so aus:
Code: Select all
cat /proc/user_beancounters
Version: 2.5
uid resource held maxheld barrier limit failcnt
10001952: kmemsize 13551860 25413453 49029120 54476800 0
lockedpages 1 104 344 344 12
privvmpages 140013 162470 524288 550502 0
shmpages 743 28399 32768 32768 6
dummy 0 0 9223372036854775807 9223372036854775807 0
numproc 68 107 256 256 0
physpages 61156 84916 0 9223372036854775807 0
vmguarpages 0 0 262144 9223372036854775807 0
oomguarpages 61156 84916 9223372036854775807 9223372036854775807 0
numtcpsock 26 143 288 288 0
numflock 10 43 224 246 0
numpty 1 5 32 32 0
numsiginfo 0 46 512 512 0
tcpsndbuf 301016 2453448 2449232 3598712 6165
tcprcvbuf 304784 2393344 2449232 3598712 0
othersockbuf 173296 602600 744366 1481926 0
dgramrcvbuf 0 25696 844366 844366 0
numothersock 127 251 288 288 0
dcachesize 1193469 1423524 7299072 8110080 0
numfile 2605 6000 6000 6000 14816461
dummy 0 0 0 0 0
dummy 0 0 0 0 0
dummy 0 0 0 0 0
numiptent 10 10 96 96 0
Kann damit jemand etwas anfangen ?
Re: MySQL Performance Problem
Posted: 2011-05-27 17:37
by Joe User
Ein Limit von 6000 Files ist mit Verlaub ein Witz. Es wird Zeit den Anbieter zu wechseln...
'Nuff said!