Over the past couple of months I have seen a few different clients with innodb setup to have multiple datafiles opposed to using file per table or a single datafile. While I generally do not think twice about a handful of datafiles, I started seeing clients with 50-100 or more 2-4GB datafiles on a single RAID set ( or in some cases a single disk ), and this started to raise the little alarms in my head. Asking these various clients the key question, why? The answers were surprisingly similar. Things like: “we always did it this way” , “their used to be a 2GB file limit”, “we did this in oracle”, etc. Their was not a ton of compelling answers ( like manageability and performance ).

Looking at these systems it seemed like they are really causing “self-induced” fragmentation. They all have large tables, and are doing scans over large amounts of the data… for instance a 40GB table in a database with 2GB datafiles. In the best …

After stepping off of the GROUP_CONCAT() solution for calculating quantiles I figured it would be nice to find a better way to calculate the median too.
Solution
I previously wrote on how to calculate the median using GROUP_CONCAT(), but I think that this is a better way:

SELECT     AVG(length) AS median                 -- take the average of left and right median
,          MIN(length) AS left_median            -- 
,          MAX(length) AS right_median           --
,          @l          AS left_median_position   --
,          @r          AS right_median_position  --
FROM (
     SELECT     @n, length                       -- @n is just here to …

My previous post on calculating percentiles with MySQL generated some comments and good discussion. In particular, I got some very interesting comments from Vladimir.

Basically, Vladimir was doubtful whether the GROUP_CONCAT() solution would be optimal in comparison to a JOIN. His proposal is to solve it like this:

SELECT   SUM(g1.r) sr
,        g2.length l
,        SUM(g1.r)/(SELECT COUNT(*) FROM film) p
FROM    (SELECT COUNT(*) r, length FROM film GROUP BY length) g1
JOIN    (SELECT COUNT(*) r, length FROM film GROUP BY length) g2
ON       g1.length < g2.length
GROUP BY g2.length
HAVING p > 0.9
ORDER BY p
LIMIT 1

First, this query sets up two identical subqueries in …

The mysql proxy project has tremendous potential to make mysql administration and usage easier. I decided to throw some load at it to get a feel for how stable and performant it is.

On EC2, I set up 6 “small” images in an example proxy setup:

- One client machine to run sysbench
- One machine to act as a mysql proxy machine, running 0.6.1 (FC4 binary)
- Four identical database servers, running mysql 5.0.45

The database configuration was largely default, with InnoDB configured for 64MB buffer pool (just enough to ensure the sysbench table was entirely in memory), 512MB log files, and 1024 max connections.

mysql-proxy was run with the following command:

mysql-proxy –proxy-backend-addresses=ip-10-251-66-63.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-71-21.ec2.internal:3306 …

Testing MySQL sequential IO performance with different IO schedulers – Part 1

I stumbled upon an interesting article discussing the different Linux I/O schedulers that are available in the 2.6 kernel, and also discussing some of the deficiencies of the 2.4 “Linus elevator” I/O scheduler.

The default scheduler as of Kernel 2.6.18 is the “Completely fair queuing” scheduler, or CFQ. The previous default was the anticipatory scheduler between 2.6.0 and 2.6.17. Also available is a buffed-up version of the 2.4 scheduler called the deadline scheduler, and a “noop” scheduler. This article has a great discussion on what these schedulers mean for databases at a higher level.

Although old, …

If there is one thing that a DBA or data warehouse architect can count on, it is that data volumes will increase while budgets will decrease.

This is why MySQL 5.1 and its partitioning capabilities are so interesting. I’m going to demonstrate how you can build a small/medium-sized data warehouse or data mart (1-10 TB range) on a shoe-string budget.

the mission

I decided to convert a relatively large statistics table (750m rows, 140GB in size in about 10 partitions) on a test machine from MyISAM to the Archive storage engine. After a long conversion process, my data, on disk, ended up being about 21GB, for an impressive compression ratio of 6.7:1.

Prior to MySQL 5.1, one of the drawbacks to the archive storage engine was that you could not index it; however, with partition pruning, you can get yourself a “free” index on a large archive table by splitting it into date-based chunks, whether by …

As I indicated in my previous post on MySQL performance, we have been doing some performance work using an internally developed web2.0 application. Akara and I will be presenting this app publicly to a large audience for the first time at the upcoming Velocity Conference in Burlingame, CA on June 23, 24. Check out our abstract.  Most of our work uses Cool Stack so a lot of the results we will be presenting will be based on that. If you're struggling with performance issues, this conference may be worth checking out.
If you will be attending the conference, please stop by and say hello. It's always good to see people whom we only know through blogs and forums.

As I indicated in my previous post on MySQL performance, we have been doing some performance work using an internally developed web2.0 application. Akara and I will be presenting this app publicly to a large audience for the first time at the upcoming Velocity Conference in Burlingame, CA on June 23, 24. Check out our abstract.  Most of our work uses Cool Stack so a lot of the results we will be presenting will be based on that. If you're struggling with performance issues, this conference may be worth checking out.
If you will be attending the conference, please stop by and say hello. It's always good to see people whom we only know through blogs and forums.

As I indicated in my previous post on MySQL performance, we have been doing some performance work using an internally developed web2.0 application. Akara and I will be presenting this app publicly to a large audience for the first time at the upcoming Velocity Conference in Burlingame, CA on June 23, 24. Check out our abstract.  Most of our work uses Cool Stack so a lot of the results we will be presenting will be based on that. If you're struggling with performance issues, this conference may be worth checking out.
If you will be attending the conference, please stop by and say hello. It's always good to see people whom we only know through blogs and forums.