Author Topic: Temporary table disk usage problem  (Read 1655 times)

Offline Keeper

  • Semi-Newbie
  • *
  • Posts: 74
Temporary table disk usage problem
« on: May 15, 2010, 05:58:18 AM »
For some reason on my server MySQL is creating almost all temporary tables on disk. The server is running SMF 1.1, vBulletin 3.8, MediaWiki 1.15 and several other smaller applications, smaller as in database size and less used.

SMF has been optimized as instructed here, InnoDB, tinytext conversion, etc, etc. MediaWiki uses InnoDB for all but few tables. vBulletin I don't really know about as I don't admin that one. But I can see it uses MyISAM for all tables. I've also complained to its admin that all attachments, avatars, profile pics and CSS is stored to database instead of disk. But he has yet to do anything about that. Still, I don't know if that alone would make temporary table disk usage statistics so bad. The vBulletin on my server is smaller forum than the SMF.

Code: [Select]
Temporary table disk usage: 0.9012
Created_tmp_disk_tables: 40407
Created_tmp_files: 4897
Created_tmp_tables: 44841
MySQL has been running less than 48 hours at the time of typing this, but ratio doesn't change much over time. I've tried 32M, 64M and even 128M as values for tmp_table_size and max_heap_table_size. But the ratio is always about same. So it's not the size what is problem here, it's something else. But what?

If you have any clues or suggestions, thanks in advance. :)