You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 3 years 9 months ago
Updated: 3 years 9 months ago
Update every: 2 hours

Percona's Data performance and scalability blog
Subscribe to MySQL Performance Blog

Speeding up GROUP BY if you want aproximate results

MySQL Performance Blog - Fri, 07/03/2008 - 7:33am

Doing performance analyzes today I wanted to count how many hits come to the pages which get more than couple of visits per day. We had SQL logs in the database so It was pretty simple query:

PLAIN TEXT SQL:
  1. SELECT sum(cnt) FROM (SELECT count(*) cnt FROM performance_log_080306 GROUP BY page HAVING cnt>2) pv;

Unfortunately this query ran for over half an hour badly overloaded server and I had to kill it in the end.

Sphinx 0.9.8 reaches RC stage, Docs updated

MySQL Performance Blog - Fri, 07/03/2008 - 1:57am

Andrew Aksenoff is pretty slow with release numbers for Sphinx. By MySQL Users Conference 2007 Sphinx version 0.9.7 was released and today we had just 0.9.8-rc1 announced This minor change in version number corresponds to about double source size (looking at download size) and major rewrite for many portions. Sphinx 0.9.8 snapshots were more stable than 0.9.7 for a long time but I guess Andrew did not call them releases because documentation was incomplete and this just was fixed now.

Evaluating IO subsystem performance for MySQL Needs

MySQL Performance Blog - Wed, 05/03/2008 - 7:55am

I'm often asked how one can evaluate IO subsystem (Hard drive RAID or SAN) performance for MySQL needs so I've decided to write some simple steps you can take to get a good feeling about it, it is not perfect but usually can tell you quite a lot of what you should expect from the system.

What I usually look for MySQL is performance in random reads and random writes. Sequential reads and writes are rarely the problem for OLTP workloads, so we will not look at them.

RAID System performance surprises

MySQL Performance Blog - Wed, 05/03/2008 - 7:21am

Implementing MySQL database in 24/7 environments we typically hope for uniform component performance, or at least would like to be able to control it. Typically this is indeed the case, for example CPU will perform with same performance day and night (unless system management software decides to lower CPU frequency due to overheating).

A piece of Sun/MySQL Marketing

MySQL Performance Blog - Thu, 28/02/2008 - 2:37am

I got first Sun/MySQL Newsletter Today which among other things lead to the site publishing among other things links to various stuff related to Sun and MySQL and among other things - These Benchmarks

This may be great piece of Benchmarks for Sales and Marketing needs as they show sun stuff is so much cooler but they are so unusable if you really want to take informed decision and pick best of the component level.

MySQL Performance Blog now uses Sphinx for Site Search

MySQL Performance Blog - Fri, 15/02/2008 - 9:33pm

I never liked how build in Wordpress search works. it shows full documents rather than snippets it does not search comments and it does not have any query language so I always used Google search if I wanted to find something on MySQL Performance Blog.

Back to USA

MySQL Performance Blog - Thu, 14/02/2008 - 3:04pm

As some of you surely know already I’m moving back to USA, so does Vadim. From very beginning we had much more business from US than from rest of the world combined so this is a great step in company development having more comfortable time zones for our customers (without having to work nights) as well as more readily available onsite consulting. This also will give better time distribution among time zones which makes it easier for us to provide 24/7 support for MySQL and other technologies.

How to check MySQL Config files

MySQL Performance Blog - Tue, 12/02/2008 - 7:16pm

Probably most of MySQL users sometime had a situation when they would do changes in MySQL config file without restarting server (may be matching SET GLOBAL command to do it in run time) but either because option is misspelled or because given version does not support such option the server when would refuse to start when it restarted, either on operating system restart or recovering from MySQL Server crash. In any case it is quite nasty.

MySQL Full Text Search in Action

MySQL Performance Blog - Thu, 07/02/2008 - 11:47am

Preparing to move I’m selling stuff on GumTree which is UK based clone of Craigslist offering similar functionality but with Ads :)

MySQL 6.0 Subquery optimizations are published.

MySQL Performance Blog - Wed, 06/02/2008 - 1:03pm

Sergey Petrunia brought to my attention the work Optimizer Team has done in MySQL 6.0 related to SubQuery Optimization.

I am excited to see this information published honestly outlining the things which are fixed now and things which are still not handled well. According to the bug sampling done by Optimizer Team 68% of reported issues are fixed (significantly improved) by changes in MySQL 6.0 which is far from all the issues but still very good number.

Finding out largest tables on MySQL Server

MySQL Performance Blog - Mon, 04/02/2008 - 2:46pm

Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a way it presents information:

Speaking on MySQL Users Conference 2008

MySQL Performance Blog - Sun, 03/02/2008 - 10:06pm

Guess what ? I also will be speaking at MySQL Users Conference 2008, which is always excited.
According to session schedule I’ll have one talk about Innodb Scalability Limits and another one together with Andrew Aksenoff about Sphinx.

Performance gotcha of MySQL memory tables

MySQL Performance Blog - Fri, 01/02/2008 - 10:53pm

One performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range lookups. This is however not performance gotcha I'm going to write about. There is one more thing you should be aware which again comes from the fact MEMORY tables use HASH indexes by default.

I've created rather similar test table:

How MySQL Query Cache works with Transactions

MySQL Performance Blog - Tue, 29/01/2008 - 9:30pm

As MySQL Manual Says Query Cache works with transactions with Innodb tables but it does not tell you how and with which restrictions.

According to my tests it works but it is very restricted and one could expect it to work much better:

Monty unviels Maria and starts Blogging

MySQL Performance Blog - Tue, 29/01/2008 - 9:44am

This weekend we’re hearing great news from Michael “Monty” Widenius - one of the Fathers of MySQL. Monty finally found a time to create his own blog with very descriptive name Monty Says. At the same time Monty finally announces Maria - the MyISAM successor storage engine he has been working for last few years. You can now get Maria from MySQL BitKeeper Server.

No more MySQL Crash Safe Replication in 5.0 ?

MySQL Performance Blog - Tue, 29/01/2008 - 8:26am

As you might know even if you're only using Innodb tables your replication is not completely crash safe - if Slave MySQL Server crashes/power goes down it is likely for relay logs to run out of sync (they are not synced to the disk) plus position on the master which slave remembers becomes stale.

During MySQL 4.0 and 4.1 series there was a great workaround if you're using only Innodb tables - Innodb when Innodb does crash recovery it would print position in master log files up to which replication was done:

Enum Fields VS Varchar VS Int + Joined table: What is Faster?

MySQL Performance Blog - Thu, 24/01/2008 - 6:29pm

Really often in customers' application we can see a huge tables with varchar/char fields, with small sets of possible values. These are "state", "gender", "status", "weapon_type", etc, etc. Frequently we suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint)? In this post I'd like to present a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns.

What should we say about Sun buying MySQL ?

MySQL Performance Blog - Mon, 21/01/2008 - 1:26pm

Kaj wrote me email yesterday asking if I was sleeping for a whole week or may be dead because I’m probably the only one of people blogging about MySQL who has not commented about announced Sun - MySQL Deal.

In fact I was just on extremely busy travel schedule last week, so I’m just finding a bit of time now to comment on it.

MySQL Blob Compression performance benefits

MySQL Performance Blog - Fri, 11/01/2008 - 5:43pm

When you’re storing text of significant size in the table it often makes sense to keep it compressed. Unfortunately MySQL Does not provide compressed BLOB/TEXT columns (I would really love to have COMPRESSED attribute for the BLOB/TEXT columns which would make them transparently compressed) but you well can do it yourself by using COMPRESS/UNCOMPRESS functions or compressing/decompressing things on the client.

PHP vs. BIGINT vs. float conversion caveat

MySQL Performance Blog - Thu, 10/01/2008 - 11:59pm

Sometimes you need to work with big numbers in PHP (gulp). For example, sometimes 32-bit identifiers are not enough and you have to use BIGINT 64-bit ids; e.g. if you are encoding additional information like the server ID into high bits of the ID.

CentOS Comes with free build of MySQL Enterprise ?

MySQL Performance Blog - Fri, 04/01/2008 - 9:19am

As I already wrote you can well go to Linux distribution vendors if you’re looking for recent MySQL version in a hassle free binary form. (On the time of this writing you could only get binaries for MySQL Community 5.0.45 from MySQL Download Pages which was released in July and so about half a year old.

What is the longest part of Innodb Recovery Process ?

MySQL Performance Blog - Thu, 20/12/2007 - 10:25am

In MySQL 4.1 and above the longest part of recovery after crash for Innodb tables could be UNDO stage - it was happening in foreground and was basically unbound - if you have large enough transaction which needed to be undone this could take long hours.
REDO stage on other hand always could be regulated by size of your Innodb log files so you could have it as large as you like. Read more about it here.

Large result sets vs. compression protocol

MySQL Performance Blog - Thu, 20/12/2007 - 9:08am

mysql_connect() function in PHP’s MySQL interface (which for reference maps to mysql_real_connect() function in MySQL C API) has a $client_flags parameter since PHP 4.3.0. This parameter is barely known and almost always overlooked but in some cases it could provide a nice boost to your application.

MVCC: Transaction IDs, Log Sequence numbers and Snapshots

MySQL Performance Blog - Wed, 19/12/2007 - 10:35am

MySQL Storage Engines implementing Multi Version Concurrency Control have several internal identifiers related to MVCC. I see a lot of people being confused what they are and why they are needed so I decided to take a time to explain it a bit. This is general explanation, it does not corresponds to Innodb in particular and some implementation can be different but I hope this will let you to understand MVCC a bit better.

Fixing column encoding mess in MySQL

MySQL Performance Blog - Tue, 18/12/2007 - 2:34pm

Just had an interesting issue with an encoding mess on a column containing non-ASCII (Russian) text. The solution was not immediately obvious so I decided it's worth sharing.

The column (actually the whole table) was created with DEFAULT CHARSET cp1251. Most of the data was in proper cp1251 national encoding indeed. However, because of web application failure to properly set the encoding, some of the rows were actually in UTF-8. That needed to be fixed.

Pages

Subscribe to MySQL Performance Blog