MySQL Forums :: Performance :: Queries slow down daily, optimizing helps -> why?


Advanced Search

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? 3593 Kaspar Con 02/18/2009 04:54AM
Re: Queries slow down daily, optimizing helps -> why? 2010 aftab khan 02/18/2009 09:04AM
Re: Queries slow down daily, optimizing helps -> why? 2073 Kaspar Con 02/18/2009 11:57AM
Re: Queries slow down daily, optimizing helps -> why? 2004 Kaspar Con 02/19/2009 01:00AM
Re: Queries slow down daily, optimizing helps -> why? 2045 Rick James 02/19/2009 01:52AM
Re: Queries slow down daily, optimizing helps -> why? 1990 Kaspar Con 02/19/2009 03:32AM
Re: Queries slow down daily, optimizing helps -> why? 1471 aftab khan 02/19/2009 05:08AM
Re: Queries slow down daily, optimizing helps -> why? 1919 Kaspar Con 02/19/2009 12:24PM
Re: Queries slow down daily, optimizing helps -> why? 2005 Rick James 02/19/2009 10:53PM
Re: Queries slow down daily, optimizing helps -> why? 2118 Kaspar Con 02/20/2009 08:34AM
Solution 1855 Kaspar Con 03/18/2009 03:58AM
Re: Queries slow down daily, optimizing helps -> why? 2045 Kaspar Con 02/21/2009 06:15AM
Re: Queries slow down daily, optimizing helps -> why? 1974 aftab khan 02/24/2009 10:02AM
Re: Queries slow down daily, optimizing helps -> why? 2016 Kaspar Con 02/26/2009 02:30AM
Re: Queries slow down daily, optimizing helps -> why? 1860 aftab khan 02/26/2009 09:56AM
Re: Queries slow down daily, optimizing helps -> why? 1948 Rick James 02/26/2009 08:14PM
Re: Queries slow down daily, optimizing helps -> why? 1957 Kaspar Con 02/27/2009 01:38AM
Re: Queries slow down daily, optimizing helps -> why? 1897 Rick James 02/27/2009 07:56PM
Re: Queries slow down daily, optimizing helps -> why? 1918 Kaspar Con 02/28/2009 06:20AM
Re: Queries slow down daily, optimizing helps -> why? 2041 Rick James 02/28/2009 03:43PM
Re: Queries slow down daily, optimizing helps -> why? 1948 aftab khan 02/27/2009 04:21AM


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.