Monday, 12 March 2007

MySQL 5.0.37 Community Server, Contributions and Binaries

Finally!

We have a release of MySQL Community Server that contains community-provided contributions and it is ready for your download in binary form.

Thanks to Jeremy Cole for providing us with the SHOW PROFILE patch. Quoting our Reference Manual at http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html:


SHOW PROFILES

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT n [OFFSET n]]

type:
ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS


The SHOW PROFILES and SHOW PROFILE statements display profiling information that indicates resource usage for statements executed during the course of the current session.

Profiling is controlled by the profiling session variable, which has a default value of 0 (OFF). Profiling is enabled by setting profiling to 1 or ON:

mysql> SET profiling = 1;
SHOW PROFILES displays a list of the most recent statements sent to the master. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling.

All statements are profiled except SHOW PROFILES and SHOW PROFILE, so you will find neither of those statements in the profile list. Malformed statements are profiled. For example, SHOW PROFILING is an illegal statement, and a syntax error occurs if you try to execute it, but it will show up in the profiling list.

SHOW PROFILE displays detailed information about a single statement. Without the FOR QUERY n clause, the output pertains to the most recently executed statement. If FOR QUERY n is included, SHOW PROFILE displays information for statement n. The values of n correspond to the Query_ID values displayed by SHOW PROFILES.

The LIMIT n clause may be given to limit the output to n rows. If LIMIT is given, OFFSET n may be added to begin the output n rows into the full set of rows.

Some sample output:

mysql> SHOW PROFILES;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 0 | 0.000088 | SET PROFILING = 1 |
| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |
| 2 | 0.011947 | CREATE TABLE t1 (id INT) |
+----------+----------+--------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table | 0.000056 |
| After create | 0.011363 |
| query end | 0.000375 |
| freeing items | 0.000089 |
| logging slow query | 0.000019 |
| cleaning up | 0.000005 |
+----------------------+----------+
7 rows in set (0.00 sec)



This information is also visible in the corresponding INFORMATION_SCHEMA.PROFILING table.

Unfortunately the SHOW PROFILE implementation as of 5.0.37 still has a small gotcha, that we will resolve for the next release: the MySQL command line client hangs if you issue the SHOW PROFILE command without having enabled the @@profiling session variable before. Hence, when you evaluate this new feature, make sure to enable it by issuing "SET @@profiling = 1;" before.

In summary, the SHOW PROFILE patch provides a way for the MySQL Community Server users to get a more detailed understanding of where the response time is formed. Jeremy also contributed a patch to display the Uptime_since_flush_status status variable, which indicates the number of seconds since the most recent FLUSH STATUS statement. Thanks, Jeremy!

Looking beyond the community enhancements, MySQL Community Server 5.0.37 is our second full (source and binary) release of the MySQL Community Server since we made the split between the Community and Enterprise Version in October 2006. It includes all bug fixes applied to up to and including the MySQL 5.0.36 Enterprise Server. The prior MySQL Community Server 5.0.33 release early January 2007 was a source-only release.

The 5.0.37 release also resolves a crashing bug that could be exploited as a potential local Denial of Service attack. Thank you, Stefan Streichsbier from SEC Consult, who also informed us about this bug via our security@mysql.com mail address - he will send out a separate advisory about his findings.

We welcome and appreciate your feedback, bug reports, bug fixes, patches etc. as described on MySQL Forge on http://forge.mysql.com/wiki/Contributing.

1 comment: