You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 2 hours 34 min ago
Updated: 2 hours 34 min ago
Update every: 2 hours

Subscribe to MySQL Performance Blog

The tool I’ve been waiting for years

MySQL Performance Blog - Wed, 19/03/2008 - 4:54am

I've just been pointed to the nice tool which I was waiting for years to see. It is fincore - little perl script which allows you to see what pages of file are cached in OS memory. This is really cool.

When it comes to MySQL it is very useful with MyISAM tables which has their data file cached by OS cache only so you do not have any good information from MySQL side on what data is cached. You can also use it with Innodb to see how much memory are you wasting with double buffering by not using of O_DIRECT.

Working with many files and file system fragmentation

MySQL Performance Blog - Tue, 18/03/2008 - 8:10pm

Working on performance optimization project (not directly MySQL related) we did a test - creating 100 files writing 4K in the random file for some time and when checking the read speed on the files we end up with, compared to just writing the file sequentially and reading it back.

The performance difference was huge - we could read single file at 80MB/sec while fragmented files only deliver about 2MB/sec - this is a massive difference.

Researching your MySQL table sizes

MySQL Performance Blog - Tue, 18/03/2008 - 2:28am

I posted a simple INFORMATION_SCHEMA query to find largest tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:

Find total number of tables, rows, total data in index size for given MySQL Instance

MySQL Error Message Nonsenses

MySQL Performance Blog - Mon, 17/03/2008 - 12:54am

What MySQL honestly was never good at is giving good helpful error messages. Start with basics for example - The error message in case of syntax error gives you information about tokens near by but little details:

  1. mysql> SELECT * FROM  user oder BY pwd;
  2. ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near 'by pwd' at line 1

Sharding and Time Base Partitioning

MySQL Performance Blog - Sat, 15/03/2008 - 5:18am

For large number of online applications once you implemented proper sharding you can consider your scaling problems solved - by getting more and more hardware you can grow. As I recently wrote it however does not mean it is the most optimal way by itself to do things.

Economics of Performance Optimization

MySQL Performance Blog - Thu, 13/03/2008 - 9:21pm

I think every person responsible for Development or Operations of growing application sooner or later have to decide on couple few questions on how to tackle application performance. These questions are:

  • Should we Optimize Application or get more Hardware ?
  • Should we do things ourselves or hire an experts to help us ?

The answer on these questions actually depend on a lot of things, some of which we’ll try to cover here.

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:

  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.


Subscribe to MySQL Performance Blog