Onderwerp: Mysql status doorlichten

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

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

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

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

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

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!

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.

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?

Oorspronkelijk geplaatst door 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.

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.

Oorspronkelijk geplaatst door 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?

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

Oorspronkelijk geplaatst door 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?

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.

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

Code:

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:

Code:

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?

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

Oorspronkelijk geplaatst door 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).

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

Oorspronkelijk geplaatst door johan.smits

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

Oorspronkelijk geplaatst door jurrian

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?