| Posted by tetrahost, 12-12-2014, 01:04 PM | | One of my server which has a high traffic wordpress site is having lots of "Copying to tmp table" query in MySQL server. Almost 90% of the queries are "Copying to tmp table". The MySQL server is also consuming a very handsome amount of CPU and overloading the server as well. Site is also getting slower. Can you guys suggest me some tweaks which might help me to fix the issue?
Server Config: XEON 8 Core, 32 GB RAM, cPanel and nginX
|
| Posted by SentinelTower, 12-12-2014, 03:06 PM | | Hi,
If you have not done it yet, you should install a caching plugin on your wordpress so that it can avoid some queries.
For the "Copying to tmp table", you can try to increase your tmp_table_size so that the temporary tables are kept in memory instead of the disk.
|
| Posted by ShineServers, 12-12-2014, 03:23 PM | | Hi,
Can you please post the output of your /etc/my.cnf ?
Thanks
|
| Posted by tetrahost, 12-12-2014, 03:31 PM | | Here is it:
==============================
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=100000
tmp_table_size=4096M
max_heap_table_size=4096M
max_connections = 300
query_cache_size=1024M
query_cache_limit=1024M
query_cache_type=1
table_cache=4096
thread_cache_size=256
key_buffer_size=512M
wait_timeout=30
connect_timeout=50
interactive_timeout=100
#slave_net_timeout = 35
read_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=16M
thread_concurrency=4
join_buffer_size=5M
#skip-networking
innodb_buffer_pool_size=512M
#innodb_flush_log_at_trx_commit=2
#innodb_additional_mem_pool_size=1M
#innodb_force_recovery = 3
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
==============================
|
| Posted by ShineServers, 12-12-2014, 03:49 PM | | Hi,
Alright, try to use the following values:
Otherwise, you can do a another solution for tmp directory as suggested by Tristan: http://forums.cpanel.net/f5/drive-cr...tml#post799162
|
| Posted by ShineServers, 12-12-2014, 04:02 PM | | PS : Once the file is updated, restart your sql and follow these some more steps
1. mysqlcheck --check --auto-repair --all-databases
2. mysqlcheck --optimize --all-databases
3. /etc/init.d/mysql restart
The above will repair and optimise your databases, so hopefully this should work well.
|
| Posted by kevincheri, 12-12-2014, 04:04 PM | | Hi,
You should really need to consider moving your Mysql tmpdir to RAM so that the operations will be really fast http://2bits.com/articles/reduce-you...-ram-disk.html
Also consider raising tmp_table_size & query_cache
|
| Posted by huck, 12-12-2014, 04:47 PM | | What makes you think the copying to temp table is actually the bottleneck?
Have you profiled your application to break down where the latency actually resides?
Do you have a slow query log that show queries are taking too long?
I ask because people often start tweaking mysql and it is not the issue.
There's nothing inherently wrong with copying to a temporary table unless it is causing the performance bottleneck.
Drop a mysql slow query log into the system and consider using a tool like NewRelic to profile your application.
Without the right date -- its just UN-educated guessing.
|
| Posted by tetrahost, 12-13-2014, 02:28 AM | | thanks. I made the changes already in MySQL server. Lets see how it goes
|
| Posted by tetrahost, 12-13-2014, 04:00 AM | | No luck! i have setup slow query log which isn't showing anything so really not sure what is causing the tmp copying issue! the tmpdisk isn't offering any improvement as well!
Here is few log which is doing the tmp query:
Here is another input, the site was having a lots of "waiting for table level lock" query, i googled and found out that, converting the table from myisam to innodb will fix the issue...i did that and my issue was gone, no more such query! now the tmp query is also related with the same table, does that give you guys any clue for a fix?
Last edited by tetrahost; 12-13-2014 at 04:15 AM.
|
| Posted by mixmox, 12-13-2014, 01:27 PM | | these values depends on mysql version, it isnt general
also i think its not a good idea to set this value max_user_connections=1000
|
| Posted by Srv24x7, 12-14-2014, 10:31 AM | | Hi,
Do you have memcache enabled? If not, give it a try. Check the below link to have see for details on its architecture.
http://dev.mysql.com/doc/refman/5.0/...memcached.html
|
| Posted by tetrahost, 12-15-2014, 03:48 AM | | I moved to DSO/mod_php, tweaked apache, nginx and mysql which gave me a very good result and now it is not overloading server very much. But still the mysql server is using handsome amount of CPU and copying tmp table query is still there! until they are gone i cannot have peace!
any input guys?
|
| Posted by ShineServers, 12-15-2014, 04:59 AM | | These values depends on the server config he posted :- Server Config: XEON 8 Core, 32 GB RAM, cPanel and nginX
Can you please try to shift to FCGI + EAccelarator + CPNIGNX (syslint one), this will reduce your apache loads. About the SQL try MySQLTuner, it will check and suggest config's accordingly.
Thanks
|
| Posted by tetrahost, 12-20-2014, 01:24 PM | | I fixed the MySQL issue by giving MySQL to highest priority and now it is working fine without any problem Currently i am working on reducing the CPU/IO usage. I am using FCGI with nginx. any optimization tips?
|
|
 Add to Favourites
 Print this Article
|
