very strange performance issue

I'm working in an environment where I have two similar servers, one
running "production" the other for "development". They're not very
dissimilar - both run 4.1.20-log, both run CentOS 4.

The development server has a cut-down snapshot of the production
database, and it's where we ... well, develop and test.

Neither is terrifically big - 32-bit machines with two cores and 4GB.

Recently, the development machine has gotten really slow. Really, REALLY
slow. Queries that take 10s of seconds on the production machine take
10s of minutes on the development machine; the dev machine is completely
CPU bound while running them, about 50% user 50% system. vmstat tells me
that lots of blocks are being read in, so my guess is that the system
is *really* thrashing the disk while pulling in pages through the
VM system. (One table is MyISAM, the other two innodb, the MyISAM
table has about 35M rows, the others considerably smaller.)

It's possible that this started when I imported the last snapshot,
but I'm not certain of that.

I have checked the queries, and they're using indices well. I have
done OPTIMIZE TABLE on the tables that are in use. I've made
sure that lots of memory is allocated (though it seems that mysqld
isn't actually using as much as it could). Top tells me that it's
only using 19.3% of physical memory, but I expect it to use a lot more.

I'm sort of stuck as to where to look next.

Here's my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/tmp
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_allowed_packet=32M
net_buffer_length=1M

key_buffer_size = 1536M
query_cache_limit = 128M
query_cache_size = 128M

max_heap_table_size = 32M
tmp_table_size = 32M

log-slow-queries = slow-queries

innodb_buffer_pool_size = 1536M
innodb_additional_mem_pool_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT

[mysql.server]
user=mysql
basedir=/var/lib

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: very strange performance issue

Perhaps some clues here: I started taking the problem query apart to see
what slows things down. I found a culprit, but I don't understand:

mysql> select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0;
+----------+
| count(*) |
+----------+
| 437 |
+----------+
1 row in set (0.35 sec)

mysql> select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0 and RuleLimit >=0;
+----------+
| count(*) |
+----------+
| 437 |
+----------+
1 row in set (6 min 15.93 sec)


Explain says that very few rows are being examined, but it takes a very
long time.


mysql> explain select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0\G
--------------
explain select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0
--------------

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Crumb
type: range
possible_keys: ix_crumb_custid_actualtime,ix_crumb_errorcode,ix_Crumb_on_Ac tual_Time,ix_Crumb_on_ErrorCode_RuleLimit
key: ix_crumb_custid_actualtime
key_len: 12
ref: NULL
rows: 290
Extra: Using where
1 row in set (0.00 sec)

mysql> explain select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0 and RuleLimit >=0\G
--------------
explain select count(*) from Crumb where customer_id=380 and Actual_Time BETWEEN '2009-06-01 00:00' AND '2009-06-02 00:00' and ErrorCode = 0 and RuleLimit >=0
--------------

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Crumb
type: ref
possible_keys: ix_crumb_custid_actualtime,ix_crumb_errorcode,ix_Crumb_on_Ac tual_Time,ix_Crumb_on_ErrorCode_RuleLimit
key: ix_Crumb_on_ErrorCode_RuleLimit
key_len: 5
ref: const
rows: 38
Extra: Using where
1 row in set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: very strange performance issue

Andrew Carlson said:

>
> I know this is basic, but check that you recreated the indexes after you
> reloaded the snapshot. That has bit me before.

I used myisamchk -r on the large table, and it has made a huge difference.

I had used myisamchk before to check the table and got no complaints.
Most confusing ... but I'm happy with the result!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org