Very slow queries on server - copying to tmp table

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I am have some unexpected problems with a production server.
For few hours it become very very slowly and queries are taking a lot more than normally. I have stopped all applications and run some very simple queries and this is obvious

While a normal query was taking 0.03 s to complete now it is taking like 100 s and I can see the query state is Copying to tmp table. It simply acts like it is not using the Indexes anymore

Here is the my.cnf configuration from the live server (20 Gigs of RAM)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-bdb
skip-name-resolve

log-slow-queries=/var/lib/mysql/mysql-slow-queries.log
long_query_time = 5

log-queries-not-using-indexes

ft_min_word_len = 1
ft_stopword_file =

max_heap_table_size = 32M
tmp_table_size = 128M
max_connections = 500
max_allowed_packet = 12M
group_concat_max_len = 4096000
wait_timeout = 30

key_buffer_size = 2048M
bulk_insert_buffer_size = 32M

sort_buffer_size = 12M
join_buffer_size = 8M
read_buffer_size = 4M
myisam_sort_buffer_size = 32M
read_rnd_buffer_size = 10M

thread_concurrency = 4
thread_cache = 8
thread_cache_size = 128
table_cache = 1024

query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 2

default-character-set = utf8

InnoDB

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/

innodb_buffer_pool_size = 1024M
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M

Set …_log_file_size to 25 % of buffer pool size

innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_open_files = 1000

Any advice would be really helpfull

Art

2

Here are some errors from the mysql log around the time the problems appeared.

091114 4:03:36 InnoDB: ERROR: the age of the last checkpoint is 120792325,
InnoDB: which exceeds the log group capacity 120792269.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
091115 3:03:13 InnoDB: ERROR: the age of the last checkpoint is 120792375,
InnoDB: which exceeds the log group capacity 120792269.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
091115 9:21:45 InnoDB: ERROR: the age of the last checkpoint is 120792453,
InnoDB: which exceeds the log group capacity 120792269.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

We are using both MyISAM and Innodb tables

Art

3

You can avoid using temp tables on disk, if you not using BLOB or TEXT columns, unless your mysql will create a temp table on disk every time.

Another possibility is to set the temp tables location to a memory filesystem. That can help a lot too.