remove tmp table - Creating slow query

[mcode]

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


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

[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?

 

[zEitEr]

Hello,

Try to check your MySQL settings with mysqltuner.pl:

cd /usr/local/src
wget -O mysqltuner.pl http://mysqltuner.pl
chmod 755 mysqltuner.pl
./mysqltuner.pl --user da_admin --pass `grep passwd= /usr/local/directadmin/conf/mysql.conf | cut -d\= -f2`

And you'll get some recommendations. Note your MySQL server should run more than 24 hours to get more accurate recommendations


p.s. You might want to give more RAM for creating tmp tables, so MySQL would not create and store then on your HDD, it will for sure speed up the process. The script will help you with it also.