September 28, 2008

What is stored InnoDB buffer pool

Posted by Vadim

Ever wonder what is stored in InnoDB buffer pool at the moment ?

It is not so hard actually - we made a short patch for MySQL 5.0 which show innodb buffer pool content

[read more...]

September 12, 2008

Google’s user_statistics V2 port and changes

Posted by Vadim

Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:

      New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc)
      New command SHOW CLIENT_STATISTICS, which shows statistics per client’s hostname, not per user
      FLUSH USER_ and CLIENT_STATISTICS commands

Our port includes:

  • INFORMATION_SCHEMA tables - USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, TABLE_STATISTICS
  • Access protection - USER_STATISTICS, CLIENT_STATISTICS available only for user with SUPER or PROCESS privileges. INDEX_STATISTICS, TABLE_STATISTICS shows only information of tables that current user has access to (SELECT privileges required). This is important for hosting providers

[read more...]

September 9, 2008

The performance effects of new patches

Posted by Vadim

We are going to show the effects of the new patches applied to Percona HighPerf release. As you see from the following graphs, there is significant difference to normal version when the data bigger than buffer pool (right graph shows CPU usage)

[read more...]

August 4, 2008

Userstats patches with information schema support

Posted by Evgeniy

Recently, we added information schema support to Google’s userstats patch.

There are three information schema tables added: user_statistics, table_statistics, index_statistics.

One can now use select * from information_schema.user_statistics along with show user_statistics.

Links:
Patch for 5.0.62
Patch for 5.1.26

April 24, 2008

MySQL extensions for hosting

Posted by Maciej Dobrzanski

A few weeks ago I was asked to isolate some functionalities from Mark Callaghan’s MySQL patch bundle. They were extensions adding per-user and per-table accounting to the database, accessible with a new set of commands such as SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS and SHOW USER_STATISTICS. The first two can interest anyone to periodically check what data or which index are the most active or which are not being used at all and could be candidates for dropping. All the patch features will surely be a great help for hosting providers to produce detailed reports on how each customer utillizes the database.
[read more...]

April 20, 2008

Updated msl (microslow) patch, installation walk-through!

Posted by Maciej Dobrzanski

For a couple of months there have been no updates to our msl patch, however recently I managed some time to change this. The functionality was extended a little bit and what’s even more important the patch is available for all the recent MySQL releases.

To remind anyone who has not yet come across this piece of code. msl (microslow) patch was developed a few years ago to allow more precise logging of query execution times into the slow log. Originally MySQL database offered a second time resolution and also a 2 second minimum for the query to get written, that is when you set long_query_time=1. After applying the patch you could see whether the time was 0.005s or 0.9s, which can make a substantial difference for the database and application performance. Over time msl patch grew with new features to let people learn more details of query execution, normally hidden from everyone’s eyes. Currently it’s used by many DBAs and developers to help in optimizations or troubleshooting the applications. We ourselves often rely on it when doing commercial MySQL consulting and MySQL AB has even adopted it into the official database distribution starting 5.1.21 release.
[read more...]

October 31, 2007

The new cool MySQL patch has landed! Check your queries performance!

Posted by Maciej Dobrzanski

Microslow patch is used by many DBAs and developers to accurately time their queries and to catch those which run less than a second as they can also be a performance killer for a busy application.

Recently I have started the development of an updated version of the patch. The basic idea is the same as for its predecessor - to get more information about query execution logged into slow log, however the new version is loaded with a set of cool new features.
[read more...]

August 29, 2007

Guess what?! Microsecond slow query timing finally available in MySQL!

Posted by Maciej Dobrzanski

Vadim asked me yesterday to update Microslow patch for 5.1.21, because the previous one I wrote for 5.1.20 failed to apply correctly on the new MySQL release. Imagine the expression on my face after I unpacked the sources and found out that MySQL incorporated the patch to their release. So it’s a built-in feature now in the 5.1 line. So far I noticed one addition to my code which is logging the microtime statistics into TABLE type log storage.

Maciek

July 18, 2007

Microslow patch for 5.1.20

Posted by Maciej Dobrzanski

Microslow patch has been there for some time, but only for earlier MySQL editions such as 4.1 and 5.0. Now it’s also available for the latest 5.1.

Because MySQL went through a lot of internal changes, the patch had to be written from scratch. It introduces some minor change in existing functionality and also adds new.

For the patch to work you must of course enable slow logging with --slow-query-log parameter. MySQL 5.1 has this nice feature which allows you to redirect query log (it’s actually called general log now) and slow log to CSV tables mysql.general_log and mysql.slow_log respectively. The patch will not work as intended with those tables, because there is no space for storing 64bit integers for time with microsecond resolution. Instead you should redirect the output for your logs to the plain old-fashioned files. In MySQL 5.1 it’s done by setting --log-slow-queries=slow.log and --log-output=FILE.

Also you need to configure so called long query time. It’s done either with startup parameter --long_query_time=n or by setting MySQL system variable with SET [SESSION|GLOBAL] long_query_time=n. The value is in microseconds (0 means to log all queries).

The new feature is that patch can allow you to log queries executed by the slave thread. This can be achieved by running MySQL with startup time parameter --log-slow-slave-statements. Please note, that any runtime changes of long_query_time or min_examined_row_limit variables will take effect on the slave thread only after it’s restarted with SLAVE STOP and SLAVE START commands.

The change in functionality is more of a bugfix than anything else. long_query_time value was meant to set time limit in microseconds, but in the end it was always compared to seconds of query execution time. Now it’s fixed, so for example you can log only those queries that run 0.3s or more with long_query_time=300000.

Maciek

June 6, 2007

Microslow patch for 5.0.37

Posted by Vadim

Just short message that patch enables microsecond resolution in slow-log (see more http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/) for 5.0.37 is available here:
www.mysqlperformanceblog.com/files/patches/patch.slow-micro.5.0.37.diff

The patch for 5.0.41 :
www.mysqlperformanceblog.com/files/patches/patch.slow-micro.5.0.41.diff


This page was found by: mysql microslow mysql this log type ...