We've been running replication poll and we've got some answers, so I thought I would comment a little on the results of the poll and what our future plans with respect to replication is as a result of the feedback. As I commented in the previous post, there are some items that require a significant development effort, but the feedback we got helps us to prioritize.

The top five items from the poll above stands out, so I thought that I would comment on each of them in turn. The results of the poll were (when this post were written):

I guess many of you know, that using SELECT count(*) FROM table is problematic and slow when using Innodb tables.
This actually only applies to COUNT(*) queries without WHERE a clause as mentioned in the MySQL Performance Blog.

But if you got some slow count query in your application the best way to increase its performance is to replace / remove it.

So if you are going do to "SELECT count(*) FROM products" the best way, is to have a separated table
that stores the number of products. If you're inserting a row increment the counter, if you're deleting a row, decrement it.

Here is some example:
CREATE TABLE counter( number_of_products int(10) DEFAULT '0' NOT NULL);

Increment when you're adding a new product to the products table:

PLAIN TEXT SQL:

1. UPDATE counter SET …

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's …

Kristian Köhntopp has a wonderful article about using oprofile to track down problems in running programs. I thought I'd add a few thoughts.

If you need to get a vmlinux kernel on redhat, apparently you just need to install kernel-debuginfo, which will provide a vmlinux image you can profile against.

If you are using debian, unfortunately there is no package I could find to allow you to get a vmlinux. so what I did was:
($kver isn't a real variable - it's your kernel version. tab completion probably comes in handy at some point)

1. Install linux-tree-$kver - which gets you the debian kernel sources
2. Unpack the tar.bz2 file that is now in /usr/src
3. Copy /boot/config-$kver to /usr/src/linux-$kver/.config
4. cd /usr/src/linux-$kver
5. make oldconfig
6. make prepare
…

Looks like MySQL Camp 2006 was really interesting and useful for its attendees and for entire MySQL community. As the result of this meeting of many MySQL-related professionals we’ve got lots of interesting publications and I want to refer one of them in this post. Very interesting list of 84 MySQL performance tips has beed created on first day of this year MySQL Camp at Google headquarters:

1. Index stuff.
2. Don’t Index Everything
3. Use benchmarking
4. Minimize traffic by fetching only what you need.
   • Paging/chunked data retrieval to limit
   • Don’t use SELECT *
   • Be wary of lots of small quick queries if a longer query can be more efficient
5. Use EXPLAIN to profile the query …

This article is part of “Looking For Optimal Solution” series, devoted to testing various Ruby On Rails deployment schemes and doing some simple benchmarks on these schemes. General idea of testing is to find subset of most optimal RoR deployment schemes for different situations.

This small article is about Rails+Mongrel setup and its performance. List of other tested deployment schemes, description of testing methodology and, of course, all benchmark results you can find on “Ruby On Rails Benchmark Summary and Findings” page.

(more…)

two interesting posts arrived on the memcached list which might be interesting to performance people.

The first was a comparison of The fastest lanugage binding on which ‘P’ language performed better. To make a note the PHP version actually uses libmemcache a ‘C’ library which goes a bit of the way to explain the wild disparity in speeds.

The 2nd more interesting one (to me) was the discussion of how Digg switched from using mysql to memcached with v3 of their new interface to handle storing sessions, due to a hardware crash on their mysql server.

others mentioned using InnoDB for this instead of MyISAM, with the biggest issue …

Hey, look! Peter mentioned me in his presentation at the UC this year.

UC2006-MySQL-Performance-Landscape.pdf

I let him use the Sunfire T2000 I borrowed from Sun (which I should return today) to generate some statistics for the talk.

© cjcollier for C.J.'s WordPress of studlyness, 2006. | Permalink | No comment

Add to del.icio.us

Search blogs linking this post with …

Jay Pipes is a co-author of the recently published Pro MySQL recently spoke at Google on MySQL Performance Tuning Best Practices

You can check out a video of the presentation on Google Video

There is also a good video from Google called The Paradox of Choice: Why More is Less.