MySQL Forums
Forum List  »  Performance

Re: Query Takes Longer Each Time Its Run
Posted by: anthony
Date: January 27, 2011 10:59PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3881
January 26, 2011 02:59PM
1481
January 27, 2011 02:35PM
Re: Query Takes Longer Each Time Its Run
1552
January 27, 2011 10:59PM
2000
January 28, 2011 01:38PM
1347
January 30, 2011 07:20PM
1390
January 31, 2011 01:22AM
1500
January 31, 2011 01:25AM
1351
February 01, 2011 12:38AM
1467
February 01, 2011 12:56AM
1414
February 01, 2011 01:23PM
1651
February 01, 2011 08:41PM
2141
February 03, 2011 02:04PM
1609
February 15, 2011 09:24PM


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.