You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 1 hour 5 min ago
Updated: 1 hour 5 min ago
Update every: 2 hours

Subscribe to MySQL Performance Blog

The performance effects of new patches

MySQL Performance Blog - Wed, 10/09/2008 - 1:21am

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.

Development plans

MySQL Performance Blog - Mon, 08/09/2008 - 11:22pm

We gathered together our ideas of MySQL improvements on this page
and we are going to implement some of them.
My favorite one is - make InnoDB files .ibd (one created with –innodb-file-per-table=1) movable from one server to another, however it is sort of challenging.

New patches, new builds

MySQL Performance Blog - Fri, 05/09/2008 - 10:32pm

We made new patches, improved previous and want to announce new builds for 5.0.62, 5.0.67 and 5.1.26 versions. One of biggest changes we separated releases of 5.0 into two branches.

How quickly you should expect to see bugs fixed

MySQL Performance Blog - Thu, 04/09/2008 - 11:24pm

Over a year ago I wrote about pretty nasty Innodb Recovery Bug. I ran in the same situation again (different system, different customer) and went to see the status of the bug… and it is still open.


MySQL Performance Blog - Wed, 03/09/2008 - 9:09pm

Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb.

Beware of running ANALYZE in Production

MySQL Performance Blog - Tue, 02/09/2008 - 10:26pm

As you might know ANALYZE TABLE just quickly updates table statistics using index dives, unlike with MyISAM when it scans indexes holding table lock for long period of time.

So ANALYZE TABLE should be very fast and non intrusive operation doing just little update on the data. Right ?

Wrong! There is the bug or rather MySQL Design Feature which causes ANALYZE TABLE to block all accesses to this table while it could be flushed from the table cache.

How network can impact MySQL Operations ?

MySQL Performance Blog - Mon, 01/09/2008 - 7:11am

This week I’ve worked with the customer doing certain work during maintenance window which involved a lot of data copying around between MySQL boxes. We had prepared well and had measured how fast we could copy the data between servers of these kind connected to the same network, and we did the same thing before. Using simple tar+netcat based copy we can get 80-90MB/sec on 1GigE assuming RAID is powerful enough.

How to track down the source of Aborted_connects

MySQL Performance Blog - Sat, 23/08/2008 - 1:17pm

Yesterday I helped someone who was seeing a lot of "server has gone away" error messages on his website. While investigating this problem, I noticed several things amiss, which appeared to be related but really weren't. The biggest measurable sign was

MySQL End Of Life (EOL) Policy

MySQL Performance Blog - Sat, 23/08/2008 - 2:09am

We’ve discussed today how we should implement MySQL Version advisory in mk-audit tool. One obvious questions was to look at the end of life - it is often bad idea to run MySQL versions past end of life as even security bugs may not be fixed in these (though do not get paranoid, if you’re running MySQL in isolated environment the risk may be low).
So how does EOL schedule looks ?

Multiple column index vs multiple indexes

MySQL Performance Blog - Sat, 23/08/2008 - 1:37am

After my previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can use both of the ways using multiple column index would be faster but I also went ahead to do some benchmarks today.

I'm using couple of simple tables:

How to find wrong indexing with glance view

MySQL Performance Blog - Fri, 22/08/2008 - 2:56am

Quite common beginners mistake is not to understand how indexing works and so index all columns used in the queries…. separately. So you end up with table which has say 20 indexes but all single column ones. This can be spotted with a glance view. If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations.

Rendundant Array of Inexpensive Servers

MySQL Performance Blog - Thu, 21/08/2008 - 8:30pm

So you need to design highly available MySQL powered system… how do you approach that ?

Worse than DDOS

MySQL Performance Blog - Tue, 19/08/2008 - 6:55am

Today I worked on rather interesting customer problem. Site was subject what was considered DDOS and solution was implemented to protect from it. However in addition to banning the intruders IPs it banned IPs of web services which were very actively used by the application which caused even worse problems by consuming all apache slots which were allocated to the problem. Here are couple of interesting lessons one can learn from it.

The ultimate tool for generating optimal my.cnf files for MySQL

MySQL Performance Blog - Mon, 18/08/2008 - 3:15pm

There are quite a few “tuning primers” and “my.cnf generators” and “sample my.cnf files” online. The ultimate tool for generating an optimal my.cnf is not a tool. It’s a human with many years of experience, deep knowledge of MySQL and the full application stack, and familiarity with your application and your data.

Beware of MyISAM Key Cache mutex contention

MySQL Performance Blog - Wed, 13/08/2008 - 4:11am

Today I was working with the client loading data to MyISAM tables at very high rate. Hundreds of millions rows are loaded daily into single MySQL instance with bursts up to 100K of records/sec which need to be inserted (in the table with few indexes). It was good not all records had to go to the same table and so in theory using multiple thread to do inserts in multiple tables would not be bound by table locks and would be able to use multiple cores efficiently to get good insert rate… or so it seemed.

Picking datatype for STATUS fields

MySQL Performance Blog - Sun, 10/08/2008 - 6:30am

Quite commonly in the applications you would need to use some kind of “status” field - status of order - “new”, “confirmed”, “in production”, “shipped” status of job, message etc. People use variety of ways to handle them often without giving enough thought to the choice which can cause problems later.

New MySQL Community release - Great job MySQL !

MySQL Performance Blog - Sat, 09/08/2008 - 3:22am

Wow!. New MySQL Community release - MySQL 5.0.67 is just out which as manual says first community release since 5.0.51b. I just recently complained about community release irregularity and I’m glad to see the new release after all.

I only hope this will not be one time event but MySQL will follow its own promises of regular schedule of source and binary MySQL Community releases.

Predicting Performance improvements from memory increase

MySQL Performance Blog - Fri, 08/08/2008 - 4:24am

One common question I guess is how much should I see performance improved in case I increase memory say from 16GB to 32GB. The benefit indeed can be very application dependent - if you have working set of say 30GB with uniform data access raising memory from 16GB to 32GB can improve performance order of magnitude by converting very IO bound load to CPU bound, it is well possible to see limited gains - if your working set already fits in 16GB you may not see any significant gains upgrading memory to 32GB.

Will Percona Support Drizzle ?

MySQL Performance Blog - Thu, 07/08/2008 - 1:15am

People are asking me if Percona will support Drizzle and what is in general our position regarding this project.

128GB or RAM finally got cheap

MySQL Performance Blog - Tue, 05/08/2008 - 4:15am

I did not usually go to “Elite” servers on Dell web site but looking at customers system today I went to check Dell Poweredge R900. This monster takes up to 4 Quad Core CPUs and has 32 memory slots, which allows to get 128GB of memory with 4GB of memory chips. This means upgrade to default configuration to 128GB of memory will cost you just $9600 (list price). I’ve been able to configure on a web the system with 8*2.5″ hard drives RAID and 2 CPUs (just as we usually configure PowerEdge 2950) with 128GB of RAM for about $16000.

To find the bottleneck, stop guessing and start measuring

MySQL Performance Blog - Mon, 04/08/2008 - 2:01pm

We recently examined a customer’s system to try to speed up an ETL (Extraction, Transformation and Loading) process for a big data set into a sort of datamart or DW.  What we typically do is ask customers to run the process in question, and then examine what’s happening.  In this case, the (very large, powerful) database server was almost completely idle, with virtually no I/O activity or CPU usage.  So we looked at the server where the ETL process was running.  It was running at 25% CPU usage and was writing some files to disk, but not waiting on I/O.

Userstats patches with information schema support

MySQL Performance Blog - Mon, 04/08/2008 - 11:20am

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.

Download 5.0.62 version
Download 5.1.26 version

Recovery beyond data restore

MySQL Performance Blog - Sun, 03/08/2008 - 6:34am

Quite frequently I see customers looking at recovery as on ability to restore data from backup which can be far from being enough to restore the whole system to operating state, especially for complex systems.

Instead of looking just at data restore process you better look at the whole process which is required to bring system to the working state, including data consistency requirements and times. This has to be considered for different data loss scenarios which may happen.


Subscribe to MySQL Performance Blog