MySQL Forums
Forum List  »  Performance

Queries slow down daily, optimizing helps -> why?
Posted by: Kaspar Con
Date: February 18, 2009 04:54AM

This post was originally posted at the administration-forum, but I think its better here... sorry for messing.

We have a Server running with several thousand users per day.

We face the strange problem that LIKE-queries gets really slow after one day server uptime. If we do optimize the relevant tables they get fast again.

One Example is a simple query whith no possible indexes like

SELECT id FROM user WHERE un like '%somebody%'

If the server is slow I the query and it takes about 4 seconds.
When I look at the tables, they look OK, no data_free, all Indices have correct cardinality. Anyhow if I optimize the table the query takes about 4 ms.

I post the definition and the Variables (I hope I chose the correct ones), perhaps anybody could find anything. We have only MyIsam-Tables.

The main question is, what happens in the background, that the query is that fast again.

thanks for your time.

+---------------------------------+------------------------- -------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------- -------------------------------------------+
| join_buffer_size | 2093056 |
| key_buffer_size | 536870912 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 120 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 134217728 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| open_files_limit | 1130 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| preload_buffer_size | 32768 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 40960 |
| query_cache_size | 536870912 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| table_cache | 500 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 16 |
| thread_stack | 196608 |
| tmp_table_size | 268435456 |
| version | 5.0.37-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | apple-darwin8.5.1 |
| wait_timeout | 28800 |
+---------------------------------+------------------------- -------------------------------------------+

Status Variables:

mysql> SHOW STATUS;
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Connections | 141410 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 2 |
| Handler_read_rnd_next | 228 |
| Handler_write | 358 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 443957 |
| Key_blocks_used | 62677 |
| Key_read_requests | 42233864 |
| Key_reads | 188714 |
| Key_write_requests | 123667 |
| Key_writes | 116333 |
| Last_query_cost | 10.499000 |
| Max_used_connections | 15 |
| Open_files | 211 |
| Open_streams | 0 |
| Open_tables | 118 |
| Opened_tables | 0 |
| Qcache_free_blocks | 49279 |
| Qcache_free_memory | 415283128 |
| Qcache_hits | 41696874 |
| Qcache_inserts | 1547461 |
| Qcache_lowmem_prunes | 52564 |
| Qcache_not_cached | 9414 |
| Qcache_queries_in_cache | 100825 |
| Qcache_total_blocks | 250999 |
| Questions | 43947663 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 2327597 |
| Table_locks_waited | 289 |
| Threads_cached | 13 |
| Threads_connected | 2 |
| Threads_created | 15 |
| Threads_running | 1 |
| Uptime | 165225 |
| Uptime_since_flush_status | 165225 |
+-----------------------------------+-----------+

The Explain for the query:

mysql> EXPLAIN SELECT id FROM user WHERE un like '%somebody%';
+----+-------------+-------+------+---------------+------+-- -------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+-- -------+------+-------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 10794 | Using where |
+----+-------------+-------+------+---------------+------+-- -------+------+-------+-------------+

(I am aware that this is not very good, but that's not my point at the moment)

mysql> SHOW TABLE STATUS LIKE 'user';
+------+--------+---------+------------+-------+------------ ----+-------------+-----------------+--------------+-------- ---+----------------+---------------------+----------------- ----+---------------------+-----------------+----------+---- ------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+-------+------------ ----+-------------+-----------------+--------------+-------- ---+----------------+---------------------+----------------- ----+---------------------+-----------------+----------+---- ------------+-----------------------------+
| user | MyISAM | 10 | Dynamic | 10794 | 126 | 1369436 | 281474976710655 | 866304 | 0 | 11473 | 2009-02-10 13:14:51 | 2009-02-18 08:35:43 | 2009-02-18 07:53:26 | utf8_unicode_ci | NULL | | Users and global privileges |
+------+--------+---------+------------+-------+------------ ----+-------------+-----------------+--------------+-------- ---+----------------+---------------------+----------------- ----+---------------------+-----------------+----------+---- ------------+-----------------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Queries slow down daily, optimizing helps -> why?
3706
February 18, 2009 04:54AM
1881
March 18, 2009 03:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.