You are here

MySQL Performance Blog

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

Subscribe to MySQL Performance Blog

How much overhead DRDB could cause ?

MySQL Performance Blog - Tue, 03/06/2008 - 6:17am

I was working with the customer today investigating MySQL over DRBD performance issues. His basic question was why there is so much overhead with DRBD in my case, while it is said there should be no more than 30% overhead when DRBD is used.

The truth is - because how DRBD works it does not adds static overhead which could be told as 10% or 80% and you really need to understand how DRBD works as well as how IO system is utilized to understand how much overhead you should expect.

Tools to use for MySQL Performance Review

MySQL Performance Blog - Sun, 01/06/2008 - 12:34am

There are some tools we commonly use doing performance review and optimization and we often ask each other where that particular stuff is located on the web or what is exactly name of the command what does that.

Initially I thought creating internal Percona Wiki page, but thought there is no reason this information should not be public instead.

So now you can find our favorite MySQL Performance Review Tools on the site.

Is DNS the Achilles heel in your MySQL installation?

MySQL Performance Blog - Sun, 01/06/2008 - 12:12am

Do you have skip_name_resolve set in your /etc/my.cnf? If not, consider it. DNS works fine, until it doesn't. Don't let it catch you off guard.

Should you name indexes while doing ALTER TABLE ?

MySQL Performance Blog - Thu, 29/05/2008 - 3:54am

MySQL Server does not require you to specify name of the index if you're running ALTER TABLE statement - it is optional. Though what might be good practical reasons to specify the key name or omit ?

Things what you should be looking at is how MySQL names indexes automatically as well as what maintaining the indexes.

Lets first speak about naming. If you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will add numeric prefix to it, for example:

Can MySQL temporary tables be made safe for statement-based replication?

MySQL Performance Blog - Tue, 27/05/2008 - 1:17am

A while ago I wrote about how to make MySQL replication reliable, part of which is to eliminate temporary tables. The idea is this: if a slave is stopped (or crashed) while a temporary table is open and is then restarted, the temporary table doesn't exist anymore, and the slave will have problems trying to replay any further statements that refer to these tables.

INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine

MySQL Performance Blog - Sat, 24/05/2008 - 1:14pm

Much has been written about the new InnoDB pluggable storage engine, which Innobase released at the MySQL conference last month.

Using flow control functions for performance monitoring queries

MySQL Performance Blog - Sat, 24/05/2008 - 8:43am

I'm not big fan on flow control functions like IF or CASE used in MySQL Queries as they are often abused used to create queries which are poorly readable as well as can hardly be optimized well by MySQL Optimizer.

One way I find IF statement very useful is computing multiple aggregates over different set of rows in the single query sweep.

Apache PHP MySQL and Runaway Scripts

MySQL Performance Blog - Wed, 21/05/2008 - 5:55am

Sometimes due to programming error or due to very complex query you can get your PHP script running too long, well after user stopped waiting for the page to render and went browsing other sites.
Looking at Server-Status I've seen scripts executing for hours sometimes which is obviously the problem - they take Apache Slot, MySQL Connection and other resources.

I had discussion today who was thinking Apache would kill the script after "Timeout" specified in Apache configuration is reached - this was not my experience so I decided to run couple of tests to check it.

Dangerous command

MySQL Performance Blog - Wed, 21/05/2008 - 5:08am

Remembering that I did RENAME DATABASE in MySQL 5.1.21 and found it useful I tried it with 5.1.24 (I was playing with 20GB InnoDB database, so dumping is not fastest way) and all my tries finished with "Syntax error".
So RTMF and documentation says
"This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23."

Wanted: Better memory profiling for MySQL

MySQL Performance Blog - Mon, 19/05/2008 - 5:40am

Quite frequently I would log in to customers system and find MySQL using too much memory. I would look at memory consumed by Innodb (it is often higher than innodb_buffer_pool_size) substract memory used by other global buffers such as query_cache_size and key_buffer and will in many cases see some mysterous memory which I can’t really explain. It can be several Gigabytes accounting for over 50% of memory usage of MySQL in some cases, though typically it is much smaller fraction.

Concurrent inserts on MyISAM and the binary log

MySQL Performance Blog - Thu, 15/05/2008 - 12:22am

Recently I had an interesting surprise with concurrent inserts into a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the process list was filling up with queries in Locked status.

MySQL Is back to Open Source Camp ?

MySQL Performance Blog - Wed, 14/05/2008 - 6:11am

Looking at Kaj’s Blog Annoucement MySQL has pulled back on the plans to release portions of the servers as Open Source only.

I am extremely happy to hear these news ! This is good for MySQL as a company, MySQL customers and MySQL users.

I’m hoping Community feedback was serious contributer to this decision, though I know there were a lot of Internal discussions as well. In any case this sends a great message to community - Speak up and you may be heard.

MySQL Performance Engineer opening

MySQL Performance Blog - Tue, 13/05/2008 - 10:15pm

As you know we love to analyze performance of various MySQL features, benchmark, compare, analyze things and post our findings on MySQL Performance Blog. However recently we got too busy with serving out customers and the backlog of things to take a look and write about is just growing larger and larger. So we decided to hire someone who could focus on such tasks.

What are we expecting from MySQL Performance Engineer ?

Quickly preloading Innodb tables in the buffer pool

MySQL Performance Blog - Fri, 02/05/2008 - 6:13am

In the previous post I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from a bit more information.

Learning about MySQL Table Fragmentation

MySQL Performance Blog - Fri, 02/05/2008 - 5:57am

Recently I was working with the customer who need quick warmup - to get Innodb table fetched in memory as fast as possible to get good in memory access performance.

To do it I run the query: “SELECT count(*) FROM tbl WHERE non_idx_col=0″ I use this particular form of query because it will do full table scan - running count(*) without where clause may pick to scan some small index instead.

T2000 CPU Performance - Watch out

MySQL Performance Blog - Thu, 01/05/2008 - 5:00pm

Sun is aggressively pushing T2000 as Scalable MySQL Platforms, and indeed it is Scalable in terms of high concurrency workloads - it is able to execute a lot of concurrent threads and so speed gain from 1 thread to say 32 thread will be significant.

But thing a lot of people miss is - Being Scalable is Not Enough - you need to scale from reasonable base to claim the good performance, and this is where T2000 performs subpar in many cases.

Can having information public hurt consulting business ?

MySQL Performance Blog - Thu, 01/05/2008 - 6:33am

People frequently ask me if the fact we keep information public can hurt our consulting business ? Lets keep aside for the moment amount of new business publishing this information brings to us but think it also have significant negative effect because people find information on MySQL Performance Blog and use it instead of purchasing our services ?

Percona Team presentations from MySQL Users Conference 2008 published

MySQL Performance Blog - Thu, 01/05/2008 - 3:08am

If you’ve missed our presentations on MySQL Users Conference you can catch up now by taking a look at the slides, which are now published in presentations section of our company web site. You can also find a lot of old presentations in the same location.

Enjoy !

Entry posted by peter | No comment

MySQL Replication vs DRBD Battles

MySQL Performance Blog - Tue, 29/04/2008 - 3:50am

Well these days we see a lot of post for and against (more, more) using of MySQL and DRBD as a high availability practice.

The MySQL optimizer, the OS cache, and sequential versus random I/O

MySQL Performance Blog - Tue, 29/04/2008 - 2:18am

In my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star schema data warehousing scenario.

In short, a query that could take several days to run with one join order takes an hour with another, and the optimizer chose the poorer of the two join orders. Why is one join order so much slower than the other, and why did the optimizer not choose the faster one? That's what this post is about.

How multiple disks can benefit for single client workload ?

MySQL Performance Blog - Sun, 27/04/2008 - 6:01am

Let us talk few more about disks. You might have read my previous post and Matt’s Reply and it looks like there are few more things to clarify and explain.

Is disk Everything for MySQL Performance ?

MySQL Performance Blog - Fri, 25/04/2008 - 7:20am

I read very nice post by Matt today and it has many good insights though I can’t say I agree on all points.

MySQL Architecture meeting at Google

MySQL Performance Blog - Fri, 25/04/2008 - 6:20am

Friday after MySQL Users Conference we had a smaller meeting at Google campus to talk about MySQL architecture mainly focusing on storage engine vendors and other extension areas.

MySQL extensions for hosting

MySQL Performance Blog - Thu, 24/04/2008 - 10:59am

A few weeks ago I was asked to isolate some functionalities from Mark Callaghan’s MySQL patch bundle. They were extensions adding per-user and per-table accounting to the database, accessible with a new set of commands such as SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS and SHOW USER_STATISTICS. The first two can interest anyone to periodically check what data or which index are the most active or which are not being used at all and could be candidates for dropping.


Subscribe to MySQL Performance Blog