You are here

MySQL Performance Blog

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

Subscribe to MySQL Performance Blog

Finding what Created_tmp_disk_tables with log_slow_filter

MySQL Performance Blog - Mon, 22/09/2008 - 3:16pm

Whilst working with a client recently I noticed a large number of temporary tables being created on disk.

show global status like 'Created_tmp%'

| Created_tmp_disk_tables           | 91970        |
| Created_tmp_files                 | 19624        |
| Created_tmp_tables                | 1617031      |

Looking at a 60 second sample showed there was a steady flow of queries creating disk based tables.

mysqladmin ext -ri60

Speaking on HighLoad++, Moscow, Russia

MySQL Performance Blog - Mon, 22/09/2008 - 5:26am

I’ll be speaking at HighLoad++ conference in Moscow,Russia taking place 6,7 Oct 2008. This conference was quite success with very interesting presentation last year and I’m hoping it would be even better this year.

I also will have a full dayMaster Class focused on Scaling MySQL w Sharding and Replication based on material in our book

A common problem when optimizing COUNT()

MySQL Performance Blog - Sat, 20/09/2008 - 4:38pm

When optimizing queries for customers, the first thing I do with a slow query is figure out what it's trying to do. You can't fully optimize a query unless you know how to consider alternative ways to write it, and you can't do that unless you know what the query "means." I frequently run into a situation where I'm forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.

The problem is when the COUNT() contains a column name, like this:

When is it a time to upgrade memory ?

MySQL Performance Blog - Wed, 17/09/2008 - 4:29am

Quite commonly I get a question similar to this - “My Innodb Buffer Pool is already 90% full, should I be thinking about upgrading memory already?”
This is a wrong way to put the question. Unless you have very small database (read as database which is less than innodb_buffer_pool_size) You will have all buffer pool busy sooner or later.
How to figure out if it is time for upgrade when ?

Unused indexes by single query

MySQL Performance Blog - Sat, 13/09/2008 - 1:31am

Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.

But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query

Google’s user_statistics V2 port and changes

MySQL Performance Blog - Fri, 12/09/2008 - 9:18pm

Recently Google published V2 release of patches, one of them user_statistics we use in our releases.
New features are quite interesting so we decided to port it to fresh releases of MySQL. Features includes:

      New statistics per user (Cpu_time, Bytes_received, Bytes_sent, etc)
      New command SHOW CLIENT_STATISTICS, which shows statistics per client's hostname, not per user

Our port includes:

What Bugs makes you to recomend upgrade most frequently ?

MySQL Performance Blog - Fri, 12/09/2008 - 8:09pm

What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

It is not the most critical bug by far but this makes it worse - critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like “sometimes my slave clone script does not work” may hang on for years.


MySQL Performance Blog - Fri, 12/09/2008 - 12:15am

How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table... which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true:

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.


Subscribe to MySQL Performance Blog