Tmp files stuck my VPS

[galante]

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.

[trq]

This is quite normal, what exactly is the issue?

[galante]

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.

[fenway]

I'm fairly sure ALTER TABLE does that.

[galante]

I'm fairly sure ALTER TABLE does that.

 

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

[fenway]

Maybe your TEMPDIR is too small / running out of space.

[galante]

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.

[premiso]

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?

[galante]

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?

[fenway]

I mean the temp folder that mysql is set up to use.

 

If you're concerned, then turn on the query log.

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

[fenway]

Yeah, wordpress has no idea how to write proper mysql queries.