tmp_table_size and max_heap_table_size

[.Nuno.]

Hi,

I've been optimizing my VPS with mysqltuner and tuning-primer and I've increased tmp_table_size and max_heap_table_size to a point that I don't understand it's behavior.

At this moment I have 112MB but with lower values (16MB) I get almost the same results:

Temporary tables created on disk: 29% (358 on disk / 1K total)

I read that Blob and Text fields are always created on disk, so maybe this is the problem since there are lot's of text fields

Any ideas?

Thanks

[Patrick.K]

Yup blob and text not allowed in memory, raising RAM wil not make less tables on the disk from own experience.

Here's mine

TEMP TABLES

Current max_heap_table_size = 512 M

Current tmp_table_size = 512 M

Of 50023 temp tables, 49% were created on disk

Perhaps you should increase your tmp_table_size and/or max_heap_table_size

to reduce the number of disk-based temporary tables

Note! BLOB and TEXT columns are not allow in memory tables.

If you are using these columns raising these values might not impact your

ratio of on disk temp tables.

//Edit, btw can anyone explain the lock wait ratio?

TABLE LOCKING
Current Lock Wait ratio = 1 : 32
You may benefit from selective use of InnoDB.

[Luis Manson]

IIRC MyISAM does table lock on insert/update, so any read will have to wait before being run, while InnoDB does record locking, which should allow selects/update/insert with no problems in the rest of the table

[.Nuno.]

My vps is small, It's a 512 Linode and works very well with myisam.
If I convert post tables to innodb, the performance will be worst or I need to upgrade.

I changed both vars to it's default values.
Lets see how it goes.