Re: Query Takes Longer Each Time Its Run

[anthony]

The machine has 16GB RAM as reported below. That's why I figured 4GB for the key_buffer was appropriate.

MemTotal: 16434976 kB
MemFree: 2641600 kB
Buffers: 187736 kB
Cached: 9316648 kB
SwapCached: 0 kB
Active: 10476848 kB
Inactive: 2967768 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 16434976 kB
LowFree: 2641600 kB
SwapTotal: 8193140 kB
SwapFree: 8193140 kB
Dirty: 64 kB
Writeback: 0 kB
AnonPages: 3940168 kB
Mapped: 15992 kB
Slab: 308056 kB
PageTables: 12888 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 16410628 kB
Committed_AS: 9109208 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 266924 kB
VmallocChunk: 34359471059 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB

Here is the buffer information from the current production db:

+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 524288000 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 52428800 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1048576 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 1048576 |
| sql_buffer_result | OFF |
+-------------------------+-----------+

On the current production system the records table is InnoDB. I've changed all the tables on the new system to MyISAM as I think its more appropriate anyway. But, I did try both engine types with the exact same result. Query initially about 2 seconds and +5 seconds every additional time its run.

Here's the my.cnf while were at it:

# Slow Query Log
slow-query-log=0
slow_query_log_file=/storage/logs/mysql/slowquery/slow-query.log
long_query_time=2
#log_queries_not_using_indexes=1
log-error=/storage/logs/mysql/error/mysqld.log

# Query Cache
query_cache_type=1
query-cache-size=64M

# File Descriptor Settings
open_files_limit=8196
table_open_cache=1024

# Binary Logging / Replication Settings
server-id=101
log-bin=/storage/logs/mysql/binary/mysql-bin
log-bin-index=/storage/logs/mysql/binary/mysql-bin
binlog-format='ROW'
max_binlog_size=250M

# Defaults
character-set-server=utf8
default-storage-engine=MyISAM
default-time-zone='GMT'
thread-cache-size=100

# InnoDB settings
innodb_buffer_pool_size=0
innodb_log_buffer_size=8M
innodb_log_file_size=100M
innodb_thread_concurrency=8
innodb_max_dirty_pages_pct=90

# Additional Options (mysql.com recommendations for InnoDB optimization)
skip-name-resolve
skip-external-locking
max_allowed_packet=8M
max_connections=200
read_buffer_size=16M
sort_buffer_size=16M
key_buffer_size=4G

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid