Today I was doing some work on one of our database servers (each of them has 4 SAS disks in RAID10 on an Adaptec controller) and it required huge multi-thread I/O-bound read load. Basically it was a set of parallel full-scan reads from a 300Gb compressed innodb table (yes, we use innodb plugin). Looking at the iostat I saw pretty expected results: 90-100% disk utilization and lots of read operations per second. Then I decided to play around with linux I/O schedulers and try to increase disk subsystem throughput. Here are the results:

In previous search benchmarks, I utilized random content generated with Drupal's devel module. In these latest benchmarks, I used an actual sanitized copy of the Drupal.org community website database, with email addresses and passwords removed. The first tests were intended to confirm that Xapian continues to perform well with large amounts of actual data. Additional tests were performed to measure the effect of various MySQL tunings and configurations. The following data was derived from several hundred benchmarks run on an Amazon AWS instance over the past week using the SearchBench module.

These tests confirm that Xapian continues to offer better search performance than Drupal's core search module. Contrary to popular belief, the data also shows that using the InnoDB storage engine for search tables significantly outperforms using the MyISAM storage engine for search tables, especially when your database server has sufficient RAM. The …

Revision: 8 - Last Update: September 03 2008

This is the first part of a series of short articles with a how-to approach about MySQL® Performance Monitoring, Tuning & Auditing. We will see the question from a GUI prospective. In particular we will describe which monitoring-oriented features HoneyMonitor, a GUI for MySQL® currently in alpha development, implements.

I will explain how HoneyMonitor let you

1. install an audit database on your server, without the need of using 3th Party Agents nor using remote repository databases
2. enable the auditing and start monitoring your server
3. tuning your server changing a few suggested list of variables to get better performance.

We will use only the 5.1.x series of the Server as we use some Scheduled Events and the Event Scheduler has been added only in the 5.1 branch. In particular we will use MySQL® 5.1.24-rc. We will also do some tests …

Tag1 Consulting is focused on improving Drupal's performance and scalability. We also believe that when information is freely shared, everyone wins. Toward these ends, we are working on an online book titled, "Drupal Performance and Scalability". The book is divided into five main sections, Drupal Performance, Front End Performance, Improved Caching and Searching, Optimizing the Database Layer, and Drupal In The Cloud. The book is primarily aimed toward users running …

Had an interesting thought today, I wonder how much faster ( if at all ) updating via insert on duplicate key in bulk was compared to individual update statements.  The client  app I was looking at receives updates to a counter from various severs around the world and they are updating a row that contains time metrics.  For instance update traffic set visistcount = visistcount + 1000 where customer = 123 and hour = ‘12′ and date=’7/15/2008′.  These statements are driven by feeds from the various servers, so it lends itself to bulk operations very easily.   It seemed like batching these up will minimally save the parse and network travel time.

Basically instead of :

update bulk_update set val=10+val where val1 = 20;
update bulk_update set val=10+val where val1 = 21;
update bulk_update set val=10+val where val1 = 22;
update bulk_update set val=10+val where val1 = 23;

use …

Properties:

Description:

This cache is used by MyISAM to optimize bulk inserts. This cache is a special tree-like structure. Bulk inserts include statements like LOAD DATA INFILE..., …

I was at a client site with Yves Trudeau ( another MySQL consultant ) and the client had purchased a brand new top of the line 16 core server.  It is well documented in many places that scalability beyond 4-8 cores with innodb is less then optimal.  We were asked for a ballpark on the performance of a 16 cores vs an 8 cores, and specifically if their were any options to reduce the number of cores the mysqld process could use.   We decided to benchmark this using DBT2.   To do the test we ended up setting the CPU affinity of the mysqld process.  You can set this with the following command:  taskset.   Yves ended up trying the same DBT2 tests for 1-16 cores.    I won’t write too much about the scalability here, as I said their are way better resources out their that can explain it better, but what I wanted to do is post the results of …

While looking at the performance of multiple datafiles I noticed something kind of interesting.  A query searching for a integer based PK value that had the value escaped with single quotes was faster then another lookup on a PK without the single quotes.  That seemed a little counterintuitive… if anything I would expect escaping an integer to take a little longer.  But because they were searching for different keys, its not really all that meaningful, but i thought I would try and see just how much of an impact quoting your integer will cause.  So I used the same query tool I put together in perl to do the multi-datafile tests to run 2 queries searching for an integer PK.

See here:

mysql> select qryid, note, avg(averagequeryruntime),  count(*), querytext from Logger_single group by qryid, note; …

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 …