When cache is deleted/optimized my /tmp directory fills and mysql crashes

[tavenger5]

As the title says. I have to reboot mysql, which clears the tmp directory and empty the table that crashed. This only happens on the tables that are very large, like vbenterprisetranslator_cache_short_ru, which is over 3.3 million records and 500mb.

My cache ttl is 15 days. Should I make it shorter?

[r.dziadusz]

Please take a look on this thread: http://www.vbenterprisetranslator.com/forum/vbet4-general-discussions/408-vbet-performance.html

[tavenger5]

Right - I have all performance suggestions implemented. The question is; what can I do to prevent /tmp from running out of space when these tables are being optimized ('repair by sort' ) ?

[r.dziadusz]

Ok, you should change your Cache clearing strategy, please find out which of them is the best for you, short description you can find there:

admincp-> vBet Cache -> Cache clearing strategy
It is not recomended to delete all cache data after TTL interval, try to use other strategy. Of course you can try to make ttl shorter, but i suggest you to, at first try out other clearing strategy. This should help, if not let us know

[vBET]

Right - I have all performance suggestions implemented. The question is; what can I do to prevent /tmp from running out of space when these tables are being optimized ('repair by sort' ) ?

Just as Radek wrote you - you can use other clearing strategy. Making TTL shorter will degrade your performance, because more often your forum will ask for outside translation.
About /tmp folder - you can also ask MySQL support/forums about the issue. It is clearly our of vBET scope. We are ready for big forums and allow you to use different clearing strategies for faster cleanings on large boards. Still if your MySQL crashes this is MySQL issue. Or maybe it is your OS issue - please try to allow for bigger temporary files.

[tavenger5]

I've actually tried different clearing strategies. The bottleneck is when a very large table is optimized after the deletion.

[tavenger5]

I think I figured it out. My tmp directory was only 500mb. When a table close to 1gb was optimized it needed more space then that to do all the optimization. So instead of increasing the size of the tmp directory I created a new one that uses RAM instead of disk according to these instructions: themattreid.com/ wordpress/2011/04/04/mysql-and-ramdisk-or-how-to-make-tmpdir-usage-queries-faster/

Since a lot of larger queries make tmp files to execute this will speed up that process as well.

[r.dziadusz]

To clarify, is this problem solved, for any other problems please open new threads

[r.dziadusz]

No answer, we consider this problem solved, if you will have any question for this issue we will reopen it, for any other question please open new thread