Mysql IO

[homeserver]

Moin moin alle zusammen,

Ich bin in ein Problem mit MySQL gerannt.
Und zwar bin ich langsam mit meinem Latein am Ende.
Mein Mysql-Server verursacht auf meinem Server 99% des I/O Loads und der I/O-Load des Servers liegt laut gstat bei beiden verbauten Platten (Raid1) zwischen 80 und 140% (!).
Dementsprechend langsam reagieren sämtliche Webseiten und MySQL Abfragen. Oft reageirt auch die Shell nurnoch träge.

Leider finde ich die Ursache für den hohen Load nicht

Skripte wie mysqltuner und tuning-primer liefern, dass meine MySQL-config eigentlich ok sein sollte. Auch das testen verschiedener Werte in der config hat bisher nicht viel gebracht.

Meine aktuelle config ist folgende:

key_buffer_size = 16M
max_allowed_packet = 16M
table_cache= 16M
table_open_cache = 6400
table_definition_cache = 1000
sort_buffer_size = 8M
net_buffer_length = 80K
read_buffer_size = 8M
read_rnd_buffer_size = 5120K
myisam_sort_buffer_size = 16M
local-infile = 0
character-set-server = utf8
collation-server = utf8_general_ci
query_cache_size = 16M
thread_cache_size = 16M
log_slow_queries=/var/log/slow-query.log
long_query_time=10
log_queries_not_using_indexes=YES

Hat jemand einen Ansatz/eine Idee für mich?

vielen Dank!

 

[d4f]

Ich rate mal ins Blaue und gehe davon aus dass die Anzahl der Queries sowie deren Inhalt das nicht verursachen sollte?

Davon ausgehend dass Mysql auf ext4 läuft, testweise atime und barrier abschalten, diese verursachen mit Mysql lahmes Verhalten:

mount -o remount,noatime,nodiratime,barrier=0 /

Bitte vor der Anwendung über Risiken von barrier=0 im Klaren sein.

 

[Joe User]

Hat jemand einen Ansatz/eine Idee für mich?

Ja, lies erstmal die MySQL-Doku und bringe Deine my.cnf entsprechend der Doku auf einen akzeptablen Zustand. Aktuell zeugt Deine my.cnf von absoluter Ahnungslosigkeit und "Optimierungen" irgendwelcher idiotischer Gamer...

Ich rate mal ins Blaue und gehe davon aus dass die Anzahl der Queries sowie deren Inhalt das nicht verursachen sollte?

Kann man bei illegalen Gameservern nie ausschliessen.

Davon ausgehend dass Mysql auf ext4 läuft

Nope, er nutzt FreeBSD für seinen illegalen Gameserver und somit kein EXT4.

 

[Thunderbyte]

Ja, lies erstmal die MySQL-Doku und bringe Deine my.cnf entsprechend der Doku auf einen akzeptablen Zustand. Aktuell zeugt Deine my.cnf von absoluter Ahnungslosigkeit und "Optimierungen" irgendwelcher idiotischer Gamer...

Das ist nicht wirklich zielführend. Wie wärs mit detaillierteren Ansatzpunkten und Verbesserungsvorschlägen?

Kann man bei illegalen Gameservern nie ausschliessen.

Woran machst Du das fest? Wenn dem so wäre, wäre das ein Grund für das Ende dieses Threads.

Nope, er nutzt FreeBSD für seinen illegalen Gameserver und somit kein EXT4.

Wenn Du einen begründeten Verdacht hast, dann gibt es dafür die Meldung des Posts.

EDIT: Geht es um einen Metin2 Gameserver?

 

[homeserver]

Hi,

ich kann dich beruhigen, dass ich keinerlei Gameserver betreibe, erst recht keine illegalen
Der MySQl-Server verwaltet nur Datenbanken für Webseiten und einen Mailserver.

Und die my.cnf ist in der Tat sehr wirr, da ich einige Werte durchgetestet habe, ob eine Besserung eintritt.
Da der I/O sehr hoch war, habe ich eben versucht die Caches zu erhöhen und zu schauen, ob es etwas bringt. Bin leider nicht als Unix-Guru auf die Welt gekommen...

Wie auch immer, mit den Werten der my-large.cnf läuft der Server etwas schneller, jedoch immer noch nicht zu meiner Zufriedenheit. Werde jetzt erstmal bissel lesen und warten, bis mir mysql_tuner verlässliche Werte liefert, bisher sagt er, alles wäre ok.

In diesem Sinne danke für die gut gemeinten Lösungsvorschläge(d4f) und Unterstellungen (Joe User)

Edit:
Ich gehe eigentlich immer noch davon aus, dass irgendwelche Cache-Werte nicht passen und daher bei einigen Queries ständig auf der HDD gerödelt wird.
Es ist auf jeden Fall MySQL die Fehlerquelle, ist der Dienst deaktiviert, sind alle I/O-Werte im Bereich weniger Prozent, sobald MySQL gestartet ist schnellt der I/O innerhalb weniger Sekunden (die ersten Abfragen gehen problemlos durch) auf 100%+ und es werden 10000-30000kBps rumgeschaufelt. Ich habe den Eidnruck, dass der Dienst mit der Laufzeit träger wird.

Edit2:
Wie es ausschaut haben auch die Standardwerte der my-large.cnf keine Verbesserugn gebracht, momentan reagiert mysql quasi gar nicht mehr. Das will mir bei den Werten nicht so richtig in den Kopf, vielleicht scheint der Fehler doch einfach woanders als in der my.cnf zu liegen.

Gruß homeserver

 

[Joe User]

EDIT: Geht es um einen Metin2 Gameserver?

Ja, wobei sein entsprechender SSF-Thread nicht mehr öffentlich zugänglich ist beziehungsweise nicht mehr existiert.

 

[homeserver]

Hallo,

also ganz ehrlich? Wer hat dir denn heute in die Suppe gespuckt?

Zur endgültigen Klarstellung:
- Ich betreibe keinen Gameserver und habe es nie getan.
- Ich habe hier nie einen Gameserver-Thread eröffnet, der irgendwie gelöscht worden wäre.
- Ich habe nie auch nur einmal Metin 2 gespielt.

Wie auch immer:
Ich habe einen Ansatz für meinen MySQL-Server, der lediglich Datenbanken für einen Mailserver und Webseiten bereitstellt gefunden:

Die innodb_buffer_pool_size ist ggf. merklich zu gering. Laut MySQL-Doku sollte sie bei mir wohl merklich höher liegen, ich werde damit jetzt mal rumtesten und dann mein Ergebnis zu besten geben.

In diesem Sinne, danke, für nichts!

Edit: Ich bitte dich, mir hier jetzt am besten öffentlich (ansonsten gerne per PN) einen Beweis vorzulegen, der mich irgendwie mit Gameservern oder illegalen Metin2 Servern in Verbindung bringt. Ich warte, es gibt keine!

 

[Joe User]

OK, hier muss ich mich nun doch ausdrücklich entschuldigen, ich habe leider zwei Nicks und obendrauf zwei Foren verhexelt :-(

Entschuldigung homeserver, das hätte mir nicht passieren dürfen.

 

[homeserver]

Hallo,

kein Problem, jeder macht mal Fehler, und ich bin nicht nachtragend

Gruß homeserver

 

[homeserver]

Jetzt nochmal eben b2t:

Ich habe die innodb_buffer_pool_size mal testweise auf 1G gesetzt (16GB vorhanden). Bringt leider keine Besserung und wird nicht komplett genutzt.

Ich habe nun folgende my.cnf Daten:

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


innodb_buffer_pool_size = 1G

Wenn ich den MySQL-Dienst neustarte, ist er für wenige Sekunden schnell, dann geht die I/O Last wieder in die Höhe und fast nix geht mehr.
Interessant ist ebenfalls, dass dieses Problem quasi über Nacht aufgetaucht ist, zuvor lief der Server ordentlich (ab und zu gab es schon Hänger).

Gruß homeserver

 

[Joe User]

Um Dir auch zu helfen, nachfolgend (m)eine Standard-my.cnf und step-by-step das Vorgehen zum Einspielen. Bitte die Pfade in der my.cnf selbst ans eigene System anpassen.

Dump der DBs anlegen
MySQLd stoppen
ibdata* und ib_logfile* löschen
my.cnf anpassen:

[client]
port                            = 3306
socket                          = /tmp/mysql.sock
max_allowed_packet              = 64M

[mysql]
prompt                          = \u@\h [\d]>\_
max_allowed_packet              = 64M
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /data/db/mysql
tmpdir                          = /data/tmp/mysql
slave-load-tmpdir               = /data/tmp/mysql
secure-file-priv                = /data/tmp/mysql
log-bin                         = /data/db/mysql/mysql-bin
relay-log                       = /data/db/mysql/relay.log
relay-log-index                 = /data/db/mysql/relay.index
relay-log-info-file             = /data/db/mysql/relay.info
master-info-file                = /data/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                        = 500
sync_binlog                     = 1
binlog_cache_size               = 4M
binlog_stmt_cache_size          = 4M
max_binlog_size                 = 500M
binlog-format                   = MIXED
expire_logs_days                = 30
slow-query-log                  = 1
slow-query-log-file             = /data/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                 = 128M
#join_buffer_size                = 128K
#sort_buffer_size                = 2M
#read_buffer_size                = 128K
#read_rnd_buffer_size            = 256K
#bulk_insert_buffer_size         = 8M
#myisam_sort_buffer_size         = 8M
max_allowed_packet              = 64M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
thread_stack                    = 192K
table_cache                     = 2048
table_open_cache                = 2048
table_definition_cache          = 2048
net_retry_count                 = 16384
query_cache_type                = 1
query_cache_size                = 64M
query_cache_limit               = 4M
query_cache_min_res_unit        = 4K
thread_cache_size               = 80
max_connections                 = 100
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 1
local-infile                    = 0
log-warnings                    = 2
log-slave-updates
log-queries-not-using-indexes
skip-external-locking
skip-symbolic-links
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 4G
innodb_buffer_pool_instances    = 4
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir            = /data/db/mysql
innodb_log_group_home_dir       = /data/db/mysql
innodb_data_file_path           = ibdata1:2000M;ibdata2:2000M;ibdata3:10M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_file_per_table           = 1
innodb_purge_threads            = 1
innodb_strict_mode              = 1
innodb_old_blocks_time          = 1000
innodb_stats_on_metadata        = 0
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_io_capacity              = 200
innodb_open_files               = 8192

[mysqldump]
max_allowed_packet              = 64M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqlhotcopy]
interactive_timeout

MySQLd starten
Dump einspielen
48h warten
mysqltuner und tuning-primer Ausgaben posten

 

[homeserver]

Vielen Dank für eure Hilfe zunächst!

Ich habe das Problem nun lösen können:
Ich konnte einschränken, dass die hohe I/O Auslastung durch das "Copying to tmp table" entstand. Dieser Vorgang dauerte von eingien Sekunden bis hin zu einer Minute und mehr.

Ich habe nun eine ramdisk erstellt und nutze diese als tmpdir von mysql (tmpdir = xy).
Das Problem ist damit gelöst und der MySQL-Server rennt

Gruß homeserver

 

[Joe User]

Temp-Tables legt MySQL on disk aber nur an, wenn TEXT/BLOB-Spalten verwendet werden oder wenn der RAM komplett voll ist. Letzteres kann man meistens ausschliessen, denn die paar MB sind nahezu immer frei. Bleiben also die TEXT/BLOB-Spalten als Ursache übrig. Diese solltest Du möglichst in geeignetere Spaltentypen umwandeln und so den MySQLd sowie die HDDs zu entlasten.
Eine RAMdisk ist hier zwar als kurzfristiger Workaround durchaus legitim, aber definitiv keine dauerhafte und empfehlenswerte Lösung.

 

[homeserver]

Ja, also der RAM ist definitiv nicht voll und nur zu einem kleinen Teil ausgelastet.

Es handelt sich bei der in erster Linie betroffenen Webseite um eine mittelgroße Community mit der Forensoftware Woltlab Burning Board.
Leider lassen sich Textfelder in der verurschenden Tabelle leider nicht ohne Usability-Einbußen für den Besucher bewerkstelligen. Ich könnte theoretisch zum Datentyp VARCHAR wechseln.

Könntest du vielleicht begründen, wieso eine "RAMdisk hier zwar als kurzfristiger Workaround durchaus legitim ist, aber definitiv keine dauerhafte und empfehlenswerte Lösung ist"?

Gruß homeserver

 

[Joe User]

Die RAMdisk liegt mit einem festen Speicherbereich an einem physisch festen Punkt im RAM.
Dadurch werden beim Schreiben auf die RAMdisk (was bei den MySQL-Temp-Tables extrem häufig geschehen kann) die einzelnen für die RAMdisk reservierten Zellen des RAM überbelastet werden. Dies kann schneller als normalerweise zu defekten Zellen und somit Speicherfehlern führen. Diese Speicherfehler können widerum zu Rechenfehlern oder gar Datenverlusten führen.
Zudem werden für die RAMdisk Speicheradressen reserviert, welche dem Kernel nicht mehr zur Nutzung zur Verfügung stehen. Sollte die RAMdisk zufälligerweise auch noch in den unteren Speicherbereichen abgelegt worden sein, kann dies unter ungünstigen Umständen Probleme mit schlecht programmierter Software hervorrufen. Auch etwaige 32Bit-Software kann davon betroffen sein, da diese nur die ersten circa 3GB Speicher adressieren kann.

Es gibt noch andere Fallstricke, welche aber eher theoretischer Natur sind aka zu selten auftreten.

 

[d4f]

Auch etwaige 32Bit-Software kann davon betroffen sein, da diese nur die ersten circa 3GB Speicher adressieren kann.

Meines Wissens kann 32bit Software zwar nur 3GB ansteuern aber wo diese liegen ist relativ egal da der Kernel die Punkte intern vorgibt.

die einzelnen für die RAMdisk reservierten Zellen des RAM überbelastet werden.

Rein logisch gesehen zweifele ich dies etwas an. Ich gehe davon aus dass jede Speicherzelle des tmpfs (gilt eigentlich ASLR nicht für Ramdisks?) doch etwas weniger oft beschrieben wird als alle paar ms was der durchschnittliche Refresh-Intervall von DRAM ist. Da im Refresh eh alle Zellen neu beschrieben werden, wäre es somit imho vernachlässigbar.

Es wäre eventuell sicherlich empfehlenswert Tmpfs statt eine Ramdisk zu verwenden da dies doch so einige Vorteile im Vergleich bringt. Zu beachten ist aber dass ein Tmpfs bei Überfüllung auf den Swap ausweicht und somit bei starkem Überlauf das System stören kann.

 

[homeserver]

Sorry hatte mich da unklar ausgedrückt, ich nutze tmpfs und keine alte ramdisk, hab das nur synonym verwendet.

Meinem Verständnis nach müsste aslr gerade im Arbeitsspeicher Sinn machen und sollt auch bri tmpfs gelten oder?
So wie ich das verstanden habe wird ein tmpfs Filesystem vom Kernel genauso gemanaged wie andere Inhalte des Arbeitsspeichers, das ist ja auch sinnvoll und dadurch kommt bei Überlastung des Arbeitsspeichers dann auh das Swapping des tmpfs zustande.
Allerdings impliziert das doch, dass das tmpfs immer an anderen Speicheradressen liegt und damit nicht bestimmte Speicherabschnitte besonders belastet werden, oder?

zu meiner Maschine: Es ist ausreichend RAM vorhanden sodass ich - zumindest vorerst - keinerlei Speicherprobleme befürchte.

Gruß homeserver

 

[Joe User]

Ja, ich bezog mich rein auf "echte" RAMdisks. Bei tmpfs verhält es sich wie mit "normalen" Daten.

Das Remapping des Kernels bei 32Bit-Software in den oberen Speicherbereich funktioniert nur dann zuverlässig, wenn der Programmierer der 32Bit-Software auf feste Sprungadressen (werden/wurden leider manchmal verwendet) verzichtet und auch andere kleinere Stolpersteine beim Speichermanagement achtet.
Das Remapping ist selbstverständlich auch nicht kostenlos zu haben, sprich es entsteht ein Overhead.

Und man darf auch nicht vergessen, dass manche 64Bit-Software-Pakete intern nach wie vor teilweise rein 32Bit arbeiten (Firefox zum Beispiel bei den Plugins und Extensions).



ASLR wird IMHO überbewertet, was auch die teilweise recht simplen Exploits hierfür zeigen. Spätestens wenn man an den DMA oder gar höher kommt, haben Speicherschutzmechanismen quasi verloren. Aber darum geht es hier ja nicht...