galante

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..

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?

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.

I do not understand.. how do you mean? What can i do to stop this..?

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.

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.