Personal blog of Yzmir Ramirez

So, this week, Drizzle released its beta, which is really exciting. But at the same time, I decided to ask the Ubuntu MOTU pull it out of Ubuntu 10.10 (a.k.a. maverick) entirely. The reasons, may not be entirely obvious.

• Licensing: There is some ambiguity on the licensing of certain non-critical source code in Drizzle that we weren’t certain Debian archive admins would accept. Since we like to follow Debian as closely as possible in Ubuntu, the MOTU sponsor we had was requesting that we upload into Debian first. Upon review of earlier packages, the debian archive maintainers pointed out some ambiguities in the copyright documentation, and it turns out, there are some ambiguities still in the source code. These things take time to sort out, though I’m confident we’ve figured most, if not all, of them out.
• Beta status: Drizzle released their first beta just yesterday. This is great, and would be a good release to have in Maverick, but its going to change *a lot* before the “elliot” milestone is released in early 2011. Monty Taylor assures me that they’re going to be ready to release before feature freeze of Natty (11.04). Until then, they’re going to be fixing bugs in the betas and releasing those fixes. In the face of that, its probably better to point people at a PPA that will have the latest bug fix release in it, and tools included to help debug/fix the release as well.
• Quality: Even though its clear that drizzle is beta to those following drizzle closely, it may not be entirely clear to everyone. Beta versions make it into Ubuntu all the time, but being a database engine, I’m hesitant to have the casual user try it out. In 6 months, Drizzle will be at a stable release stage, and all users should be feeling pretty good about running on it. That seems like the right time to put it into Debian and Ubuntu.

So, what should you do if you want to run Drizzle on Maverick?

There are two package archives maintained by the drizzle developers just for ubuntu.

The PPA for drizzle development – This should have the latest stable release, and all of the build-depends to rebuild it.
The Drizzle Trunk PPA – This should have the latest daily build of drizzle from the source code repository, which may have fixes made since the last stable release.

Those links include instructions for adding the PPA’s to your system, after that, just


apt-get install drizzle-server drizzle-client


And have fun!

Also, we’ll be discussing drizzle sometime at UDS-N in Orlando. So make sure to check the schedule out and join us (remotely or on site) if you want to chime in or hear what we’re going to do with the Narwhal and Drizzle.

We now have hardware in our test lab that represents the next generation of commodity servers for databases. It’s a Cisco UCS C250 server, powered by two Intel Westmere CPUs (X5670 @ 2.93GHz). Each CPU has 6 cores and 12 threads. The most amazing part is the amount of memory. It has 384GB of RAM, which is actually more space than the disks contain.  The disks are 270GB in total, with the underlying configuration RAID10 over eight 2.5″ 15K RPM disks. To make the system even more powerful, I put a FusionIO 320GB SLC card in the PCI-E slot. Here is a link to the box specs.

The server was generously provided by Cisco Systems, Inc.

So, obviously I’m anxious to see how Percona Server with XtraDB scales on this hardware, and you can expect a series of benchmarks. An especially interesting topic is what we can get from “threads”, as there are only 12 “real” cores, with each core having two “threads”.

So, I took Percona Server 5.1.47-11.2  and ran the sysbench oltp read-only and read-write benchmarks using from 1 to 32 threads. The database size was 100 million rows ( about 23GB of data). (Starting with Percona Server 5.1.49-12.0, we are going to provide regular builds dedicated to the Cisco UCS platform)

The full results are available on Wiki, and the graphical representation follows:

You can see from the graph that it scales pretty well even up to 24 threads, despite the fact that half of them are not real CPU cores. For up to 10 threads, the scale factor is quite impressive: it is 8.2 for read-only and 9.2 for read-write.  (I calculate the scale factor as the result with 10 threads divided by the result with 1 thread.) Above 10 threads, the rate of increase is not as large, and for 24 threads we have a scale factor of 12.6 for read-only and 13.3 for read-write.

Of course, it will be interesting to compare these results with the latest MySQL 5.5 releases, and especially in cases with the number of threads is > 100. I’m going to do these comparisons in my next round of testing.

(Edited by Fred Linhoss)

Entry posted by Vadim |
14 comments

Add to: | | | |

Drizzle7 Beta Released! now with MySQL migration! « LinuxJedis /dev/null.

Drizzle is a project that is near and dear to my heart.

To sum it up, Drizzle took all that was really good in MySQL, cut out all that was mediocre, and replaced some of it with really good stuff. The end product is, I think, something that is leaner, should be more stable, and definitely more flexible.

So go check out the beta! I guess I should use Andrew’s migration tool and see if I can migrate this blog to drizzle.

During my visit to Moscow, Russia next month I’m going to give a full day training/tutorial presentation on Diagnosing and Fixing MySQL Performance Problems . This even will be based on updated and expanded tutorial from MySQL Conference & Expo which was so popular it was sold out earlier this year. This also will be the only full day training event open to the public during this trip. There are few conference talks, user meetings and private training engagements which I’m doing on this trip as well. I’ll blog about these later.

Entry posted by peter |
No comment

Add to: | | | |

Dear Community,

Percona Server version 5.1.50-rel11.4 is now available for download.

The changes in this release include:

New features

The primary purpose of this release is to update to the latest version of MySQL 5.1.

• Percona Server 5.1.50-rel11.4 is now based on MySQL 5.1.50.

Fixed bugs

• innodb_use_sys_stats_table – The SYS_STATS table is now used only for non-temporary tables.

The binary packages are available on our website.

Percona Server can be installed from either our RPM or Debian repository.
It is worth mentioning that Percona Server doesn’t obsolete MySQL packages, so, if you’re migrating from MySQL to Percona Server, you have to remove your MySQL packages and then install Percona Server. To keep applications which depend on MySQL during migration, you need to install the Percona-Server-shared-compat package.

The latest source code for Percona Server, including the development branch, can be found on LaunchPAD.

Please report any bugs found at Bugs in Percona Server.
For general questions, use our Pecona-discussions group, and for development questions our Percona-dev group.

For support, commercial, and sponsorship inquiries, contact Percona.

Entry posted by Aleksandr Kuzminsky |
One comment

Add to: | | | |

I wrote couple of weeks ago on dangers of bad cache design. Today I’ve been troubleshooting the production down case which had fair amount of issues related to how cache was used.

The deal was as following. The update to the codebase was performed and it caused performance issues, so it was rolled back but yet the problem remained. This is a very common case when you would see customer telling you everything is the same as it was yesterday… but it does not work today.

When I hear these words I like to tell people computers are state machines and they work in predictable way. If it does not work same today as it worked yesterday something was changed… it is just you may not recognize WHAT was changed. It may be something subtle as change in query plan or increase in search engine bot activity. It may be RAID writeback cache disabled due to battery learning but there must be something. This is actually where Trending often comes handy – graphs would often expose which metrics became different, they just need to be detailed enough.

So back to this case… MySQL was getting overloaded with thousands of same queries… which corresponded to cache miss storm but why it was not problem before ? The answer lies in caching as well. When software is deployed the memcache is cleared to avoid potential issues with different cache content, so system have to start with cold cache which overloads the system and it never recovers. When you have expiration based cache you increase the chance of conditions when system will not gradually recover by populating cache – if because of cache misses performance is so bad the speed of populating cache with new items is lower than speed with which items expire due to timeout you may never get a system warmed up.

But wait again… was this the first change ? Was not the code ever updated before ? Of course it was. As often with serious failures there is more than one reason which pushes system over top. During normal deployment the code change is done at night when when the traffic is low, so even if system has higher load and worse response time for several minutes after code is updated, the traffic is not high enough to push it to conditions it is unable to recover. This time code update was not successful and by the time rollback was completed the traffic was already high enough to cause the problems.

So the immediate solution to bring the system up was surprisingly simple. We just had to get traffic on the system in stages allowing Memcache to be warmed up. There were no code which would allow to do it on application side so we did it on MySQL side instead. “SET GLOBAL max_connections=20” to limit number of connections to MySQL and so let application to err when it tries to put too much load on MySQL as MySQL load stabilizes increasing number of connections higher until you finally can serve all traffic without problems.

So what we can learn from this, besides cache design related issues I mentioned in the previous post.

Include Rollback in Maintainance Window Ensure you plan the maintainance window long enough so you can do rollback inside this window and do not hesitate to do this rollback
if you’re running out of time. Know how long rollback takes and have it well prepared. Way to often I see people trying to make things work until time allocated for the operation is up and when
rollback have to be done outside of the time window allowed.

Know your Cold Cache Performance and Behavior Know how your application behaves with cold cache. Does it recovers or does it just dies with the high traffic ? How high is the response time penalty and how long it takes to reach normal performance ?

Have a way to increase traffic gradually There are many reasons beyond caching when you may want to slowly ramp up the traffic on the system. Make sure you have some means to do that. I’d recommend doing it on user session so some users are in and can use the system completely while others have to wait for their turn to get in. It is a lot better than having it done on page basics when you randomly have some pages giving error messages. In some cases you can also do ramp up feature by feature.

Consider Pre-Priming Caches In some cases when cold performance gives too bad response time you may want to prime the caches by running/replaying some production workload on the system before it is put online. In this case all ramp up and suffering from bad response time can be done by script… which does not care.

Entry posted by peter |
5 comments

Add to: | | | |

I occasionally get in to light arguments healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables.  My standard response has always been: “it depends on how much of a tradeoff you are willing to make for performance. In some situations the cost can be considerable”.

.. that’s when they expect me to “come up with some real proof” to show them. I do not disagree with their logic or proof being on their list-of-demands.  I support the use of data to make decisions.

The problem is that MySQL has (traditionally) been lacking the instrumentation required to make these decisions easy.  This is getting better  – here is an example we recently added to our InnoDB course:

 CREATE TABLE parent (
 id INT NOT NULL AUTO_INCREMENT,
 bogus_column char(32),
 PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE child (
 id INT NOT NULL AUTO_INCREMENT,
 parent_id INT NOT NULL,
 bogus_column char(32),
 PRIMARY KEY (id),
 KEY (parent_id),
 CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id)
) ENGINE=InnoDB;

INSERT INTO parent (bogus_column)
VALUES ('aaa'), ('bbb'), ('ccc'), ('ddd'), ('eee');

INSERT INTO child (parent_id,bogus_column) VALUES
(1, 'aaa'), (2,'bbb'), (3, 'ccc'),
(4, 'ddd'), (5, 'eee');

START TRANSACTION; # session1
START TRANSACTION; # session2

# session1
UPDATE child SET parent_id = 5? WHERE parent_id = 4;

#session2
UPDATE parent SET bogus_column = 'new!' WHERE id = 4;

#session2
UPDATE parent SET bogus_column = 'new!' WHERE id = 5;

In the last statement, session2 will block waiting on a lock. Want to know where that lock is? Check information_schema.innodb_locks:

mysql> SELECT * FROM information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 87035:1300:3:6
lock_trx_id: 87035
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`parent`
 lock_index: `PRIMARY`
 lock_space: 1300
  lock_page: 3
   lock_rec: 6
  lock_data: 5
*************************** 2. row ***************************
    lock_id: 87034:1300:3:6
lock_trx_id: 87034
  lock_mode: S
  lock_type: RECORD
 lock_table: `test`.`parent`
 lock_index: `PRIMARY`
 lock_space: 1300
  lock_page: 3
   lock_rec: 6
  lock_data: 5
2 rows in set (0.00 sec)

The same example without the foreign key constraints does not block on the last statement.  We also see the expected output change to:

mysql> SELECT * FROM information_schema.innodb_locks;
Empty set (0.00 sec)

This information_schema table is new to InnoDB plugin.  In earlier releases of MySQL you may be able to get the data, but it is not in such an easily digestible form.  Instrumentation is the most under talked about feature in all new releases of MySQL and Percona Server.  See BUG #53336 for more examples of pure awesomeness.

Entry posted by Morgan Tocker |
6 comments

Add to: | | | |

Dear Community,

Starting with this release, we introduce a new release model for Percona Server. From now on, we will have both Stable and Release Candidate releases. Release Candidates will introduce new features not yet available in Stable releases.

Along with new features, our new 5.1.49-12.0 RC contains a couple of patches from the Facebook-MySQL tree – https://launchpad.net/mysqlatfacebook . Of particular note is better integration with FlashCache.

We also introduce the very interesting new feature called ”Permanent InnoDB Buffer Pool”  (sponsored by a Well Known Social Network site). It allows you to keep the InnoDB buffer pool in memory between restarts of MySQL servers.
This can result in a significant decrease in warmup time after MySQL restarts.

Starting with this release, we will also provide special builds optimized for Cisco UCS B250/C250 servers, which come with a lot of memory (up to 384GB). Mentioned above, “Permanent InnoDB Buffer Pool” is especially interesting for servers with 128GB+ memory sizes, as it currently can take hours to warm up an empty InnoDB buffer pool on these boxes.

Percona Server version 5.1.49-rel12.0 RC is available for download now.

The changes in this release include:

New features

Fixed bugs

The binary packages are available on our website.
The Release Notes for this and previous releases can be found in our Wiki.

The latest source code for Percona Server, including the development branch, can be found on LaunchPAD.

Please report any bugs found at Bugs in Percona Server.
For general questions, use our Pecona-discussions group, and for development questions our Percona-dev group.

For support, commercial, and sponsorship inquiries, contact Percona.

Entry posted by Aleksandr Kuzminsky |
6 comments

Add to: | | | |

I worked on the problem recently which showed itself as rather low MySQL load (probably 5% CPU usage and close to zero IO) would spike to have hundreds instances of threads running at the same time, causing intense utilization spike and server very unresponsive for anywhere from half a minute to ten minutes until everything would go back to normal. What was interesting is Same query was taking large portion of slots in PROCESSLIST. I do not just mean query with same fingerprint but literally the same query with same constants.

What we observed was a cache miss storm – situation which can happen with memcache (as in this case) as well as with query cache. If you have the item which is expensive to generate but which has a lot of hits in the cache you can get into situation when many clients at once will have miss in the cache and will attempt to re-create the item pushing server to overload. Now because a lot of requests being proceed in parallel the response time for initial request may take a lot longer than if it is ran all by itself increasing the time it takes server to recover.

What do I mean by expensive query in this case ? This is the query which has too high ratio of requests to be served with 100% misses for portion of time. For example if I have 100 accesses for given cache objects per second and it takes 500ms to populate it, it still will be too expensive, because for these 500 ms it takes to populate the item 50 requests will be started (this is the average case, because of random arrivals the worse case is worse) which takes 25 seconds to deal with (assuming there is just one execution unit). Because we normally have multiple cores and multiple drives it can be less than that but it is enough to cause hiccup for a few seconds which is unacceptable for a lot of modern applications.

How can you deal with this problem ? You should carefully watch frequently accessed cache items as well as cache items which take long to generate in case of cache miss. To find first one for memcached you can use mk-query-digest to analyze which items are requested frequently, it can decode memcached wire traffic. For second you can have instrumentation in your applications or take a look at MySQL Slow queries – which is good enough if you populate each cache item with single query.

Optimize query if you can. This is a good thing to do in any case but it may not be the only part of best solution. You can get some query patterns getting slow over time as data size growths or execution plan changes, you can also have some items becoming hot unexpectedly due to changes to content interest or launch of new features.

Use Smarter Cache Especially with memcache it is you who decide how to populate the cache. There is number of techniques you can use to avoid this problem such as probabilistic invalidation, you can also put the special value in the cache to reflect it is being updated right now so you’re better wait rather than starting populating it. For MySQL Query Cache the solution should have been to make queries wait on first query started to complete. Unfortunately this have not been implemented so far.

Pre-Populate the cache In some cases you can’t change how caching works easily, especially if it is built in in the application however it may be easier enough to identify hot items
and pre-populate them before they expire. So if item expires in 15 minutes you can refresh it every 10 minutes and so you get basically no misses. This works best when there are few hot cache entries which cause the problem.

So if your server has seemingly random spikes of activities check this out – cache miss storm could be one of the possible causes.

Entry posted by peter |
25 comments

Add to: | | | |

Dear Community,

Percona Server version 5.1.49-rel11.3 is now available for download.

The changes in this release include:

New features

• Percona Server 5.1.49-rel11.3 is based on MySQL 5.1.49.
• A new variable was introduced: innodb_use_sys_stats_table. If ON, the table’s statistics are stored statically in the internal table SYS_STATS. The table is populated with the ANALYZE TABLE command.
• A new session variable was introduced: innodb_flush_log_at_trx_commit_session.

Fixed bugs

• Bug #576041 – Fixes long stalls while accessing the innodb_buffer_pool_pages_index table on systems with a large number of tables
• Bug #592007 – More strictly enforces the maximum purge delay defined by innodb_max_purge_lag by removing the requirement that purge operations be delayed if an old consistent read view exists that could see the rows to be purged. The problem and solution are explained in DimitriK blog
• Bug #607449 – Fixes a crash during shutdown when userstat_running=1
• Bug #612954 – Fixes a problem with SHOW PROCESSLIST displaying an incorrect time
• Bug #610525 – Reduces the number of compile time errors when the server is rebuilt
• Bug #569275 – Fixes a crash when XtraDB shuts down in “crash resistent mode”

The binary packages are available on our website.

The latest source code for Percona Server, including the development branch, can be found on LaunchPAD.

Please report any bugs found at Bugs in Percona Server.
For general questions, use our Pecona-discussions group, and for development questions our Percona-dev group.

For support, commercial, and sponsorship inquiries, contact Percona.

Entry posted by Aleksandr Kuzminsky |
One comment

Add to: | | | |