Dave Stokes posted earlier that the official list of MySQL Certified will be going away. There has been a MySQL Certified Professionals LinkedIn group for almost 2 years now. It's community managed by myself and Dave, and is open to all MySQL Certified DBAs & Developers, as well as recruiters & hiring managers.
-- Mark
mark@thetajoin.com
http://www.thetajoin.com - The Drupal Hosting & Performance Company
Saturday, October 31, 2009
Thursday, August 13, 2009
Fun With Information Schema!!
Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:
Calculate index sizes
mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'database';
+------------------+
| Total Index Size |
+------------------+
| 1.70 GB |
+------------------+
1 row in set (1.60 sec)
To calculate the total size of the data in the database
mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'database';
+-----------------+
| Total Data Size |
+-----------------+
| 3.01 GB |
+-----------------+
1 row in set (1.35 sec)
An overall analysis of entire database on a per table basis
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
CONCAT(ROUND(table_rows/1000000,2),'M') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') AS 'Index Size' ,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'database';
Just replace database with the partial name of your database you need to analyze. Yes, I know, those wonderful Maatkit tools contains mk-find which can do the same thing, but then you won't learn about the information_schema database!!
Calculate index sizes
mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'database';
+------------------+
| Total Index Size |
+------------------+
| 1.70 GB |
+------------------+
1 row in set (1.60 sec)
To calculate the total size of the data in the database
mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size'
FROM information_schema.TABLES WHERE table_schema LIKE 'database';
+-----------------+
| Total Data Size |
+-----------------+
| 3.01 GB |
+-----------------+
1 row in set (1.35 sec)
An overall analysis of entire database on a per table basis
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
CONCAT(ROUND(table_rows/1000000,2),'M') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') AS 'Index Size' ,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'database';
Just replace database with the partial name of your database you need to analyze. Yes, I know, those wonderful Maatkit tools contains mk-find which can do the same thing, but then you won't learn about the information_schema database!!
Friday, July 24, 2009
Portable Maatkit
What do you do when you're in a situation where you REALLY need your favorite Maatkit tools, but are not allowed to load software on the server running MySQL, AND you're not allowed to install software on the provided workstation, especially a workstation that's running software from a large corporation in Redmond? Portable Maatkit to the rescue!
DISCLAIMER: I've only tested a few maatkit tools, namely mk-find and mk-query-digest, but not completely. Some features and some tools may not function, such as mk-audit. Use your best judgment. I'm not responsible if your workstation or server looses all your data, your USB key bursts into flames, or your significant other leaves you. YMMV, use at YOUR OWN RISK!
Strawberry Perl has been out for some time now, and recently, a portable version of Strawberry Perl has been available. Download the the latest 5.10 version of Portable Strawberry Perl and unzip it onto your USB key. Also download and unzip to your USB key the latest copy of Maatkit. After unzipping everything, your USB key should look something like:
The portable version ships with DBI installed, but not DBD::mysql. To install DBD::mysql, use the Perl Package Manager (PPM) that comes with Strawberry Perl.
Change to the maatkit directory on your USB key, and prepare it for installation:
I know this really didn't do much, but it's a good check to make sure all dependancies have been met. Don't install maatkit at this point! You don't need to.
Now, take your USB key to the workstation and plug it in. Once it has been recognized, open a command prompt by going to the start button, click run, then enter cmd into the dropdown box. Navigate to the your maatkit\bin directory.
I really needed mk-find for my task. Here's how to run mk-find with portable Strawberry Perl:
Nice, no errors! Since Maatkit isn't installed.
Lets try an example:
Another example:
Yet another example:
I can also report that mk-query-digest also worked without an error. I can't show you the output to demonstrate, I just analyzed using mk-query-digest using default settings to process the slow log of a server. I'm sure most options will work, with the exception of daemon mode since this is a windows workstation.
It's incredible the flexibility Open Source tools gives a person. Portable Strawberry Perl and now Portable Maatkit will be in my toolbox. Much thanks to the Strawberry Perl and Maatkit teams!
DISCLAIMER: I've only tested a few maatkit tools, namely mk-find and mk-query-digest, but not completely. Some features and some tools may not function, such as mk-audit. Use your best judgment. I'm not responsible if your workstation or server looses all your data, your USB key bursts into flames, or your significant other leaves you. YMMV, use at YOUR OWN RISK!
Strawberry Perl has been out for some time now, and recently, a portable version of Strawberry Perl has been available. Download the the latest 5.10 version of Portable Strawberry Perl and unzip it onto your USB key. Also download and unzip to your USB key the latest copy of Maatkit. After unzipping everything, your USB key should look something like:
E:\>dir
Volume in drive E has no label.
Volume Serial Number is BEEF-FEED
Directory of E:\
01/29/2009 11:13 PM 116 Strawberry Perl Website.url
01/29/2009 11:13 PM 50 Win32 Perl Wiki.url
01/29/2009 11:14 PM <DIR> cpan
01/29/2009 10:29 PM <DIR> licenses
01/29/2009 11:14 PM <DIR> perl
01/29/2009 11:09 PM <DIR> ppm
07/23/2009 01:20 PM <DIR> maatkit-4047
01/29/2009 11:13 PM 51 CPAN Search.url
01/29/2009 11:13 PM 52 Perl 5.10.0 Documentation.url
10/16/2008 09:01 PM 1,861 portable.perl
12/24/2007 03:44 PM 1,406 Strawberry Perl Website.ico
01/29/2009 11:14 PM <DIR> c
8 File(s) 55,836 bytes
6 Dir(s) 1,848,025,088 bytes free
The portable version ships with DBI installed, but not DBD::mysql. To install DBD::mysql, use the Perl Package Manager (PPM) that comes with Strawberry Perl.
E:\>cd perl\bin
E:\perl\bin>ppm
PPM interactive shell (0.01_01) - type 'help' for available commands.
PPM> help
Commands:
exit - leave the program.
help [command] - prints this screen, or help on 'command'.
install PACKAGES - installs specified PACKAGES.
quit - leave the program.
query [options] - query information about installed packages.
remove PACKAGES - removes the specified PACKAGES from the system.
search [options] - search information about available packages.
set [options] - set/display current options.
verify [options] - verifies current install is up to date.
version - displays PPM version number
PPM> search mysql
Packages available from http://cpan.uwinnipeg.ca/PPMPackages/10xx:
DBD-mysql [4.005] A MySQL driver for the Perl5 Database Interface (DBI)
Packages available from http://ppm.activestate.com/PPMPackages/5.10-windows:
ApacheMysql [0.3 ] Initiate a persistent database connection
to Mysql
DBIx-Mysql-InformationSchema [0.04] Perl module to access the mysql
INFORMATION_SCHEMA view, which contains
database metadata.
Install it:
PPM> install DBD-mysql
Install package 'DBD-mysql?' (y/N): y
Installing package 'DBD-mysql'...
Bytes transferred: 1018351
Files found in blib\arch: installing files in blib\lib into architecture depende
nt library tree
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.bs
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.dll
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.exp
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.lib
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.pdb
Installing E:\perl\site\lib\Bundle\DBD\mysql.pm
Installing E:\perl\site\lib\DBD\mysql.pm
Installing E:\perl\site\lib\DBD\mysql\GetInfo.pm
Installing E:\perl\site\lib\DBD\mysql\INSTALL.pod
PPM>
Change to the maatkit directory on your USB key, and prepare it for installation:
E:\maatkit-4047>e:\perl\bin\perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for maatkit
E:\maatkit-4047>
I know this really didn't do much, but it's a good check to make sure all dependancies have been met. Don't install maatkit at this point! You don't need to.
Now, take your USB key to the workstation and plug it in. Once it has been recognized, open a command prompt by going to the start button, click run, then enter cmd into the dropdown box. Navigate to the your maatkit\bin directory.
I really needed mk-find for my task. Here's how to run mk-find with portable Strawberry Perl:
F:\maatkit-4047\bin>f:\perl\bin\perl mk-find --help
mk-find searches for MySQL tables and executes actions, like GNU find. The
default action is to print the database and table name. For more details,
please use the --help option, or try 'perldoc mk-find' for complete
documentation.
Usage: mk-find [option]... [database...]
Options:
--ask-pass Prompt for a password when connecting to MySQL
--case-insensitive Specifies that all regular expression searches are
case-insensitive
--charset -A Default character set
--config Read this comma-separated list of config files; if
specified, this must be the first option on the command
line
--day-start Measure times (for --mmin, etc) from the beginning of
today rather than from the current time
--defaults-file -F Only read mysql options from the given file
--help Show help and exit
--host -h Connect to host
--or Combine tests with OR, not AND
--password -p Password to use when connecting
--port -P Port number to use for connection
--[no]quote Quotes MySQL identifier names with MySQL's standard
backtick character (default yes)
--set-vars Set these MySQL variables (default wait_timeout=10000)
--socket -S Socket file to use for connection
--user -u User for login if not current user
--version Show version and exit
F:\maatkit-4047\bin>
Nice, no errors! Since Maatkit isn't installed.
Lets try an example:
F:\maatkit-4047\bin>f:\perl\bin\perl mk-find -hmysqlserver -u user --ask-pass --print
Enter password:
`mysql`.`columns_priv`
`mysql`.`db`
`mysql`.`func`
`mysql`.`help_category`
`mysql`.`help_keyword`
`mysql`.`help_relation`
`mysql`.`help_topic`
`mysql`.`host`
`mysql`.`proc`
`mysql`.`procs_priv`
`mysql`.`tables_priv`
`mysql`.`time_zone`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone_name`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_transition_type`
`mysql`.`user`
Another example:
F:\maatkit-4047\bin>f:\perl\bin\perl mk-find -hmysqlserver \
-u user --ask-pass --ctime +1 --engine MyISAM
Enter password:
`mysql`.`columns_priv`
`mysql`.`db`
`mysql`.`func`
`mysql`.`help_category`
`mysql`.`help_keyword`
`mysql`.`help_relation`
`mysql`.`help_topic`
`mysql`.`host`
`mysql`.`proc`
`mysql`.`procs_priv`
`mysql`.`tables_priv`
`mysql`.`time_zone`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone_name`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_transition_type`
`mysql`.`user`
F:\maatkit-4047\bin>
Yet another example:
F:\maatkit-4047\bin>f:\perl\bin\perl mk-find -hmysqlserver \
-u user --ask-pass --printf "%T\t%D.%N\n"
Enter password:
1024 `mysql`.`columns_priv`
4972 `mysql`.`db`
1024 `mysql`.`func`
23988 `mysql`.`help_category`
90417 `mysql`.`help_keyword`
19909 `mysql`.`help_relation`
281940 `mysql`.`help_topic`
1024 `mysql`.`host`
4628 `mysql`.`proc`
1024 `mysql`.`procs_priv`
1024 `mysql`.`tables_priv`
1024 `mysql`.`time_zone`
1024 `mysql`.`time_zone_leap_second`
1024 `mysql`.`time_zone_name`
1024 `mysql`.`time_zone_transition`
1024 `mysql`.`time_zone_transition_type`
2472 `mysql`.`user`
I can also report that mk-query-digest also worked without an error. I can't show you the output to demonstrate, I just analyzed using mk-query-digest using default settings to process the slow log of a server. I'm sure most options will work, with the exception of daemon mode since this is a windows workstation.
It's incredible the flexibility Open Source tools gives a person. Portable Strawberry Perl and now Portable Maatkit will be in my toolbox. Much thanks to the Strawberry Perl and Maatkit teams!
Tuesday, June 16, 2009
Review: MySQL Administrator's Bible
On the front cover of MySQL Administrator’s Bible is a sentence that reads:
“The book you need to succeed!”
I must say, I do agree. Authored by two very experienced DBAs, Sheeri Cabral and Keith Murphy, they’ve combined their talents to cover what you really need to know to succeed. This book is very versatile. If you’re new to MySQL, or experienced in another database and have to start administrating MySQL, you need this book. I can honestly say, even if you have years of MySQL experience, you will learn something new. I did. Divided into four parts, MySQL Administrator’s Bible covers your First Steps with MySQL, Developing with MySQL, Core MySQL Administration and Extending Your Skills.
First Steps with MySQL starts with a gentle introduction to MySQL with company information, which seems to be changing annually, and most importantly, the MySQL community itself. What makes MySQL so fantastic is the community. After that, you’ll be lead into installing and configuring MySQL on various platforms including Linux, Windows and Solaris while touching on post installation configuration too. Basic security is covered as well as some tips on troubleshooting and accessing your new MySQL installation using tools included with MySQL or using third party software.
Developing with MySQL covers the MySQL Language Structure and if you’re coming from another RDBMS, it covers how MySQL deviates from the SQL standard by extending that standard to make MySQL the number one open source database used on the Internet. After that, this section covers the same type of topics covering just about any other mainstream databases such as using stored procedures, cursors, events, views and transactions.
The Core MySQL Administration is the heart of this book. It covers MySQL server tuning, covering all major storage engines including MyISAM, InnoDB, Falcon, PBXT, and NDB engines including the first time I’ve seen in print, the Maria storage engine. An entire chapter is devoted to implementing cache tables and using the query cache. Memcached is also mentioned, and mentioned again in the final section. Continuing on with what I consider the most important job of a DBA, backup and recovery. Databases are very central to running a business, any data loss could put a company out of business. Be prepared.
This section gives a solid introduction to the topic of dealing with users, and how they are managed within MySQL. Count on covering GRANT/REVOKE, using SHOW GRANTS and mk-show-grants MaatKit tool. Partitioning, logging and replication and measuring performance rounds out this section.
If you have experience with another RDBMS, plan on spending a significant amount of time in this section. Not that the other sections aren’t important, they are, but this is the bread and butter of what a MySQL DBA does on a daily basis.
Extending You Skills section can be considered getting your Masters in Database Administration. Just about every DBA will have to tackle improving queries and the tuning of indexes. The second most important job of a DBA is monitoring performance of your MySQL server. Don’t let your users be your first line of monitoring! Be proactive, there are plenty of open source monitoring tools available. The most popular are discussed, as well as MySQL Enterprise and third party companies too. MySQL Data Dictionary is covered in in detail over 58 pages. This is the most I’ve read in any book about the data dictionary.
Last but not least, most high performance MySQL systems involve scaling up or out. It covers the usual suspects of replication, MySQL Cluster, and memcached. MySQL Proxy is initially covered and has an appendix to expand on that information. MySQL Proxy itself is worthy of its own book. (hint, hint :) ) Two more appendices cover MySQL Functions and Operators, and additional resources.
Even though this book targets MySQL 5.1/6.0, there is plenty of information that will apply to 5.0. If you’re still on 5.0, don’t hesitate to pick up a copy. This will be a book that can stay with you as your upgrade to 5.1 and beyond. The companion website – http://www.wiley.com/go/mysqladminbible contains all the code from the book too, rounding out this fine tome.
What didn’t I like about the book? There are only a couple of things, all personal I’m sure. First, I really don’t care too much for tables of options from the various tools. Most open source tools are developed rather quickly and options change. This could render portions of the book out of date quickly.
The other thing I noticed that wasn’t mentioned in the book was the community versions of MySQL supported by Open Query and Percona. The latter has their own storage engine, XtraDB and backup solution, XtraBackup.
All in all, this is a very solid book on administering MySQL. This book digs deeper, the experience of the authors really show. Well done Sheeri and Keith!
Thursday, April 23, 2009
Did You Get Your MySQL Certification ??
Did you get your certification during the User Conference? If so, be sure to stop by the MySQL Certified Professionals LinkedIn page and request membership! Congratulations on your certification!!
Wednesday, April 22, 2009
Thoughts on Oracle's Purchase of Sun
With a first day announcement at the MySQL 2009 User Conference, Oracle had purchased Sun! Reading the tweets and blogs, it's understandable that some think it's the end for MySQL, some think it's not - I think it could be both.
MySQL itself is going through a second corporate purchase inside of 18 months. When Sun purchased MySQL, some of the internal talent left. Just recently we saw the exit of Marten, and just before that Monty. What made MySQL what it was in 2008 is the people behind the database, from corporate to the community.
MySQL the database server itself is safe since it's GPLed. Anyone can have access to the code and create their own distribution. We've already seen this happen within the community, Jeremy Cole, Percona, OurDelta to name a few. The main concern that could slow or stop MySQL progress is all the programming talent leaving en masse. Put this together with the departure of all the original corporate management and MySQL is no longer MySQL the company we remember. The part we don't know for sure is Oracle's plans for MySQL. Just seems to be getting worse.
Drizzle could also be impacted since Brian Aker is a Sun employee, so is Jay Pipes, and I'm sure there are others. Some companies support Open Source Software by paying their employees to work on these projects. We don't know if Oracle will allow this to continue.
Who could be impacted by Oracle's purchase? That huge computer company in Redmond. For years, their database team has had access to the operating system source code. Gives a huge advantage to support and performance. Oracle not only gains the source code to Solaris, but also their hardware too. No company in my 22 years in IT has had access to everything - hardware, operating system and the database. It's going to be a wild ride....
The other company I think could be greatly impacted is Kickfire. Now that Oracle has all that Sun hardware talent on tap, they could also design a database appliance as well.
It's not all gloom and doom.
I see great opportunity for 3rd party companies that support MySQL. Some of these companies have former MySQL employees on staff too, and have already started contributing code to MySQL with their own patches and enhancements. This could be an opportunity for a consortium of companies to come together and support MySQL. Something like the Eclipse Foundation might work. I could also see David, Monty and Marten coming together again under a new company. Plenty of opportunities exist.
What advice can I give? First thing - don't panic. Your MySQL database servers are not going to stop working tomorrow. I'd research 3rd party companies like Percona and Pythian. Be diligent, don't wait until Oracle announces something. Like any great DBA, be proactive.
Thursday, March 12, 2009
Errata: Improving Drupal Performance
Back in November 2008, I wrote a 4 part series on improving Drupal’s MySQL Performance. In that series of posts, I used the BENCHMARK() function to demonstrate the improvement of some of the SQL that Drupal uses.
Note: Displaying commands and SQL looks horrible in Firefox, but just fine in Opera and IE. Not sure why exactly. I’ve not found a clean way to display code on blogspot.com. If anyone knows how to format code so it looks great, maybe even has line numbers, could you drop me an email? Thanks.
Baron Schwartz, AKA xparb opines:
“ BENCHMARK() doesn't work the way you think it does. The second parameter is an expression, not a string to be treated as a statement.
Try this:
set @sql := 'select sleep(5)';
select benchmark(1, @sql);
If it works as you think, it'll take 5 seconds. “
Since I’ve had a break in my studies, I have some time to do further research and respond to xparb. When I wrote up this series, BENCHMARK() was new to me, I hadn’t used it before. I definitely learned to do more diligent research prior to posting! The results of his comment?
mysql> SET @sql := 'select sleep(5)';
Query OK, 0 rows affected (0.00 sec)
| BENCHMARK(1,@sql) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
mysql>
Sure enough, he’s correct, and that was no surprise to me actually. It’s clear BENCHMARK() isn’t working like I thought, the results should have been 5 seconds. Mostly what surprises me is that he’s the only person to catch my mistake. In thinking about this further, even if it had worked, I don’t think it’s that handy at all. I mean, it’s giving only a single result for the entire process of querying MySQL, which really isn’t all that helpful. It won’t provide the detailed information such as opening closing tables, CPU utilization, sending data, etc. What can be used?
If you’re using the Community Edition of MySQL 5.0.37 or greater, there’s hope. Jeremy Cole from Proven Scaling provided a patch to enable profiling queries within MySQL. I’ll demonstrate how to use the profile function with one of the Drupal queries I’ve used in a previous article.
By default, profiling isn’t enabled. To enable profiling, issue the following in your favorite MySQL client:
mysql> SET PROFILING=1;
Query OK, 0 rows affected (0.00 sec)
mysql>
Now, run a query just like normal:
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00059500 | SELECT * FROM system WHERE type = 'theme' |
+----------+------------+-------------------------------------------+
1 row in set (0.00 sec)
SHOW PROFILES is listing the query we just ran. Looking at the Duration column, it took 0.000595 seconds to run this query. To see greater details, use the Query_ID like this:
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000078 |
| Opening tables | 0.000212 |
| System lock | 0.000007 |
| Table lock | 0.000010 |
| init | 0.000045 |
| optimizing | 0.000014 |
| statistics | 0.000016 |
| preparing | 0.000016 |
| executing | 0.000005 |
| Sending data | 0.000151 |
| end | 0.000006 |
| end | 0.000004 |
| query end | 0.000005 |
| freeing items | 0.000010 |
| closing tables | 0.000006 |
| logging slow query | 0.000005 |
| cleaning up | 0.000005 |
+--------------------+----------+
17 rows in set (0.00 sec)
This returned detailed information on how this query is performing. We can clearly see Opening tables and Sending data are two of the most time consuming parts of this query. Running a query a single time doesn’t provide enough data to judge the performance of a query. Putting together a Perl script to run the query multiple times, or during times of heavy use could provide some insight to make improvements.
Note: Displaying commands and SQL looks horrible in Firefox, but just fine in Opera and IE. Not sure why exactly. I’ve not found a clean way to display code on blogspot.com. If anyone knows how to format code so it looks great, maybe even has line numbers, could you drop me an email? Thanks.
Baron Schwartz, AKA xparb opines:
“ BENCHMARK() doesn't work the way you think it does. The second parameter is an expression, not a string to be treated as a statement.
Try this:
set @sql := 'select sleep(5)';
select benchmark(1, @sql);
If it works as you think, it'll take 5 seconds. “
Since I’ve had a break in my studies, I have some time to do further research and respond to xparb. When I wrote up this series, BENCHMARK() was new to me, I hadn’t used it before. I definitely learned to do more diligent research prior to posting! The results of his comment?
mysql> SET @sql := 'select sleep(5)';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT BENCHMARK(1,@sql);
| BENCHMARK(1,@sql) |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
mysql>
Sure enough, he’s correct, and that was no surprise to me actually. It’s clear BENCHMARK() isn’t working like I thought, the results should have been 5 seconds. Mostly what surprises me is that he’s the only person to catch my mistake. In thinking about this further, even if it had worked, I don’t think it’s that handy at all. I mean, it’s giving only a single result for the entire process of querying MySQL, which really isn’t all that helpful. It won’t provide the detailed information such as opening closing tables, CPU utilization, sending data, etc. What can be used?
If you’re using the Community Edition of MySQL 5.0.37 or greater, there’s hope. Jeremy Cole from Proven Scaling provided a patch to enable profiling queries within MySQL. I’ll demonstrate how to use the profile function with one of the Drupal queries I’ve used in a previous article.
By default, profiling isn’t enabled. To enable profiling, issue the following in your favorite MySQL client:
mysql> SET PROFILING=1;
Query OK, 0 rows affected (0.00 sec)
mysql>
Now, run a query just like normal:
mysql> SELECT * FROM system WHERE type = 'theme' ;
mysql>
mysql> SHOW PROFILES;+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00059500 | SELECT * FROM system WHERE type = 'theme' |
+----------+------------+-------------------------------------------+
1 row in set (0.00 sec)
SHOW PROFILES is listing the query we just ran. Looking at the Duration column, it took 0.000595 seconds to run this query. To see greater details, use the Query_ID like this:
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000078 |
| Opening tables | 0.000212 |
| System lock | 0.000007 |
| Table lock | 0.000010 |
| init | 0.000045 |
| optimizing | 0.000014 |
| statistics | 0.000016 |
| preparing | 0.000016 |
| executing | 0.000005 |
| Sending data | 0.000151 |
| end | 0.000006 |
| end | 0.000004 |
| query end | 0.000005 |
| freeing items | 0.000010 |
| closing tables | 0.000006 |
| logging slow query | 0.000005 |
| cleaning up | 0.000005 |
+--------------------+----------+
17 rows in set (0.00 sec)
mysql>
This returned detailed information on how this query is performing. We can clearly see Opening tables and Sending data are two of the most time consuming parts of this query. Running a query a single time doesn’t provide enough data to judge the performance of a query. Putting together a Perl script to run the query multiple times, or during times of heavy use could provide some insight to make improvements.
Just a quick post to fix my error, and to introduce query profiling that’s available in MySQL Community Edition 5.0.37 or greater. Thanks to xparb for pointing this error out, and to Jeremy for creating this profiling patch.
Now, back to my studies.
Subscribe to:
Posts (Atom)
