Have you wanted to compress only certain types of columns in a table while leaving other columns uncompressed? While working on a customer case this week I saw an interesting problem where a table had many heavily utilized TEXT fields with some read queries exceeding 500MB (!!), and stored in a 100GB table. In this case we were not allowed to make any query or application logic changes so we chose to implement the Barracuda file format and utilize compressed rows as this appealed to me for this mostly-read application. One quick way you can see if your rows will benefit from compression would be to read Peter Zaitsev’s blog post and execute:

SELECT AVG(LENGTH((`colTextField`)) FROM `t1` WHERE `id` < 1000

compare this to:

SELECT AVG(LENGTH(COMPRESS(`colTextField`))) FROM `t1` WHERE `id` < …

I’m excited by all of the recent developments surrounding the Percona Live MySQL Conference and Expo! Our own Baron Schwartz will moderate the Diamond Keynote Panel entitled “Future Perfect: The Road Ahead for MySQL” which will feature a panel of MySQL industry leaders, including: Sundar Raghavan, director product management at Amazon; Paul Mikesell, CEO of Clustrix; a representative from HP; and, a representative from McAfee. The Diamond Sponsor Keynote Panel will take place at 9:30 a.m. on Thursday, April 12th and provide insight into the future of MySQL technology, adoption, and the ecosystem landscape. I am also very pleased to introduce two new sponsors including McAfee which recently joined as a Diamond Sponsor and AOL which …

The Percona Live MySQL Conference & Expo is going to be awesome! Great speakers, an A-list of sponsors, countless opportunities to engage with the community, and an enthusiastic crowd of MySQL users ensure this is going to be a great event. The conference features 72 breakout sessions, keynotes by leading industry luminaries, an optional day of 16 tutorial sessions, a bustling exhibit hall, and numerous opportunities to connect with other community members.

I am pleased to announce the conference Birds of a Feather sessions and Lightning Talks. Birds of a Feather sessions will be Tuesday and Wednesday nights following the evening receptions. Lightning Talks will be …

SHOW PROFILES shows how much time MySQL spends in various phases of query execution, but it isn’t a full-featured profile. By that, I mean that it doesn’t show similar phases aggregated together, doesn’t sort them by worst-first, and doesn’t show the relative amount of time consumed.

I’ll profile the “nicer_but_slower_film_list” included with the Sakila sample database to demonstrate:

mysql> SET profiling=1;
mysql> pager cat > /dev/null
mysql> SELECT * FROM nicer_but_slower_film_list;
997 rows in set (0.18 sec)

The query consumed 0.18 seconds. Where did the time go?

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000032 |
| checking permissions | 0.000005 |
... snip ...
| init                 | 0.000021 |
| optimizing           | 0.000003 |
| statistics …

Wouldn’t it be nice if you could get expert advice on your SQL queries to find problems in them, the same way that programmers can use lint-check tools to warn about bugs in their C?

if ( execute = 1 ) {
   launch_missile();
}

Such a simple mistake, but it’s the kind of thing that James Bond movies are made of, isn’t it? Well, a lot of SQL queries have similar bugs, and thanks to Miguel Trias, now there’s a tool to help you find them. This is the second addition to our online suite of tools for MySQL users. You paste a query, it tells you what’s wrong with it. Simple as that.

Find the bug in this query:

select * from t1
   left join t2 using(id)
where t2.created_date < 2012-02-15;

Do you see it? Congratulations! I've analyzed that query with the tool, and shared the results with you. …

We will be holding our highly acclaimed MySQL workshops in Frankfurt, Germany the week of February 13th. Early registration is open; enroll today and secure your seat. Detail and enrollment can be found here.

Many of you have seen the announcement for our Percona Live in Washington, DC in January! But did you know we also have our highly rated MySQL Training coming to Washington, DC the week of January 16h? Full details can be found on the Percona website. If you would like to attend the MySQL Training, click here to register and receive a 20% discount.

An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual:

1. Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their corresponding queries from INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.STATISTICS)
2. When 1 / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed

…

There are many ways of improving response times for users. There are some people that spend a lot of time, energy and money on trying to have the application respond as fast as possible at the time when the users made the request.

Those people may miss out on an opportunity to do some or all of the processing the application needs to do at a different point in time. In other words, if you preprocess your data ahead of time, you can reduce the time it takes to complete a request.

Allow me to give you three examples of what I mean:

1)  There is a sales report that your managers would like to see on their fancy new dashboards. The query for this report takes 45 minutes to run and may disrupt other functions that the database server needs to do. You decide to run this report at 3am when there is very little happening on the database server and save the results to a separate table. When the dashboard …

Released on June 8, 2011 (Downloads are available here and from the Percona Software Repositories.

Percona Server 5.1.57-12.8 is now the current stable release in the 5.1 series. It is is based on MySQL 5.1.57.

Bug Fixes

• Fixed InnoDB I/O code so that the interrupted system calls are restarted if they are interrupted by a signal. InnoDB I/O code was not fully conforming to the standard on POSIX systems, causing a crash with an assertion failure when receiving a signal on pwrite(). Bug Fixed: LaunchPad: #764395 / MySQL bug #60788 (A. …