MySQL Performance Problem

[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

Code: Select all

delete from x where y='abc'

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

[Joe User]

Wie sieht denn Deins entsprechende PHP-Funktion/Klasse aus? URL zur phpinfo?

[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

Code: Select all

hdparm -Tt /dev/vzfs

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);
	}

[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... :-?

[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.

[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.

[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?

[Joe User]

http://dev.mysql.com/doc/refman/5.1/de/ ... ommit.html

[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

[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

[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

[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 :-?

[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 ?

[Joe User]

Ein Limit von 6000 Files ist mit Verlaub ein Witz. Es wird Zeit den Anbieter zu wechseln...

'Nuff said!