You are here

MySQL Performance Blog

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

Subscribe to MySQL Performance Blog

MySQL: what read_buffer_size value is optimal ?

MySQL Performance Blog - Mon, 17/09/2007 - 2:41pm

The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to less believe in common sense or common sense of documentation writers and do more benchmarks and performance research. I just recently wrote about rather surprising results with sort performance and today I've discovered even read_buffer_size selection may be less than obvious.

Going to MySQL Developers conference in Heidelberg

MySQL Performance Blog - Sat, 15/09/2007 - 4:47pm

I finally got my visa so I’m going to MySQL Developers meeting in Heidelberg, Germany next week.
Hopefully it will be great source of “technical insight” information, either from organized sessions or from developers themselves.

Plus I’m looking forward meeting a lot of friends our where both MySQL Developers and active community members.

Enabling/Disabling query log without server restart in MySQL 5.0

MySQL Performance Blog - Thu, 13/09/2007 - 10:19am

General query logging can be very handy in MySQL on profuction server for various debugging needs. Unfortunately you can’t switch it on and off without restarting server until MySQL 5.0.

What can you do in MySQL 5.0 and below ?

InnoDB in self-compiled MySQL 5.1

MySQL Performance Blog - Thu, 13/09/2007 - 7:47am

If you like to compile MySQL from sources by yourself, for different needs, like debugging, testing etc, you probably can face this issue.

What I usually do to fast compile and test is

  1. ./configure --prefix=/dir/to/mysql
  2. make
  3. make install

and then, for example, load the dump of InnoDB from previous version:

mysql testdatabase < dump.sql

I bet you will not notice all your tables now is MyISAM. Why?

Query Profiling with MySQL: Bypassing caches

MySQL Performance Blog - Wed, 12/09/2007 - 4:22pm

Quite frequently I run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I run it, why is that ?

Read Buffers, mmap, malloc and MySQL Performance

MySQL Performance Blog - Wed, 12/09/2007 - 9:33am

Monty Taylor posted interesting investigation of the fact read_buffer_size variable affects connection speed.

This is not something you would expect right ? me too. Not only global user data is expected to be cached on startup but even if it is not why would you do full table scan to fetch single user information ? Something is fishy here and perhaps bug needs to be filed either resulting in fixes or docummentation updates.

VOIP Advice requested

MySQL Performance Blog - Mon, 10/09/2007 - 3:27pm

As one of my last posts about issues with hosting provider got great response and We got a lot of good advice and offers I decided to ask for advice another problem we have as we’re growing our company - organizing good phone communications.

Our goals are rather simple though the fact we’re globally distributed may put us a bit aside from typical small business needs.

Withdrawal of Memory allocation in Stored Function

MySQL Performance Blog - Fri, 07/09/2007 - 8:31am

Returning to my post about memory consumption with Stored Function at this moment I want to annul that post. Trying to repeat described behavoir I cannot repeat that again and probably there was something wrong with the whole box, not MySQL. I apologize if it was confusing. Although I am almost sure this topic will popup somewhen.

What would make MySQL Multiple Queries Usable ?

MySQL Performance Blog - Wed, 05/09/2007 - 1:53pm

MySQL Has API to run Multiple Queries at once. This feature was designed mainly with saving network round trip in mind and got a little traction due to associated security risks and not significant gains in most cases.

What would make MySQL Multiple Queries API more usable ?

Speaking on HighLoad Conference, Moscow, Russia

MySQL Performance Blog - Mon, 03/09/2007 - 9:05pm

HighLoad.RU is the conference focused on Building High Performance Systems and has speakers from most of top Russian Internet companies.

It is taking place in Moscow, Russia 24-25 of September and you should know a bit of Russian to attend :)

Looking for High Performance MySQL Interns

MySQL Performance Blog - Sun, 02/09/2007 - 4:35pm

We have yet another opening at MySQL Performance Blog.

We’re looking for Intern position (with limited compensation) to work on project of MySQL Performance research and analysis, developing and running benchmarks under our guidance, writing WhitePapers and articles.

We feel this would be perfect part time position for the Computer Science student interested in High Performance systems design, performance optimizations and MySQL. The work done with us has potential of also being used for course work or diploma project.

Site was down today, support and web hosting.

MySQL Performance Blog - Sat, 01/09/2007 - 12:36am

During last one and a half year we had pretty good track record with MySQL Performance Blog - there were times when site was slow (especially when backup was running) but I do not remember significant downtime, until today we went down for few hours.

Working on &ldquo;High Performance MySQL, Second Edition&rdquo;, how you can help?

MySQL Performance Blog - Fri, 31/08/2007 - 1:52pm

As you may already have seen announcement by Baron we’re working on major rewrite of High Performance MySQL book - the most famous book about MySQL Performance on the market… which is getting old though. We’ve been slowly working on the book for over half a year now and were later joined by Arjen Lentz and Baron

Guess what?! Microsecond slow query timing finally available in MySQL!

MySQL Performance Blog - Thu, 30/08/2007 - 6:08am

Vadim asked me yesterday to update Microslow patch for 5.1.21, because the previous one I wrote for 5.1.20 failed to apply correctly on the new MySQL release. Imagine the expression on my face after I unpacked the sources and found out that MySQL incorporated the patch to their release. So it’s a built-in feature now in the 5.1 line. So far I noticed one addition to my code which is logging the microtime statistics into TABLE type log storage.



MySQL Performance Blog - Tue, 28/08/2007 - 9:49pm

When we optimize clients' SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one - to get a result set, another - to count total number of rows. In this post I'll try to check, is this true or not and when it is better to run two separate queries.

For my tests I've created following simple table:

Do you always need index on WHERE column ?

MySQL Performance Blog - Tue, 28/08/2007 - 5:53pm

I believe we wrote about this before, but this topic popups again and again.
Today I've read opinion that if we have clause WHERE has_something=1 we should have index on column `has_something` (the column has two values 0 and 1).

In reality the right answer is not so simple.

Let's look next table

Redundant index is not always bad

MySQL Performance Blog - Tue, 28/08/2007 - 9:54am

About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I'm speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as for queries like WHERE A=5 the index (A,B) also can be used.

But there is case when for performance it would be good to have both

Let we have the table

Memory allocation in Stored Function

MySQL Performance Blog - Sun, 26/08/2007 - 5:09pm

Not so long time ago I had task to update string column in table with 10mil+ rows, and, as the manipulation was non-trivial, I decided this task is good to try Stored Function. Function written - go ahead. Since 5 min I got totally frozen box with no free memory and giant swap.

The case was worth to look deeply - let's try simple table

How fast can you sort data with MySQL ?

MySQL Performance Blog - Sat, 18/08/2007 - 5:48pm

I took the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice.

I tested full table scan of the table completes in 0.22 seconds giving us about 4.5 Million of rows/sec. Obviously we can't get sorted result set faster than that.

Should MySQL Extend GROUP BY Syntax ?

MySQL Performance Blog - Fri, 17/08/2007 - 8:55pm

Jan has a good article about finding the row matching some value in the group:

How much overhead is caused by on disk temporary tables

MySQL Performance Blog - Thu, 16/08/2007 - 10:19pm

As you might know while running GROUP BY and some other kinds of queries MySQL needs to create temporary tables, which can be created in memory, using MEMORY storage engine or can be created on disk as MYISAM tables. Which one will be used depends on the allowed tmp_table_size and also by the data which needs to be put to temporary tables - BLOB/TEXT columns are not supported with MEMORY storage engine so must use on disk MyISAM temporary table.

Yahoo Search Suggestions for MySQL

MySQL Performance Blog - Thu, 16/08/2007 - 7:17pm

I noticed today if you go to and type MySQL to Search field it gives you suggestions for MySQL, which are:

* loading javascript arrays with mysql data
* mysql performance blog
* mysql download
* mysql administrator download
* mysql front download

It may not be best search suggestions but it is cool to see our blog name to make it in the top of the list.
Too bad few Geeks use Yahoo for search so most of our search engine traffic comes from Google.

MySQL Community Needs

MySQL Performance Blog - Wed, 15/08/2007 - 9:40pm

Sheeri just posted a great post putting a different view on recent MySQL Community Announcements.

This however raises very interesting point what MySQL Community really needs ?

I think the problem is there are no single set of needs for Community which can be maintained within single version. I see at least two set of community with very different needs.

MySQL VIEW as performance troublemaker

MySQL Performance Blog - Sun, 12/08/2007 - 6:17pm

I start to see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the queries - to keep queries simple without really thinking how it affects server performance.

Even worse than that - looking at the short table which just gets single row from the table by the key we think this is simple query, while can be real monster instead with complexity hidden away in VIEW definition.

FaceBook Search, Search for social networks

MySQL Performance Blog - Sun, 12/08/2007 - 2:06pm

Yesterday I ran into the article which sheds some light on FaceBook search implementation. As we're recently a lot into search having implemented a bunch of search projects ourselves and helped number a of customers with their full text search needs I decided to post my thoughts on this matter.


Subscribe to MySQL Performance Blog