Jul
29
2011
--

Intel 320 SSD read performance

(this is cross-post from http://www.ssdperformanceblog.com/)
While PCI-e Flash cards show great performance, I am often asked about alternatives, as price for PCI-e cards is still significant and not acceptable for small companies and startups.

Intel 320 SSD appears to be a popular drive with a quite acceptable price.
I wrote about write performance of these cards, and now let’s take look on a random read workload.

I used a Cisco UCS C250 as base hardware, comparing in it:

  • regular RAID10 over 8 SAS 2.5 disks
  • single Intel 320 SSD directly attached to a HighPoint RocketRAID 2300
  • two Intel 320 SSD in hardware RAID0 mode, attached to a LSI SAS9211-4i controller

For simulating the workload I used sysbench’s fileio random reads. Scripts and raw results available on Launchpad.

Let’s see throughput results:

Throughput, MiB/sec (more is better)
threads Intel 320 Intel 320 2 strip RAID10 ratio Intel 320 / RAID10 ratio Intel 320 2 strip / RAID10
1 30.27 31.18 3.75 8.07 8.31
2 55.18 60.49 6.98 7.91 8.67
4 95.13 112.85 12.10 7.86 9.33
8 143.58 191.64 19.05 7.54 10.06
16 174.75 277.70 26.70 6.54 10.40
32 174.60 351.84 32.90 5.31 10.69

And response times:

95% response time, ms (less is better)
threads Intel 320 SSD Intel 320 SSD strip RAID ratio RAID/Intel 320 ratio RAID/Intel 320 strip
1 0.53 0.56 6.13 11.57 10.95
2 0.72 0.59 7.27 10.10 12.32
4 0.89 0.74 10.07 11.31 13.61
8 1.24 0.95 15.63 12.60 16.45
16 1.76 1.38 25.52 14.50 18.49
32 3.33 2.15 47.35 14.22 22.02

As conclusion, this card provides great read performance. A single card provides 5-8x better throughput and 10-14x better response time. Striping helps to increase throughput in 8-10x and response time in 10-22x.

While there are questions about write performance (see my previous post), I think this card is very suitable for read-intensive tasks, where you can expect significant improvements.




Jul
29
2011
--

MySQL Row Generator

A row generator is a method to generate numbered rows on demand. It is possible to build a row generator with pure standard SQL—using recursive common table expressions (CTE), the with clause. If you never heard of the with clause, it’s probably because MySQL doesn’t implement that specific part of the SQL-99 standard (feature request from 2006). This article introduces generator views for MySQL. Not as powerful as recursive CTEs, but good enough in most cases. But before going into the implementation details, I’ll show you a use case for a row generator.

Row generators are useful to fill gaps in results. Consider the following query:

SELECT COUNT(*), sale_date
  FROM sales
 WHERE sale_date > CURDATE() - INTERVAL 1 MONTH
 GROUP BY sale_date
 ORDER BY sale_date;

The result will not contain rows for days where no sales record exists at all. You can complete the result with a row generator.

Imagine a view, GENERATOR, that returns numbered rows like this:

SELECT n
  FROM generator
 WHERE n < 31;

+-----+
|  n  |
+-----+
|   0 | 
|   1 | 
. . . .
|  29 | 
|  30 | 
+-----+
31 rows in set (0.00 sec)

This is the basic functionality of a row generator. Having that, it is quite simple to list all days since a month ago:

SELECT CURDATE() - INTERVAL n DAY dt
  FROM generator
 WHERE CURDATE() - INTERVAL n DAY 
     > CURDATE() - INTERVAL 1 MONTH;

+------------+
| dt         |
+------------+
| 2011-07-29 | 
| 2011-07-28 | 
. . . . . . . 
| 2011-07-01 |
| 2011-06-30 | 
+------------+
30 rows in set (0.00 sec)

Finally, we can use an outer join to combine the original result with the generated dates:

SELECT IFNULL(sales, 0) sales, dt sale_date
  FROM
     ( SELECT CURDATE() - INTERVAL n DAY dt
         FROM generator
        WHERE CURDATE() - INTERVAL n DAY 
            > CURDATE() - INTERVAL 1 MONTH
     ) dates
  LEFT OUTER JOIN
     ( SELECT COUNT(*) sales, sale_date
         FROM sales
        WHERE sale_date > CURDATE() - INTERVAL 1 MONTH
        GROUP BY sale_date
     ) sales
    ON (sales.sale_date = dates.dt)
 ORDER BY dt;

The left side of the join has all the generated dates so that the outer join pads the right wide with NULL, if there were no sale on that day. The IFNULL turns the missing sales count into a zero.

So far, so good. But the problem is that MySQL has no row generator that produces an arbitrary number of rows as needed. Still there is a technique that is good enough in most cases.

It starts with something rather ridiculous:

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

It is a generator to produces 16 rows. You can use the same technique for larger generators as well. E.g., to produce one million rows, like shown at the end of this article. Just kidding. There is, of course, a better way:

CREATE OR REPLACE VIEW generator_256
AS SELECT ( hi.n * 16 + lo.n ) AS n
     FROM generator_16 lo
        , generator_16 hi;

This view builds the cross join (Cartesian product) of the previous view with itself. That means it pairs every row from the "lo" side with all rows from the "hi" side of the join. The result has 265 rows (16×16).

Considering the introductory example, it is not only important to generate an arbitrary number of rows, we need numbered rows to make use of it. For that purpose, I threat the two sides of the cross join like digits in a hexadecimal number. The "lo" side building the least significant, the "hi" side the most significant digit. Explained by SQL:

SELECT CONCAT(LPAD(hi.n,2,' '), ' * 16 + '
            , LPAD(lo.n,2,' '), ' = '
            , LPAD(hi.n*16+lo.n, 3,' '))
              AS "HI        LO   SEQ"
  FROM generator_16 lo, generator_16 hi;

+--------------------+
| HI        LO   SEQ |
+--------------------+
|  0 * 16 +  0 =   0 | 
|  0 * 16 +  1 =   1 | 
|  0 * 16 +  2 =   2 | 
|  0 * 16 +  3 =   3 | 
|  0 * 16 +  4 =   4 | 
|  0 * 16 +  5 =   5 | 
|  0 * 16 +  6 =   6 | 
|  0 * 16 +  7 =   7 | 
|  0 * 16 +  8 =   8 | 
|  0 * 16 +  9 =   9 | 
|  0 * 16 + 10 =  10 | 
|  0 * 16 + 11 =  11 | 
|  0 * 16 + 12 =  12 | 
|  0 * 16 + 13 =  13 | 
|  0 * 16 + 14 =  14 | 
|  0 * 16 + 15 =  15 | 
|  1 * 16 +  0 =  16 | 
|  1 * 16 +  1 =  17 | 
. . . . . . . . . . .
| 15 * 16 + 14 = 254 | 
| 15 * 16 + 15 = 255 | 
+--------------------+
256 rows in set (0.00 sec)

I guess you know how to build a larger generator now?

There are, of course, some limitations:

  • The views are bounded

    They cannot produce an arbitrary number of rows. But you can prepare views that generate large number of rows (see below).

  • The views will always build the full Cartesian product

    Although you can limit the result with the where clause, the work to produce all rows is still done. It’s just filtering the unneeded. That means, it is very inefficient to use a large generator view if you need just a few rows.

Another aspect is that the result order is undefined—although the example above produces the numbers is ascending order. That’s just because the way MySQL joins the two views. If you build another meta view, e.g., GENERATOR_64K, you’ll note that the order is not ascending anymore—because of MySQLs Block Nested-Loop Join. But that doesn’t mean that the generator doesn’t work, each number is still generated exactly once. If you need a particular order, just use the ORDER BY clause on the outermost select.

Warning

Don’t use LIMIT to cut the view as needed because you might receive unexpected numbering. Please note that using ORDER BY and LIMIT in combination returns the correct result, but requires the intermediate result to be stored temporarily.

Use where to filter on the returned numbers. There is no need to store the intermediate result in that case.

Finally, here are some handy generator views up to 1 "mega-row". They use a minor improvement: bit-shift operations instead of arithmetics.

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

Even the last view, producing 220 rows, returns the result in a seconds on current hardware. However, the only use I have for the GENERATOR_1M view is producing test data.

Always use the smallest possible generator for best performance.

Jul
29
2011
--

MySQL Row Generator

A row generator is a method to generate numbered rows on demand. It is possible to build a row generator with pure standard SQL—using recursive common table expressions (CTE), the with clause. If you never heard of the with clause, it's probably because MySQL doesn't implement that specific part of the SQL-99 standard (feature request from 2006). This article introduces generator views for MySQL. Not as powerful as recursive CTEs, but good enough in most cases. But before going into the implementation details, I'll show you a use case for a row generator.

Row generators are useful to fill gaps in results. Consider the following query:

SELECT COUNT(*), sale_date
  FROM sales
 WHERE sale_date > CURDATE() - INTERVAL 1 MONTH
 GROUP BY sale_date
 ORDER BY sale_date;

The result will not contain rows for days where no sales record exists at all. You can complete the result with a row generator.

Imagine a view, GENERATOR, that returns numbered rows like this:

SELECT n
  FROM generator
 WHERE n < 31;

+-----+
|  n  |
+-----+
|   0 | 
|   1 | 
. . . .
|  29 | 
|  30 | 
+-----+
31 rows in set (0.00 sec)

This is the basic functionality of a row generator. Having that, it is quite simple to list all days since a month ago:

SELECT CURDATE() - INTERVAL n DAY dt
  FROM generator
 WHERE CURDATE() - INTERVAL n DAY 
     > CURDATE() - INTERVAL 1 MONTH;

+------------+
| dt         |
+------------+
| 2011-07-29 | 
| 2011-07-28 | 
. . . . . . . 
| 2011-07-01 |
| 2011-06-30 | 
+------------+
30 rows in set (0.00 sec)

Finally, we can use an outer join to combine the original result with the generated dates:

SELECT IFNULL(sales, 0) sales, dt sale_date
  FROM
     ( SELECT CURDATE() - INTERVAL n DAY dt
         FROM generator
        WHERE CURDATE() - INTERVAL n DAY 
            > CURDATE() - INTERVAL 1 MONTH
     ) dates
  LEFT OUTER JOIN
     ( SELECT COUNT(*) sales, sale_date
         FROM sales
        WHERE sale_date > CURDATE() - INTERVAL 1 MONTH
        GROUP BY sale_date
     ) sales
    ON (sales.sale_date = dates.dt)
 ORDER BY dt;

The left side of the join has all the generated dates so that the outer join pads the right wide with NULL, if there were no sale on that day. The IFNULL turns the missing sales count into a zero.

So far, so good. But the problem is that MySQL has no row generator that produces an arbitrary number of rows as needed. Still there is a technique that is good enough in most cases.

It starts with something rather ridiculous:

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

It is a generator to produces 16 rows. You can use the same technique for larger generators as well. E.g., to produce one million rows, like shown at the end of this article. Just kidding. There is, of course, a better way:

CREATE OR REPLACE VIEW generator_256
AS SELECT ( hi.n * 16 + lo.n ) AS n
     FROM generator_16 lo
        , generator_16 hi;

This view builds the cross join (Cartesian product) of the previous view with itself. That means it pairs every row from the "lo" side with all rows from the "hi" side of the join. The result has 265 rows (16×16).

Considering the introductory example, it is not only important to generate an arbitrary number of rows, we need numbered rows to make use of it. For that purpose, I threat the two sides of the cross join like digits in a hexadecimal number. The "lo" side building the least significant, the "hi" side the most significant digit. Explained by SQL:

SELECT CONCAT(LPAD(hi.n,2,' '), ' * 16 + '
            , LPAD(lo.n,2,' '), ' = '
            , LPAD(hi.n*16+lo.n, 3,' '))
              AS "HI        LO   SEQ"
  FROM generator_16 lo, generator_16 hi;

+--------------------+
| HI        LO   SEQ |
+--------------------+
|  0 * 16 +  0 =   0 | 
|  0 * 16 +  1 =   1 | 
|  0 * 16 +  2 =   2 | 
|  0 * 16 +  3 =   3 | 
|  0 * 16 +  4 =   4 | 
|  0 * 16 +  5 =   5 | 
|  0 * 16 +  6 =   6 | 
|  0 * 16 +  7 =   7 | 
|  0 * 16 +  8 =   8 | 
|  0 * 16 +  9 =   9 | 
|  0 * 16 + 10 =  10 | 
|  0 * 16 + 11 =  11 | 
|  0 * 16 + 12 =  12 | 
|  0 * 16 + 13 =  13 | 
|  0 * 16 + 14 =  14 | 
|  0 * 16 + 15 =  15 | 
|  1 * 16 +  0 =  16 | 
|  1 * 16 +  1 =  17 | 
. . . . . . . . . . .
| 15 * 16 + 14 = 254 | 
| 15 * 16 + 15 = 255 | 
+--------------------+
256 rows in set (0.00 sec)

I guess you know how to build a larger generator now?

There are, of course, some limitations:

  • The views are bounded

    They cannot produce an arbitrary number of rows. But you can prepare views that generate large number of rows (see below).

  • The views will always build the full Cartesian product

    Although you can limit the result with the where clause, the work to produce all rows is still done. It's just filtering the unneeded. That means, it is very inefficient to use a large generator view if you need just a few rows.

Another aspect is that the result order is undefined—although the example above produces the numbers is ascending order. That's just because the way MySQL joins the two views. If you build another meta view, e.g., GENERATOR_64K, you'll note that the order is not ascending anymore—because of MySQLs Block Nested-Loop Join. But that doesn't mean that the generator doesn't work, each number is still generated exactly once. If you need a particular order, just use the ORDER BY clause on the outermost select.

Warning

Don't use LIMIT to cut the view as needed because you might receive unexpected numbering. Please note that using ORDER BY and LIMIT in combination returns the correct result, but requires the intermediate result to be stored temporarily.

Use where to filter on the returned numbers. There is no need to store the intermediate result in that case.

Finally, here are some handy generator views up to 1 "mega-row". They use a minor improvement: bit-shift operations instead of arithmetics.

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

Even the last view, producing 220 rows, returns the result in a seconds on current hardware. However, the only use I have for the GENERATOR_1M view is producing test data.

Always use the smallest possible generator for best performance.

Jul
29
2011
--

Reasons for MySQL Replication Lag

One common theme in the questions our MySQL Support customers ask is Replication Lag. The story is typically along the lines everything is same as before and for some unknown reason the slave is started to lag and not catching up any more. I always smile at “nothing has changed” claim as it usually wrong, and one should claim I’m not aware of any change. Digging deeper we would find some change, though often subtle as computers are state machines and with no external and internal changes they behave the same way. First let me start pointing out common causes of replication lag

Hardware Faults Hardware faults may cause reduced performance causing replication to lag. Failed hard drive getting RAID in degraded mode could be one common example. Sometimes it is not fault per say but operating mode change may cause the problem. Many RAID controllers have to periodically go through battery learning cycle which switches RAID cache in write-through mode which can cause serve performance degradation.

Configuration Changes Have you changed MySQL configuration ? OS Configuration or Hardware configuration or could have anyone changed it without your knowledge ? If yes this could cause problems with replication lag (and performance in general). Typical problems for replication include setting sync_binlog=1, enabling log_slave_updates, setting innodb_flush_log_at_trx_commit=1. Also note there is no such thing as safe change. Even such “it can only do good” change as increasing buffer pool size could cause performance problems.

MySQL Restart MySQL Restarts may include recovery time but what is the most important they often come with a need of warmup, because buffer pool content is lost on restart. Many people understanding this do not quite recognize how long it takes, and by the fact removing slave read traffic from non warmed up slave will often cause warmup to be longer, because it has to happen in the single thread. In the worse case scenario you might be looking at some 100 pages per second which is 1.6MB/sec which may take 24+hours to feel 200GB buffer pool. Automatic Buffer Pool Save/Restore available in Percona Server is great help in this case.

Changing Slave Role Changing Slave role, such as swapping active and passive masters in Master-Master setup is often cause of the problem for similar reason as MySQL Restart – the traffic on different boxes might be different and hence cache content.

Replication Traffic Changes Changing in the replication traffic (what updates are being replicated) are frequent cause of concern. Remember replication runs as a single thread and something which might not look like a lot of load for your powerful master may be enough to take replication overboard. Application changes and Traffic increases are frequent causes of problems but more subtle things such as “small” batch job being ran which does a lot of updates can also be the problem. It is nice to analyze your replication traffic regularly with mk-query-digest (Percona Server marks Slave Thread in slow query log so it is easy to filter out events) so you can see whenever there are any suspicious events in the replication traffic. An interesting thing to note – scripts can hurt replication traffic well after they are ended. I’ve seen cases when overly eager batch job doing updates from 20 threads on the master will create enough updates for a few hours, to make replication lag for over 24 hours until they all could be done with.

Slave Read Traffic Changes Slave read traffic impacts replication performance dramatically and even if nothing has changed to the replication traffic, changes to concurrent traffic on the slave can affect replication capacity a lot. Checking traffic for changes with mk-query-digest is a great way to deal with it. Note you need not only to check the types and frequencies of queries but also how much IO they are causing and how many rows they analyzes. Changes could be in the data which is being accessed by the queries or query execution plans.

Side load on IO subsystem Could there be any side load on IO subsystem ? This can take many forms. It can be you running backup through xtrabackup or LVM snapshot, RAID controller doing RAID validation, some centrally kicked of SAN backup, intensive activity on the other tenants sharing physical hardware with you in SAN, NAS or Cloud installations. It all can be the cause of the problems. In case the side load is going through the same operating system you should be able to see it by comparing IO MySQL is doing to IO going to devices from OS standpoint. If this is external and not recorded by operating system you often can see it as higher IO load (IO utilization, response time) while less work is being done in terms of requests per second. Should be easy to see on the graphs. If you have not yet installed something for graphing check out Cacti Tempates which have a lot of such goodness.

Data Size ChangesThe more data you have the more IO bound workload is likely to be and also it is possible queries become more complicated scanning more rows than before. Data growth is frequent cause of performance problems and slave lag among them. The relationship is also very workload dependent – some may see only modest slowdown when data doubles, while in other case 10-20% difference in data size can be the difference between slave never lags and slave never is able to catch up.

MySQL or OS version changesMySQL version changes (even minor upgrade) may introduce issues which causes replication lag. Same may happen with operating system and other system software involved. In could be even system which queries mysql. I’ve seen monitoring updates which would add excessively expensive and frequent query on information_schema which caused a lot of issues. Note there is a lot of complicated software which runs on devices themselves now, so watch for things like firmware updates bios updates etc, which even though not frequent may be cause of the issues.

Finally – do not be mistaken, it is not always just one issue which is responsible for everything, Quite often it is many things which come together to form the perfect storm – just a bit of data growth which just a bit different workload and when backup taken at the wrong time may be responsible for unexpected replication lag.

Jul
28
2011
--

Percona Server 5.1.57 with Galera 0.8.1

Codership team announced availability of MySQL/Galera 0.8.1, which is minor release, but actually it has bunch of improvements that makes Galera replication more user friendly (there are many bugs fixed, reported by me personally, what annoyed me a lot).

As part of my evaluation activity I ported MySQL/Galera 0.8.1 to Percona Server/Galera 0.8.1 and you can get source code on Launchpad.

I appreciate the fact that not everybody has fun from compiling source code (hint, hint for Drizzle developers), that is why I also made binaries for RHEL 6.1 / Oracle Linux 6.1
http://www.percona.com/downloads/TESTING/Galera/percona-5.1.57-galera-0.8.1.tar.gz

This is ABSOLUTELY NO production quality release, but you are welcome to play with it.

Jul
28
2011
--

How Innodb Contention may manifest itself

Even though multiple fixes have been implemented in Percona Server and MySQL 5.5, there are still workloads in which case mutex (or rw-lock) contention is a performance limiting factor, helped by ever growing number of cores available in the systems. It is interesting though the contention may manifest itself in the different form from the system monitoring standpoint. In many cases as heavy contention happens user CPU will be very high, and the context switches will be somewhere reasonable. In others you would see the CPU usage being low with a lot of CPU being idle, increased compared to normal workload portion of system CPU and high number of context switches. These correspond to different contention situations which can be handled differently.

First situation often corresponds to Innodb spending a lot of CPU time running “loops” as part of spinlock implementation. In many cases busy wait is indeed more efficient than doing context switches, however sometimes there is just too much spinning and it becomes inefficient. Reducing innodb_sync_spin_loops variable to smaller values is known to help in some of those cases.
Second situation corresponds to a lot of context switches being done, which well may be normal – if your workload has large portion of it being “critical section” where only one thread can be working at the time you should expect a lot of threads waiting and the more CPU cores you have available the less CPU utilization you can have. Sometimes though you may be wasting time with context switching because Innodb is not spinning too much, thus you can consider increasing innodb_sync_spin_loops to a higher number.

It is worth to note fine tuning Innodb Contention with number of spin locks loops is something i would consider last resort tuning, the performance improvements it provides is typically rather limited and can be used to buy a little bit more time while you’re upgrading to Percona Server or newer MySQL version or doing application/architecture changes. Reducing number of competing threads (such as limiting number of Apache Children) as well as adjusting innodb_thread_concurrency and doing other server tuning should be first on your list.

I also wanted to highlight how much context switches you can get from the system, as unlike CPU usage it is not obvious what to consider a high number. With modern hardware and modern Linux process scheduler you can be looking at 500.000+ context switches which system can do. I would look at some 200.000 context switches a second as the point where context switching itself can be seen as a problem, though it is better not to look at the absolute number but at how it changes compared to your normal situation. In many cases when bad contention is happening I see number of context switches increasing 3x or more compared to normal state.

Jul
27
2011
--

What’s required to tune MySQL?

I got a serendipitous call (thanks!) yesterday asking what would be needed to tune[1] a database for better performance. It is a question that I hear often, but I never thought about answering it in public. Here’s a consolidated version of what I explained during our conversation.

Have realistic expectations about configuration

The first thing to know is that server configuration itself really isn’t something you should expect to deliver huge wins. If MySQL is actually badly configured, you can hurt its performance significantly. Correcting these mistakes can correspondingly improve performance. But such mistakes are relatively few and/or non-obvious to make. A few of the common ones I see are not configuring the InnoDB buffer pool size or log file size, and not using InnoDB. If your server really hasn’t been configured — that is, it’s running with a default configuration — then it’s quite possible you have one of these scenarios.

Most other serious configuration mistakes will only affect you in edge cases. If you get bitten, the impact can be severe. But the likelihood of that happening is not extremely high in the general population. This blog’s articles often illustrate extreme cases, so keep in mind that the vast majority of MySQL installations don’t suffer from the variety and severity of problems we write about here. Examples include what happens when you have query cache contention, DNS problems, or very slow I/O. In addition to unlikely circumstances, I sometimes see unlikely settings, such as making the sort buffer size much too large (a gigabyte comes to mind).

As a result, although it’s a good thing to have an expert look at your system and let you know if something obscure is wrong with the configuration, it’s not likely to improve the system’s performance dramatically, all things considered.

More than configuration

To do a proper job, the consultant will need more than simply looking at your my.cnf file. It’s sometimes possible to look at the my.cnf file and see something wrong. Usually, though, the consultant needs to look at several important factors, including the following:

  • The hardware
  • The server configuration
  • The workload
  • The queries
  • The schema design (table design, data types, indexing, etc)
  • The data itself

All of these things interact, and it’s important to have an overall view of the server to form a responsible opinion about its configuration. If a particular person has been working with the system for a time and knows it well, it’s good to keep that person involved if possible.

In addition to requiring more than the configuration as input, the consultant is likely to suggest changes to other areas as a deliverable of the process. For example, you may receive suggestions about reindexing, or changing queries, or even changing the way that the application uses the database. These changes can give a much greater improvement in your database’s performance.

Conclusion

Don’t expect huge improvements from a review of your server’s configuration, unless you think that something is seriously wrong. It’s a wise investment of time to take a deeper look at the server, including how the data, schema, queries, and workload interact with the server itself. This review can sometimes deliver large gains that go far beyond configuration alone.

[1] I prefer to avoid the word “tuning,” because database tuning is an activity that can be done endlessly, with little definition or quantification of results. I prefer to speak specifically of configuring, or of performance optimization, or something more descriptive and concrete instead.

Jul
25
2011
--

Percona Xtrabackup 1.6.2 released!

Percona is glad to announce the release of Percona XtraBackup 1.6.2 on 25 July, 2011 (Downloads are available here and from thePercona Software Repositories).

This release is purely composed of bug fixes and is the current stable release of Percona Xtrabackup.

All of Percona’s software is open-source and free, all the details of the release and its development process can be found in the 1.6.2 milestone at Launchpad.

New Options

–version

The --version option has been added to the xtrabackup binary for printing its version. Previously, the version was displayed only while executing the binary without arguments or performing a backup. Bug Fixed: #610614 (Alexey Kopytov).

Changes

  • As exporting tables should only be used with innodb-file-per-table set in the server, the variable is checked by xtrabackup when using the --export option. It will fail before applying the archived log without producing a potentially unusable backup. Bug Fixed: #758888 (Alexey Kopytov).

Bugs Fixed

  • When creating an InnoDB with its own tablespace after taking a full backup, if the log files have been flushed, taking an incremental backup based on that full one would not contain the added table. This has been corrected by explicitly creating the tablespace before applying the delta files in such cases. Bug Fixed: #766607(Alexey Kopytov).
  • In some cases, innobackupex ignored the specified xtrabackup binary with the --ibbackup option. Bug Fixed: #729497 (Stewart Smith).
  • Minor file descriptors leaks in error cases were fixed. Bug Fixed: #803718 (Stewart Smith).

Other Changes

  • Improvements and fixes on the XtraBackup Test Suite: #744303#787966 (Alexey Kopytov)
  • Improvements and fixes on platform-specific distribution: #785556 (Ignacio Nin)
  • Improvements and fixes on the XtraBackup Documentation: #745185#721339 (Rodrigo Gadea)

For more information, please see the following links:

Jul
20
2011
--

Percona Live London!

The first Percona Live Conferences in San Francisco and New York were such wonderful successes that we have been inspired to take it across the ocean into the UK. We are therefore proud to announce that our next Percona Live Conference will be held London October 24-25, 2011 . Registration is now open and we are offering an early-bird registration for eager attendees who appreciate a discount. Register here: http://www.eventbrite.com/event/1909670877/eorg/

Additionally, the Percona Live team is ready to accept submissions of talks and/or tutorials from individuals who wish to speak at this two day event. Please go here: http://www.percona.com/live/london-2011/call-for-speakers/ to submit your talks.

Whether you are a speaker or an attendee, there are a number of sponsorship opportunities available. Please contact Percona’s sales team for further information.

Jul
19
2011
--

Server Outages at Percona

On Saturday July 16, Percona suffered a catastrophic failure of three disks on our primary web server. This was compounded by unexpected problems in recovery from our backups arising from staff changes. The net result is that several Percona web properties were offline from several hours to several days and that our cleanup continues. Interruptions in our provision of software downloads, documentation, and credit card transactions directly affected Percona customers and users of our software. However no customer data was compromised and customer access to our engineers via our customer service portal and online chat was uninterrupted. The web server that failed is completely separate from those that contain customer data.

The recovery lessons learned for us have been considerable and will be incorporated into our internal processes. Availability and performance of all of our websites is a top priority. On behalf of all of us at Percona, I apologize for the inconvenience this has caused for our users.

Sincerely,

- Tom Basil, COO

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com