[Beans0063]

Thanks for the comments. The database is poorly performing and I believe MONyog is pointing to some trouble areas. However I am not familiar enough with MySQL to further tune the system.

Is your company available to consult on database optimization, or have you worked with anyone you might recommend for private consulting?

[Beans0063]

Thanks for your replies.

Do you have any advice on how to resolve high disk usage for temporary tables? I've followed the MONyog advice of raising tmp_table_size and max_heap_table_size (to ridiculously large numbers), yet 98% of my temp tables are still written to disk. Clearly something is out of whack with all the free mem and less than 1MB temp tables, all written to disk?

Maximum Size Allowed (Per Client): 256.00M

Maximum Size of a MEMORY table: 256.00M

Total Tables Created: 863.84K

Created on Disk: 850.93K

Disk:Total Ratio : 98.5%

Group

Temporary Tables

Name

Disk:Total Ratio

Description

The %age of temporary tables that got created on disk because of insufficient tmp_table_size or max_heap_table_size.

MySQL creates temporary tables internally for executing various tasks. If the size of a temporary table exceeds tmp_table_size or max_heap_table_size, MySQL creates the temporary files in disk instead of memory.

Formula

Created_tmp_disk_tables / Created_tmp_tables

Mail Alert Enabled?

Yes

Warm-up Required?

Yes

Thresholds

Warning = 50

Critical = 75

Advice

If this value is high, you should consider increasing the value of tmp_table_size and max_heap_table_size. MySQL uses the lowest of these variables to determine whether a temporary table can be created in memory. Excessive temporary tables on disk are bad for performance.

Temporary tables having TEXT or BLOBS are always placed in disk, so try to see if you can change TEXT or BLOBs to some other column types.

[Author]

Posts