Great Info, But Now What?

[peterlaursen]

We will provide an option to analyze the SLOW LOG from inside MONyog soon.

With version 1.1 (beta1) we support SHOW FULL PROCESSLIST from inside MONyog and you can monitor the queries in real time, and EXPLAIN them.

Please refer to the 1.1 beta1 release notes: http://www.webyog.com/forums//index.php?sh…view=getnewpost

[Rohit]

“MySQL has a great feature – slow query log, which allows you to log all queries which took more than a specified amount of time to execute.

The slow query log can be used to identify queries that take long time to complete. Enabling the slow query log is highly recommended as it allows you to record unoptimized queries.

Enable the slow query log. Refer to http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html for more information on the slow query log.”

The above advice is available in MONyog for enabling the Slow Query Log. We would also modify the advice for “Percentage of Full Table Scans” to include this information.

MONyog 2.0 will be able to read the log files and will generate various reports to help quickly identify the problem SQL. MONyog 2.0 BETA is 6-8 weeks away.

[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.

[peterlaursen]

your applications don't use BLOBs/TEXTs extensively?

(it is a common problem with 'standard' PHP applications like this Forums for instance. If so there is not much to do – except hoping that the developers of such software will use MONyog on their test servers to optimize their software!)

[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?

[Rohit]

We don't provide consultancy, but I am a huge fan of Peter Zaitsev's MySQL Performance Blog (www.mysqlperformanceblog.com). Peter's company does provide MySQL performance consultancy.

[Author]

Posts