remove tmp table – Creating slow query

remove tmp table – Creating slow query

I have been dealing with one particular website created in joomla 2.5.6. freezes every website with a sudden CPU spike. I have the process and at this point have no clue how to handle this particular situation.

Here is the process list at the CPU spike
Attachment 1396

I’m also including my.cnf (in-case there is a way to solve it through here)

Quote:

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock

max_connections = 500
skip-external-locking
key_buffer_size = 1500M
max_allowed_packet = 1M
#binlog_cache_size = 1M
#max_heap_table_size = 64M
table_open_cache = 2000
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 7m
thread_cache_size = 80
#query_cache_size = 50M
#tmp_table_size = 64M
#default_table_type = INNODB
#thread_stack = 192K
#transaction_isolation = REPEATABLE-READ
#log_slow_queries
#long_query_time = 2
#log_long_format
#tmpdir = /tmp

#memlock

innodb_data_home_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 120M
innodb_change_buffering = all
innodb_additional_mem_pool_size = 4M
innodb_file_per_table = 1
innodb_commit_concurrency = 0
innodb_thread_concurrency= 16
innodb_concurrency_tickets = 500
innodb_log_file_size= 30M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0
#innodb_flush_method=O_DIRECT
#innodb_read_io_threads=8
#innodb_write_io_threads=8
innodb_io_capacity=200
innodb_lock_wait_timeout=50
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend


I have a very powerful machine (8 processors 16 gig of ram, Raid 0) but small tables. They all run joomla. The only problem is this one particular site that has over 60,000 records. It runs very fast on every page, then this remove tmp takes place and it freezes for over a minute, every site on the server and sometimes SSH. I have tried every configuration. It seems to run better with the myisam engine, Want to make it work with innodb.

Are there any suggestions? Has anyone had this issue?

Attached Images
Comments are closed.