galante Posted January 27, 2012 Share Posted January 27, 2012 Hello, I have an issue with MySQL for last few weeks. We use wordpress 3.3.1 on our site, MySQL 5.0.77, and we have a problem with MySQL tmp files. Sometimes during a day, db creates 30-40 of MYI and MYD, each MYI up to 100MB. Files are like #sql_somethingsomething.MYI This process is taking all our resources for quite some time, and we have nice VPS - 4 cores, 2 GB RAM. I have to mention, we do have a lot of things in database - 5000 posts, 75.000 comments, 3.000 users. Our my.cnf is now like this: [mysqld] port = 3306 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock skip-locking user=mysql local-infile=0 tmp_table_size = 32M max_heap_table_size = 32M query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_connections=500 delayed_insert_timeout=40 interactive_timeout=30 wait_timeout=30 connect_timeout=20 thread_cache_size=64 key_buffer=32M ## 32MB for every 1GB of RAM join_buffer=1M max_connect_errors=20 max_allowed_packet=16M table_cache=1024 record_buffer=1M sort_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=1M thread_concurrency=2 myisam_sort_buffer_size=16M skip-innodb skip-bdb long_query_time = 1 log-slow-queries=/var/lib/mysql/slow.log old_passwords=1 #set-variable = max_allowed_packet=32M #set-variable = max_connections=400 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash Thanks for any help. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/ Share on other sites More sharing options...
trq Posted January 27, 2012 Share Posted January 27, 2012 This is quite normal, what exactly is the issue? Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311648 Share on other sites More sharing options...
galante Posted January 27, 2012 Author Share Posted January 27, 2012 I do not get it.. what is normal? Our vps get stucked few times a day, sometimes for 15 minutes, sometimes for 1 hour, we can not open a single page on website while those files are being created in tmp folder.. In just a few minutes at least 20-30 MYI files are being created and in 5-10 minutes each is having up to 100MB. After a while, they are deleted, but in the meantime, server is overloded and we can not access it. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311656 Share on other sites More sharing options...
fenway Posted January 27, 2012 Share Posted January 27, 2012 I'm fairly sure ALTER TABLE does that. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311720 Share on other sites More sharing options...
galante Posted January 27, 2012 Author Share Posted January 27, 2012 I'm fairly sure ALTER TABLE does that. I do not understand.. how do you mean? What can i do to stop this..? Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311737 Share on other sites More sharing options...
fenway Posted January 27, 2012 Share Posted January 27, 2012 Maybe your TEMPDIR is too small / running out of space. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311738 Share on other sites More sharing options...
galante Posted January 27, 2012 Author Share Posted January 27, 2012 Maybe your TEMPDIR is too small / running out of space. You mean tmp folder on my vps? It doesnt have any limits and i have 30GB free space.. We tried so many things. I even deleted 10.000 comments on some old posts, optimized tables as much as possible, deleted everything not needed from db... but problem stil persists. Someone told me it can be sql injection atack. We found that one of the plugins had security issue, it was discovered few weeks ago.. these days we also started to have problems. But i disabled that plugin, i disabled them all.. i even restored my db from 20 days earlier. Problem persists. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311742 Share on other sites More sharing options...
premiso Posted January 27, 2012 Share Posted January 27, 2012 Did you do password resets on the database for the main accounts etc? If you didn't I would suggest doing so, if you were sql injected, they may have the password(s). Are you constantly running an alter table in your code? Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311745 Share on other sites More sharing options...
galante Posted January 27, 2012 Author Share Posted January 27, 2012 Did you do password resets on the database for the main accounts etc? If you didn't I would suggest doing so, if you were sql injected, they may have the password(s). Are you constantly running an alter table in your code? Yes, i've reseted pass for my account which im using for this particular database, but i didnt reset pass for root user for MySQL. Ill do that also. I think im not using alter db anywhere.. its wordpress. It shouldnt alter databases each day.. right? But one thing i noticed is that problem occured for 4-5 days at 14.00 GMT +1. Before that time everything worked fine, and at 14.05, 14.07.. problem started to happen. After 15-30 minutes, or hour, problem dissapeared, files where deleted. It happened even when i disabled all my plugins. Thats why i thought its sql injection attack, and then i found out that one of my plugins is having security issue. Is there any chance to remove that code from sql injection if that really happened? And how to find it? Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311749 Share on other sites More sharing options...
fenway Posted January 27, 2012 Share Posted January 27, 2012 I mean the temp folder that mysql is set up to use. If you're concerned, then turn on the query log. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1311786 Share on other sites More sharing options...
galante Posted January 29, 2012 Author Share Posted January 29, 2012 Query log was turned on actually, but we couldn't find the problem so easily.. at the end, we found out that there are few wordpress queries that slow down the site, and many other users experienced same problems.. Queries were like this: SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND ( wp_posts.ID IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (1,461) GROUP BY object_id HAVING COUNT(object_id) = 2 ) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 6; I've been told that queries like this go into all rows in the wp_posts and wp_comments tables, and we had there at least 80.000 rows. When we shut down MySQL, MySQL log returned something like this - Sorting aborted, and at least 10 threads like this.. After we deleted nearly 40.000 comments from our database, site started to work normally.. well, in the past 36 hours it's working fine, and it's a new record i would say, after two weeks of problems.. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1312323 Share on other sites More sharing options...
fenway Posted January 30, 2012 Share Posted January 30, 2012 Yeah, wordpress has no idea how to write proper mysql queries. Quote Link to comment https://forums.phpfreaks.com/topic/255876-tmp-files-stuck-my-vps/#findComment-1312620 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.