mysql optimization -- Copying to tmp table on disk

yoachan · 11-16-2010, 02:31 AM

Dear all,

we had a CENTOS 5.5 x86_64 machine with 8GB RAM, powered with MySQL 5.0.91-community.

Currently we had a very high number of Created_tmp_disk_tables (31k in 4,5 hours!!!).
I've read suggestion that we need to increase tmp_table_size, and we've set tmp_table_size to 64M, but this drupal modul's query still cause mysql create tmp table on disk

Code:

SELECT DISTINCT node.nid AS nid,
        comments.subject AS comments_subject,
            comments.cid AS comment"

Finally, I desperately increase tmp_table_size to 512M, but mysql still create disk tmp tables instead of memory temporary table. (But currently we switched it back to 64M because 512M was insane).

This thing become a problem because several times when high request occurs, tmp usage increased to several Gigs, and caused mysql crashed.

does anyone has any suggestion(s)?

any help appreciated.

regards,

yoachan

szboardstretcher · 11-16-2010, 03:14 PM

My first suggestion is to make a filesystem of type tmp_fs and mount it. Then point mysql at it, to make its tmp tables there.

tmp_fs is a ramdrive, and so is made of ram. Therefore, mysql will still be writing tmp-tables out to ram, even though it thinks it is writing to disk.

http://www.thegeekstuff.com/2008/11/...tmpfs-on-linux

yoachan · 11-17-2010, 02:51 AM

Quote:

Originally Posted by szboardstretcher

My first suggestion is to make a filesystem of type tmp_fs and mount it. Then point mysql at it, to make its tmp tables there.

tmp_fs is a ramdrive, and so is made of ram. Therefore, mysql will still be writing tmp-tables out to ram, even though it thinks it is writing to disk.

http://www.thegeekstuff.com/2008/11/...tmpfs-on-linux

It doesn't answer the real question,
but it sure is a good alternate solution to speedup the swap process.
thanks for sharing

szboardstretcher · 11-18-2010, 01:36 PM

Quote:

Originally Posted by yoachan

It doesn't answer the real question,
but it sure is a good alternate solution to speedup the swap process.
thanks for sharing

You are creating many TMP tables, that are eventually swapping to physical disk. Therefore, if you create a ramdisk, and lower the max size of tmp tables, they will be written to memory/ramdisk - thus staying in ram. This is one solution directly related to your problem.

Also, to figure out what your configuration values should be, take a look at this:

http://www.sitebuddy.com/Mysql/tmp_t...ad_buffer_size

yoachan · 11-23-2010, 09:24 AM

Quote:

Originally Posted by szboardstretcher

You are creating many TMP tables, that are eventually swapping to physical disk. Therefore, if you create a ramdisk, and lower the max size of tmp tables, they will be written to memory/ramdisk - thus staying in ram. This is one solution directly related to your problem.

Also, to figure out what your configuration values should be, take a look at this:

http://www.sitebuddy.com/Mysql/tmp_t...ad_buffer_size

Yes, it's in deed related

But my main question is why it dump the table to disk, instead of memory.
The link that you gave was so helpful.
Instead of only increasing tmp_table_size, I need to increase MAX_HEAP_TABLE_SIZE too. And currently everything is working as I expected.

Thanks.

-- case closed --