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=arch...@jab.org
