MySQL Optimierung

MySQL, PostgreSQL, SQLite
Takumi
Posts: 3
Joined: 2016-08-08 16:08

MySQL Optimierung

Post by Takumi » 2016-08-08 16:31

Hallo zusammen,

ich weiß das es eigentlich eine Frechheit ist, mit den spärlichen Angaben etwas zu Fragen.
Leider hab ich aber keine Ahnung was ihr braucht um mir zu helfen.

Ich hab auf der Seite immer wieder mal einen Error 500 und vermute die Datenbank, da alles andere wirklich sauber läuft.

Das ganze läuft auf einem Windows 2008 R2 Datacenter
Intel XEON 6 Kerne/12 Threads
32GB Arbeitsspeicher deren Auslastung bei durchschnittlich 60% liegt

Kann mir mit den folgenden Angaben jemand helfen das ganze zu optimieren?
Wenn was fehlt kann das recht schnell nachgeliefert werden.

MySQL Tuner spuckt folgendes aus:

Code: Select all

MySQL Tuner 0.8 - Peter Chapman <peter@conglomo.co.nz>
Performing tests on localhost:3306 at Monday, 8 August 2016 1:57:02 p.m.
Currently running supported MySQL version 5.1.73-community
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Not Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 1G (Tables: 4666)
Data in MyISAM tables: 48M (Tables: 590)
Total fragmented tables: 4681
All database users have passwords assigned
Up for: 8d 4h 4m 10s (10M q [15,000 qps], 482K conn, TX: 81G, RX: 2G)
Reads / Writes: 58% / 42%
Total buffers: 1,1G global + 19,3M per thread (400 max threads)
Maximum possible memory usage: 8,6G (35% of installed RAM)
Slow queries: 1% (13/10M)
Highest usage of available connections: 8% (32/400)
Key buffer size / total MyISAM indexes: 256,0M/26,6M
Key buffer hit rate: 98% (25M cached / 314K reads)
Query cache efficiency: 67% (4M cached / 6M selects)
Query cache prunes per day: 0
Sorts requiring temporary tables: 0% (0 temp sorts / 354K sorts)
Joins performed without indexes: 113336
Temporary tables created on disk: 34% (111K on disk / 333K total)
Thread cache hit rate: 99% (32 created / 482K connections)
Table cache hit rate: 0% (52 open / 117K opened)
Open file limit used: 0% (14/2K)
Table locks acquired immediately: 99% (5M immediate / 5M locks)
InnoDB data size / buffer pool: 1,1G/64,0M
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
long_query_time (<= 10)
join_buffer_size (> 128,0K, or always use indexes with joins)
table_open_cache (> 524288)
innodb_buffer_pool_size (>= 1G)
Scan Completed at Monday, 8 August 2016 1:58:29 p.m.
Wenn ich mir alle Status-Variablen anzeigen lasse tauchen dort folgende Punkte in ROT auf:

Code: Select all

Aborted clientsDokumentation	4 	
Aborted connectsDokumentation	137 	
Created tmp disk tablesDokumentation	113,2 k	
Handler read rndDokumentation	8,1 M	
Handler read rnd nextDokumentation	1,3 G
Innodb buffer pool pages dirtyDokumentation	33
Innodb buffer pool readsDokumentation	337 k
Innodb log waitsDokumentation	10
Innodb row lock time maxDokumentation	1,5 k
Innodb row lock waitsDokumentation	521
Key readsDokumentation	319 k
Opened tablesDokumentation	130,1 k	
Select full joinDokumentation	115,3 k
Slow queriesDokumentation	16
Table locks waitedDokumentation	3,7 k
Der Inhalt meiner my.ini lautet wie folgt:

Code: Select all

[MySQLD]
port=3306
default-time-zone=SYSTEM
basedir=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL51\
datadir=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data
lower_case_table_names=0
character-set-server=latin1
default-storage-engine=INNODB
read_buffer_size=10M
read_rnd_buffer_size=5M
sort_buffer_size=4M
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=1M
innodb_buffer_pool_size=64M
innodb_log_file_size=10M
innodb_thread_concurrency=0
max_connections=400
old_passwords=0
max_user_connections=100
thread_cache_size=40
thread_stack=196608
binlog_cache_size=32768
net_read_timeout=30
net_retry_count=10
net_write_timeout=30
thread_concurrency=10
table_open_cache=39390
open_files_limit=78780
key_buffer=256M
max_allowed_packet=16M
query_cache_limit=512M
query_cache_size=512M
table_cache=256M
tmp_table_size=256M
max_heap_table_size=256M
log-error=mysql.err
[client]
port=3306

Vielen Dank im voraus
Takumi

User avatar
Joe User
Project Manager
Project Manager
Posts: 11139
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Optimierung

Post by Joe User » 2016-08-08 18:42

Das Problem ist zweiteilig:

Der erste Teil besteht in einer/mehrerer der Apps die auf MySQL zugreifen und dabei JOINs ohne INDEXes verwenden. Hier muss also die App gefixt werden, entweder von Dir, sofern es Open-Source ist und Du weisst was Du tust, oder aber vom Hersteller der App. Eventuell gibt es da auch schon längst Updates, denn:

Der zweite Teil besteht in dem seit Jahren überfälligen Update auf eine supportete MySQL Version (aktuell 5.7.14). Dabei musst Du schrittweise vorgehen, also 5.1 auf 5.5 auf 5.6 auf 5.7, ansonsten gibt es erst richtig Probleme. Aber nicht blind Updates installieren, sondern dabei immer die entsprechenden Hinweise in der versionsbezogenen Doku beachten. Und natürlich ein vollständiges Backup mittels mysqldump nicht vergessen.

Danach sollten sich die Probleme weitestgehend erledigt haben.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

Takumi
Posts: 3
Joined: 2016-08-08 16:08

Re: MySQL Optimierung

Post by Takumi » 2016-08-08 20:40

Ok, vielen Dank schon mal für die Antwort.

Der erste Teil ist leider nicht so leicht, es handelt sich um Joomla in der aktuellsten Version, alle Erweiterungen sind ebenfalls auf aktuellem Stand und die letzten Updates sind noch keine 4 Wochen her. Selbst kenn ich mich dafür zu wenig aus, also wird mir hier nichts anderes übrig bleiben als es zu lassen wie es ist.

Teil zwei wird auch nicht einfacher. Ich kann MySQL nur über Plesk updaten und dort wird mir lediglich die 5.6 angeboten. Wenns danach nicht läuft ist der Downgrade versperrt. Werd ich die Männer von HostEurope mal befragen was die dazu sagen und was sie evtl. für das Update haben wollen.

An der my.ini kann ich derzeit nichts machen um da eine spürbare Verbesserung zu erhalten? Wobei ich sagen muss das die Seite jetzt nicht gerade langsam ist. Schneller geht immer, oder was denkt ihr? www.pilzfreunde.eu

Evtl. könnt ihr mir noch bei einem anderen Problem helfen, oder zumindest sagen woran es liegt.
Die folgenden Protokolleinträge wiederholen sich ständig, auch damit kann ich aber nichts anfangen.

Code: Select all

2016-08-08 17:35:08			PHP Warning: mysqli::stat(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 219				php_error
2016-08-08 17:35:08			PHP Warning: mysqli_close(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 226				php_error
2016-08-08 17:38:24			PHP Warning: mysqli::stat(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 219				php_error
2016-08-08 17:38:24			PHP Warning: mysqli_close(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 226				php_error
2016-08-08 17:38:27			PHP Warning: mysqli::stat(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 219				php_error
2016-08-08 17:38:27			PHP Warning: mysqli_close(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 226				php_error
2016-08-08 17:38:30			PHP Warning: mysqli::stat(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 219				php_error
2016-08-08 17:38:30			PHP Warning: mysqli_close(): Couldn't fetch mysqli in C:\Inetpub\vhosts\pilzfreunde.eu\httpdocs\libraries\joomla\database\driver\mysqli.php on line 226				php_error
Die entsprechenden Zeilen markiere ich mal mit --->

Code: Select all

// Close the connection.
219---->	if ($this->connection instanceof mysqli && $this->connection->stat() !== false)
		{
			foreach ($this->disconnectHandlers as $h)
			{
				call_user_func_array($h, array( &$this));
			}

226---->		mysqli_close($this->connection);
		}

		$this->connection = null;
	}
Herzlichen Dank vorab für die Unterstützung

User avatar
Joe User
Project Manager
Project Manager
Posts: 11139
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Optimierung

Post by Joe User » 2016-08-08 22:10

An der Geschwindigkeit lässt sich noch Einiges verbessern:
https://tools.pingdom.com/#!/iEba0/www.pilzfreunde.eu
https://developers.google.com/speed/pag ... unde.eu%2F

Die my.ini ist laut Deinem mysqltuner erstmal ausreichend, da sollte man frühestens nach einem MySQL-Update einen gründlicheren Blick drauf werfen.

Die PHP-Warnings habe ich jetzt nicht nachgeschlagen, vermute aber, dass bei Dir auch PHP in einer veralteten unsupporteten Version läuft.


Du solltest wohl mal darüber nachdenken, auf ein komplett neues aktuelles System umzusteigen (Win 2008 ist auch nicht mehr wirklich aktuell) und künftig erheblich regelmässiger Updates durchzuführen.
Allein Deine MySQL Version hat weit über 50 bekannte leicht ausnutzbare Sicherheitslücken und der Rest Deiner Software ist vermutlich auch nicht regelmässig aktualisiert und damit abgesichert worden. Das würde mir als Admin schlaflose Nächte bereiten, da wäre mir eine langsamere Webseite erstmal völlig egal.

Eventuell ist auch ein reines Webhosting-Angebot besser für Dich geeignet, denn dort musst Du Dich nicht mehr um das System kümmern und kannst Dich in Ruhe mit der Webseite auseinandersetzen. Ist zumindest mal eine Überlegung wert und unterm Strich auch günstiger und vor Allem schneller und sicherer.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

Takumi
Posts: 3
Joined: 2016-08-08 16:08

Re: MySQL Optimierung

Post by Takumi » 2016-08-08 23:45

Es läuft PHP 7.0.6 und alle Updates werden regelmäßig gemacht. Lediglich der MySQL-Server ist noch so altbacken.
So einfach ist es mit dem Umstieg leider auch nicht, es gibt wohl wenig Hostingangebote die mir das bieten was ein Root-Server zu bieten hat und die 49 Domains die darauf laufen sind auch nicht einfach so umzuziehen.

Nungut, ich rede mal mit Hosteurope, sollen die mir den MySQL-Server updaten.

Danke aber für die Einschätzung
Gruß
Takumi

User avatar
Joe User
Project Manager
Project Manager
Posts: 11139
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Optimierung

Post by Joe User » 2016-08-09 00:10

OK, bei rund 50 Domains/Webseiten wird Webhosting wirklich recht uninteressant.
Wenn der Rest der Software aktuell ist und nur MySQL veraltet, dann lasse den erstmal updaten und anschliessend können wir uns gerne zusammen an die Optimierung der my.ini machen, sofern es dann noch nötig oder sinnvoll ist.

Bezüglich der Webseiten-Geschwindigkeit gibt Dir Googles Insight schon viel Hilfestellung, davon solltest Du soviel wie möglich umsetzen, das könnte die Ladezeiten sicherlich schon um rund eine Sekunde drücken.
Mit dem Aufräumen und Reduzierem der HTML, CSS, JS sowie der Grafiken lassen sich danach dann noch mal einige hundert Milisekunden einsparen.
Am Ende könntest Du vermutlich auf deutlich unter eine Sekunde kommen, aber das ist auch viel Arbeit ;)
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.