Mysql status doorlichten

[kalakuta]

Zou iemand mijn mysql status eens door willen lichten?
Sinds een week is mijn site met php forum geregeld behoorlijk traag en ik vermoed dat het mysql gerelateerd is, maar kan er de vinger niet op leggen.
Ik heb de langzaamste queries al geoptimaliseerd; er lijken geen slow queries te zijn.
Wat me wel verontrust is de waarde 0 voor handler_key_reads, waarvan ik begrijp dat die hoog zou moeten zijn.
De load van de server is vreemd genoeg niet buitensporig hoog.
Het draait op een pentium 4 van 3 GHz met 2Gb geheugen.

Hier de status:

Variable_name Value
Aborted_clients 13089
Aborted_connects 1249
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 104
Bytes_sent 72
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 0
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 0
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 0
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 0
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_unlock_tables 0
Com_update 0
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 166941
Created_tmp_disk_tables 0
Created_tmp_files 130
Created_tmp_tables 1
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 130
Innodb_buffer_pool_pages_data 0
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 0
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 0
Innodb_buffer_pool_reads 0
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 0
Innodb_data_fsyncs 0
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 0
Innodb_data_reads 0
Innodb_data_writes 0
Innodb_data_written 0
Innodb_dblwr_pages_written 0
Innodb_dblwr_writes 0
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 0
Innodb_os_log_fsyncs 0
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 0
Innodb_page_size 0
Innodb_pages_created 0
Innodb_pages_read 0
Innodb_pages_written 0
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 0
Innodb_rows_inserted 0
Innodb_rows_read 0
Innodb_rows_updated 0
Key_blocks_not_flushed 4294967292
Key_blocks_unused 348573
Key_blocks_used 175202
Key_read_requests 4141902032
Key_reads 2874208805
Key_write_requests 1100497
Key_writes 872098
Last_query_cost 0.000000
Max_used_connections 101
Not_flushed_delayed_rows 0
Open_files 133
Open_streams 0
Open_tables 68
Opened_tables 0
Qcache_free_blocks 3485
Qcache_free_memory 37994328
Qcache_hits 30569769
Qcache_inserts 22725990
Qcache_lowmem_prunes 0
Qcache_not_cached 444894
Qcache_queries_in_cache 16068
Qcache_total_blocks 36103
Questions 70647864
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 1
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_ctx_verify_depth 0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_timeouts 0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries 0
Ssl_verify_depth 0
Ssl_verify_mode 0
Ssl_version
Table_locks_immediate 43744388
Table_locks_waited 34126
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 6
Threads_connected 2
Threads_created 3187
Threads_running 1
Uptime 221773

[oehTie]

welk os draait er op je server? toevallig veel netwerkverkeer voor andere zaken?

[dirk939]

Je geeft weinig informatie maar waarschijnlijk ontbreken indexes. Met SHOW FULL COLUMNS FROM `db`.`tablename` kun je dit controleren.

[dreamhost_nl]

Wellicht dat je via mytop kunt achterhalen welke queries er op je server runnen op het moment dat je forum merkbaar trager is. Houdt er ook rekening mee dat externe factoren een oorzaak kunnen hebben, zoals een spider die simultaan diverse pagina's bezoekt of zelfs een hardwareprobleem op je server...

[Xolphin]

Probeer http://www.day32.com/MySQL/tuning-primer.sh eens...

[kalakuta]

De site draait op Fedora Core 6. Het netwerkverkeer lijkt me niet de bottleneck; queries zijn optimized en gebruiken indexes. Elders wordt gesuggereerd dat de key buffer size veel te klein is. In elk geval heb ik aanknopingspunten om verder te zoeken. Allemaal dank!

[systemdeveloper]

Misschien een stom idee, maar log eens in als root op mysql en voer het commando 'UNLOCK TABLES' eens uit?
Zoals dit eruit ziet heeft die mysql server niks te doen behalve te wachten op het vrijgeven van tablelocks. Er wordt geen query uitgevoerd, alle clients aborten en je 'table locks waited' is redelijk hoog.

Edit: Restart mysqld anders eens en kijk in de mysql error log of er niet toevallig een tabel of index kapot is.

[johan.smits]

Zijn er tools die je query kunnen analiseren en hierop kunnen aangeven waar een index te leggen bv?

Ik heb het tuning-primer.sh script gebruikt en hier kwamen uit mijn configuratie toch vreemde getallen uit en gebruikte op de verkeerde plaatsen de verkeerde resources.

Echte heb nog een aantal slow querys in mijn applicatie zitten die ik er niet uit krijg.

Is hier een tool voor?

[kalakuta]

Misschien een stom idee, maar log eens in als root op mysql en voer het commando 'UNLOCK TABLES' eens uit?
Zoals dit eruit ziet heeft die mysql server niks te doen behalve te wachten op het vrijgeven van tablelocks. Er wordt geen query uitgevoerd, alle clients aborten en je 'table locks waited' is redelijk hoog.

Edit: Restart mysqld anders eens en kijk in de mysql error log of er niet toevallig een tabel of index kapot is.

Dat het een locking probleem kan zijn heeft ook al door mijn hoofd gespeeld. Het forum heeft 4 a 5 jaar redelijk probleemloos gelopen maar is in de loop van de tijd ook behoorlijk gegroeid. Misschien moet ik overstappen op innodb om row locking te kunnen gebruiken in plaats van table locking. Het feit dat dat extra overhead oplevert heeft me tot heden daarvan afgeschrikt, maar misschien ben ik al over het kantelpunt heen.


Zijn er tools die je query kunnen analiseren en hierop kunnen aangeven waar een index te leggen bv?

Ik heb het tuning-primer.sh script gebruikt en hier kwamen uit mijn configuratie toch vreemde getallen uit en gebruikte op de verkeerde plaatsen de verkeerde resources.

Echte heb nog een aantal slow querys in mijn applicatie zitten die ik er niet uit krijg.

Is hier een tool voor?

Explain gebruiken. Ik kan je het book 'High Performance MySQL' van harte aanbevelen.

[Xolphin]

Zijn er tools die je query kunnen analiseren en hierop kunnen aangeven waar een index te leggen bv?

Ik heb het tuning-primer.sh script gebruikt en hier kwamen uit mijn configuratie toch vreemde getallen uit en gebruikte op de verkeerde plaatsen de verkeerde resources.

Echte heb nog een aantal slow querys in mijn applicatie zitten die ik er niet uit krijg.

Is hier een tool voor?

Je kunt MySQL zelf hiervoor gebruiken, deze kan je precies aangeven waar het probleem zit. In bijna elke frontend, waaronder phpMyAdmin zitten functies voor profiling en dergelijke. Hiermee laat MySQL zien wat hij doet, en welke links tussen tabellen gebruikt worden. Daarnaast is via Google gigantisch veel informatie te vinden.

[johan.smits]

Als ik de explain optie gebruik krijg ik het volgende op deze query:


id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gebruikers ALL User's Online,bedrijfs_id NULL NULL NULL 149 Using where; Using temporary; Using filesort
1 SIMPLE bedrijf eq_ref PRIMARY PRIMARY 4 wcm.gebruikers.bedrijfs_id 1

Dit is de query:
SELECT `gebruikers`.`id` , `gebruikers`.`gebruikersnaam` , `gebruikers`.`echte_naam` , `bedrijf`.`bedrijfsnaam` , `bedrijf`.`id` FROM `gebruikers` INNER JOIN `bedrijf` ON ( `gebruikers`.`bedrijfs_id` = `bedrijf`.`id` ) WHERE `bedrijf`.`id` !=0 ORDER BY `bedrijf`.`bedrijfsnaam` ASC , `gebruikers`.`gebruikersnaam`
Dit zijn de indexes op de Tables
Bedrijf:
http://www.leftclick.nl/tmp/mysql-bedrijf.png

Gebruikers:
http://www.leftclick.nl/tmp/mysql-gebruikers.png

Mysql markeerd deze query als SLOW maar wat kan ik nu aan de hand met EXPLAIN verbeteren?
Het is juist de bedoeling om alle gebruikers rows te openen. Maar word hij daarom misschien gemarkeerd?

[jurrian]

Aantal zaken die mij opvallen die volgens mij beter kunnen:

Qcache_not_cached is vrij hoog, wat inhoud dat bepaalde queries niet gecached kunnen worden. Veel voorkomende oorzaken hiervoor is het gebruik van OR (gebruik liever 2x dezelfde query met verschillende WHERE's en voeg deze samen met een UNION)
Het gebruik van NOT (het woord NOT, <> of !=) is niet handig; MySQL gaat hiermee alle records / waarden in de index langs
Als je veel resultaten verwacht, gebruik van SELECT SQL_BIG_RESULT... MySQL zal dan niet/minder snel een temporary table aanmaken (die op harddisk wordt weggeschreven, hetgeen de query zo traag maakt).
Let er op dat niet iedere query in je slow-query-log traag is! Het begint met de eerste query die je systeem traag maakt, de daarop volgende queries zijn dan ook traag en komen ook in het log


Uit de screenshots die je post blijkt overigens dat je wel indices hebt, maar dat ze niet werken (geen kardinaliteit). Draai eens OPTIMIZE TABLE <tabelnaam> om de indices van die tabel te optimaliseren.

Hierboven wordt al aangeraden 'MySQL High Performance' eens te lezen... Is zeker aan aanrader, maar is helaas al verouderd. Het is geschreven toen MySQL 5 nog in ontwikkeling was en baseert zich nog op MySQL 4. In MySQL 5 zijn veel wijzigingen en verbeteringen waardoor het boek vaak wat out-of-date is. Er wordt overigens wel gewerkt aan een nieuwe versie van het boek (waarin MySQL meegenomen gaat worden).

[johan.smits]

Aantal zaken die mij opvallen die volgens mij beter kunnen:
Qcache_not_cached is vrij hoog, wat inhoud dat bepaalde queries niet gecached kunnen worden. Veel voorkomende oorzaken hiervoor is het gebruik van OR (gebruik liever 2x dezelfde query met verschillende WHERE's en voeg deze samen met een UNION)
Het gebruik van NOT (het woord NOT, <> of !=) is niet handig; MySQL gaat hiermee alle records / waarden in de index langs
Als je veel resultaten verwacht, gebruik van SELECT SQL_BIG_RESULT... MySQL zal dan niet/minder snel een temporary table aanmaken (die op harddisk wordt weggeschreven, hetgeen de query zo traag maakt).
Let er op dat niet iedere query in je slow-query-log traag is! Het begint met de eerste query die je systeem traag maakt, de daarop volgende queries zijn dan ook traag en komen ook in het log
Uit de screenshots die je post blijkt overigens dat je wel indices hebt, maar dat ze niet werken (geen kardinaliteit). Draai eens OPTIMIZE TABLE <tabelnaam> om de indices van die tabel te optimaliseren.

Hierboven wordt al aangeraden 'MySQL High Performance' eens te lezen... Is zeker aan aanrader, maar is helaas al verouderd. Het is geschreven toen MySQL 5 nog in ontwikkeling was en baseert zich nog op MySQL 4. In MySQL 5 zijn veel wijzigingen en verbeteringen waardoor het boek vaak wat out-of-date is. Er wordt overigens wel gewerkt aan een nieuwe versie van het boek (waarin MySQL meegenomen gaat worden).

Wat kan ik dan als alternatief gebruiken op <> != NOT?

[jurrian]

Wat kan ik dan als alternatief gebruiken op <> != NOT?

Volgens mij wil je toch != 0? Misschien dat > 0 hetzelfde oplevert (mits je geen negatieve waarden gebruikt). Anders 2 queries maken... de ene < 0 en dan andere > 0 en de resultaten combineren met een UNION

[Xolphin]

Volgens mij wil je toch != 0? Misschien dat > 0 hetzelfde oplevert (mits je geen negatieve waarden gebruikt). Anders 2 queries maken... de ene < 0 en dan andere > 0 en de resultaten combineren met een UNION

En dat wordt dan sneller? Geloof je het zelf?

[jurrian]

Dat wordt zeker sneller ja... als je je data nl. in een tree sorteert die oplopend is opgebouwd weet je dat als je een bepaalde waarde gevonden hebt, de volgende waarden automatisch ook groter danwel kleiner zijn.

Bovendien: de Query-cache van MySQL wordt NOOIT gebruikt als er een NOT (in welke vorm dan ook) in voorkomt. Bij een groterdan of kleinerdan wel. Dus al zou de query er 2x zo lang over doen, dan is het nog altijd sneller dan 3x de vorige query. :)

[Xolphin]

Waar haal je deze 'wijsheid' vandaan? Heb je hier een bron voor, of is dit iets wat je zelf bedacht hebt?

Ik vraag me echt af of je dit serieus bedoeld...

[wonko]

De claim die gemaakt wordt is zeker correct!

Als je wil weten hoe het werkt, lees even na hoe de BTREE-datastructuur ineen steekt, en lees dan even na hoe MySQL zijn indexen opbouwt en gebruikt. Zorg ervoor dat je mysql zolang mogelijk zijn index kan blijven gebruiken; en denk eraan dat het een geordende datastructuur is (> of < geeft orde aan, != niet). Tuning primer is een prima script, maar lees wat het zegt, toets het aan je eigen situatie en ga dan pas acties ondernemen. Het is een tool, geen oplossing.

Het optimaliseren van queries kan een gigantisch verschil geven; soms is 1 query verantwoordelijk voor slechte performantie van een hele server.

Hoofdstuk 7 van de mysql manual is ook zeer intressant te noemen, en zeker deel 7.2: http://dev.mysql.com/doc/refman/5.0/en/query-speed.html. Echter, ik wil benadrukken dat je het moet lezen, en begrijpen voor je maar "mysql" durft te tikken op je server! Wild en zonder clue tekeer gaan in je DB is nooit goed.

[Xolphin]

Dat optimaliseren slim is twijfel ik niet aan, dat een query met NOT er in meer tijd kost ook niet.

Het gaat er bij mij alleen niet in dat een UNION van een > en < query sneller gaat zijn dan een != query. Een UNION kost ook tijd, vooral als je daarna de data nog eens wil sorteren, of ook op andere data wil selecteren. Ik heb geen enkele informatie gevonden voor deze claim, graag hoor ik van een van jullie waar ik dan overheen lees...

Ben er nog even over in discussie gegaan op #mysql on FreeNode, volgens de mensen daar heeft de UNION geen snelheidswinst. Afhankelijk van het percentage wat uitgesloten wordt is een table scan in de meeste gevallen sneller dan het gebruik van een index. Daarna volgde een hele discussie over welke storage engine je het beste kon gebruiken en dergelijke, maar dat gaat voor hier wat te ver.

Ik blijf van mening dat het gebruik van != 0 op geen andere snellere manier kan worden opgelost, ook niet door een UNION van > 0 en < 0. Graag zie ik wat meer argumenten dan alleen "De claim die gemaakt wordt is zeker correct!".

[wonko]

Ok, geen probleem dan. Wel vreemd dat je om raad vraagt en die dan in twijfel gaat trekken (al is het natuurlijk zeker niet verkeerd om kritisch te zijn naar input toe).

Ik zou je aanraden om dan maar eens een benchmark te laten lopen.

Voor jou specifieke query: probeer een index te maken voor je sort-velden, in de volgorde van je sort. Dat zou je "using filesort" moeten wegwerken.

[Xolphin]

Ok, geen probleem dan. Wel vreemd dat je om raad vraagt en die dan in twijfel gaat trekken (al is het natuurlijk zeker niet verkeerd om kritisch te zijn naar input toe).

Ik vraag niet om raad, ik ben niet de TS. Ik heb geen probleem queries, ik reageerde alleen op de bewering van Jurrian. Volgens mij is die bewering onjuist.

[jurrian]

Zoals ik al eerder aangaf: de UNION hoeft niet sneller te zijn dan de NOT, maar de query met de UNION kan wel gecached worden; de NOT-query niet (en evt. een variant met < 0 OR > 0 ook niet, want OR en caching gaan volgens mij ook niet goed samen). De cache zal de snelheidswinst leveren.

Hoe ik aan die wijsheid kom? Dagelijks werken met complexe queries op grote datasets (tabellen met een paar miljoen records, een paar keer gejoined tegen andere tabellen met veel records) in MySQL, 'High performance MySQL' lezen, documentatie lezen bij MySQL, al je SELECT-queries een keer uitvoeren met EXPLAIN ervoor (en niet alleen op je test- of ontwikkelomgeving; MySQL kan met een ander dataset heel andere beslissingen nemen) en natuurlijk ook gewoon veel uitproberen.

[Xolphin]

Hoe ik aan die wijsheid kom? Dagelijks werken met complexe queries op grote datasets (tabellen met een paar miljoen records, een paar keer gejoined tegen andere tabellen met veel records) in MySQL, 'High performance MySQL' lezen, documentatie lezen bij MySQL, al je SELECT-queries een keer uitvoeren met EXPLAIN ervoor (en niet alleen op je test- of ontwikkelomgeving; MySQL kan met een ander dataset heel andere beslissingen nemen) en natuurlijk ook gewoon veel uitproberen.

Op welke bladzijde van "High performance MySQL" staat dit dan beschreven? Ik heb het boek hier liggen, maar ik zie wat jij beweerd er nergens in staan en kan het ook nergens anders terug vinden.