sorted by: recent | see : popular

Content Tagged with performance + MySQL

MySQL has a handy feature, that allows you to turn an INSERT into an UPDATE if a unique or primary key duplication is detected:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

A common usage pattern for this is “lazy initialization“ of a row in a database, which is exactly what my team was using it for yesterday to solve a problem in the backend for version 2.0 of the MySQL Enterprise Monitor. However, we ran into an issue where Hibernate would throw an exception complaining that when the INSERT was turned into an UPDATE, it couldn‘t retrieve the generated primary key value (we are using auto increments on this particular table, as it‘s not a high insertion-rate table).

To understand why this happens, you have to know a little bit about how Statement.getGeneratedKeys() works with MySQL‘s JDBC driver. When an auto increment value is generated by MySQL, that value is returned on the wire as part of the message back to the client that contains the update count, warnings, etc. When an application asks for the generated keys from MySQL‘s JDBC driver, it takes the value returned on the wire and crafts a “synthetic“ result set to represent it.

In the case where “ON DUPLICATE KEY UPDATE“ is in play, it turns out that the value returned on the wire, by default is indeterminate, which we would‘ve learned from reading our own manual more carefully:

“If a table contains an AUTO_INCREMENT column and INSERT … UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful.”

It turns out, the answer to this is to add a little assignment to the UPDATE clause:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

With that little “id=LAST_INSERT_ID(id)” bit added to the INSERT statement, the server now returns the already existing auto increment value back to the client, and Hibernate is happy again.

I just got the rest of the production schedule from the publisher, plus the PDF files for quality control, for our upcoming book. (Now I have to proofreeed the whole book!) This is the first time I’ve seen the entire production schedule. The book is supposed to go to the printer in the first week of June. I don’t know what the on-the-shelf date will be, but I think very shortly after that. The publisher has promised that it’ll physically be on sale at Velocity.

I also took a peek at the PDFs. Without the appendixes, the last page of Chapter 14 (Tools for High Performance) is page 604. The appendixes bring it to 660 pages. That’s real material, not including tables of contents and indexes. So my estimate (620) was not too far off.

660 pages is not bad, considering that the contract was for 384 pages.

Another note: the marketing materials for the book emphasize that it covers MySQL 5.1. While this is true, I want to point out that we took a real-life approach: we write about what we’ve seen in the real world, and 5.1 is not as widely deployed in the real world. However, the book’s real value, as far as version-specific content goes, is its tremendous depth and breadth in MySQL 4.1 and 5.0. These have been “out there” for a long time, and among the four of us we’ve seen about every conceivable scenario with it. So you’ll get a lot of insight about current, production-ready, widely-used versions. Let the other guys speculate — we just report the facts. It’s not like there’s any shortage of things to say about 5.0, right?

I’m going to be at beCamp 2008, the followup to the first beCamp, which I sadly missed.

beCamp is a BarCamp un-conference. Tonight was about meeting, greeting, and throwing ideas at the wall to see which ones stick. Literally. We stuck pieces of paper on the wall with our ideas — things we can either talk about or want to hear about — and then scratched our votes on them to see which are popular.

I live and breathe MySQL for a decent part of the day, so I hesitated, but then stuck “MySQL Performance” on the wall. It got quite a few votes, so I assume will be giving a talk on MySQL performance basics at some point during the conference. (The exact schedule is probably being determined right now, in my absence, but I’m so tired right now that I’ll just take my chances on it not being at 8:00 AM tomorrow.) [edit: I just checked the website and there won’t be anything before 9:00, and the schedule is determined tomorrow. I did say I’m tired, right?]

See you there!

PS: if you want to meet some of my colleagues from my former employer, the Rimm-Kaufman Group, they’ll be there too, wearing the “We’re Hiring” t-shirts. They’re hiring, by the way.

At the 2008 MySQL Conference and Expo, Ronald Bradford delivered "The Top 20 Design Tips for Enterprise Data Architects". See the slides on the Forge at http://forge.mysql.com/wiki/MySQLConf2008ThursdayNotes#Top_20_DB_Design_Tips_Every_Architect_Needs_to_Know

At the 2008 MySQL User Conference and Expo, Baron Schwartz spoke on "The MySQL Query Cache". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008WednesdayNotes#The_MySQL_Query_Cache

At the 2008 MySQL Conference and Expo, there was a panel discussion on "Addressing Challenges of Data Warehousing - a Panel Discussion" including:

Robin Schumacher (Sun/MySQL) (moderator)

Brian Miezejewski (MySQL), Charles Hooper (Pro Relational Systems), Paul Whittington (NitroSecurity, Inc.), Raj Cherabuddi (Kickfire), Victoria Eastwood (InfoBright Inc.)

At the 2008 MySQL User Conference and Expo, Rick Falkvinge of the Swedish Pirate Party delivered a keynote on "Copyright Regime vs. Civil Liberties".

At the 2008 MySQL Users Conference and Expo, Brian Aker (MySQL) and Allan Kasindorf (SixApart) gave a presentation on Memcached and MySQL. Download the slides and see blog posts others have written about the tutorial from the Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008MondayNotes#Memcached_and_MySQL.

If you'd like to download the WMV video file in parts, here's a link to:
Part 1, 112.09 Mb

read more

This is a continuation post on InnoDB plugin row format performance with few more test scenarios to cover based on the feedback and questions that I got in my Inbox.

This time the initial table looks like this

CREATE TABLE `sbtest` (
  `id` int(10) unsigned NOT NULL,
  `k` int(10) unsigned NOT NULL DEFAULT ‘0′,
  `c` char(120) NOT NULL DEFAULT ”,
  `pad` char(60) NOT NULL DEFAULT ”,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

This time, the table does not have a secondary index in the create time, instead it will be added after the table is populated with the data to test the performance of the new faster index support from the plugin. I also added a new scenario to test 2K compression as we noticed 4K compression seems to be a bottleneck in the earlier case.

Same test environment is chosen as of the first one, RHEL-4 64-Bit having MySQL-5.1.24 with InnoDB plugin 1.0 statically linked.

InnoDB configuration variables:

   1: mysql> show variables like ‘%innodb%’;

   2: +———————————+————————–+

   3: | Variable_name                   | Value                    |

   4: +———————————+————————–+

   5: | have_innodb                     | YES                      |

   6: | innodb_adaptive_hash_index      | ON                       |

   7: | innodb_additional_mem_pool_size | 104857600                |

   8: | innodb_autoextend_increment     | 8                        |

   9: | innodb_autoinc_lock_mode        | 1                        |

  10: | innodb_buffer_pool_size         | 6442450944               |

  11: | innodb_checksums                | ON                       |

  12: | innodb_commit_concurrency       | 0                        |

  13: | innodb_concurrency_tickets      | 500                      |

  14: | innodb_data_file_path           | ibdata1:256M:autoextend  |

  15: | innodb_data_home_dir            | /mysql/ibdata |

  16: | innodb_doublewrite              | ON                       |

  17: | innodb_fast_shutdown            | 1                        |

  18: | innodb_file_format              | Barracuda                |

  19: | innodb_file_io_threads          | 4                        |

  20: | innodb_file_per_table           | ON                       |

  21: | innodb_flush_log_at_trx_commit  | 1                        |

  22: | innodb_flush_method             | O_DIRECT                 |

  23: | innodb_force_recovery           | 0                        |

  24: | innodb_lock_wait_timeout        | 50                       |

  25: | innodb_locks_unsafe_for_binlog  | OFF                      |

  26: | innodb_log_buffer_size          | 8388608                  |

  27: | innodb_log_file_size            | 268435456                |

  28: | innodb_log_files_in_group       | 2                        |

  29: | innodb_log_group_home_dir       | /mysql/iblog  |

  30: | innodb_max_dirty_pages_pct      | 90                       |

  31: | innodb_max_purge_lag            | 0                        |

  32: | innodb_mirrored_log_groups      | 1                        |

  33: | innodb_open_files               | 300                      |

  34: | innodb_replication_delay        | 0                        |

  35: | innodb_rollback_on_timeout      | OFF                      |

  36: | innodb_strict_mode              | OFF                      |

  37: | innodb_support_xa               | ON                       |

  38: | innodb_sync_spin_loops          | 20                       |

  39: | innodb_table_locks              | ON                       |

  40: | innodb_thread_concurrency       | 32                       |

  41: | innodb_thread_sleep_delay       | 10000                    |

  42: +———————————+————————–+

  43: 37 rows in set (0.00 sec)

Normally I disable the following InnoDB configuration options when running any benchmark tests; but this time enabled all of them (including in the first case)…

innodb_checksums
innodb_doublewrite
innodb_support_xa

Table Load:

Load time from a dump of SQL script having 10M rows (not batched)

Secondary Index Creation:

Secondary Index create time after the table has been populated. The index added using

CREATE INDEX k ON sbtest(k)

File Sizes:

Here is the size of the .ibd file after each data load

Testing:

I used the sysbench to run the tests by varying the threads to run mixed read/write on a  pre-populated table by creating the table with different ROW_FORMATS along with KEY_BLOCK_SIZE. The server is taken down by cleaning its logs and data directories completely and a fresh server is started back to re-run a different row-format .

Here is the sysbench options:

--test=oltp --db-driver=mysql --oltp-table-size=10000000
–oltp-skip-trx=on –oltp-auto-inc=off –mysql-table-engine=innodb
–mysql-user=root –mysql-ignore-duplicates=on –max-requests=100000
–init-rng=on –oltp-test-mode=complex –oltp-dist-type=uniform
–mysql-engine-trx=yes –oltp-read-only=off
–num-threads=XXX run

Some variables are non-relevant to test, but I use the same scripts to run lot of other cases, –mysql-ignore-duplicates is not a standard sysbench option, its a patch that I created to make sure the test does not bail on duplicate key errors due to poor random number generator.

The test typically executes 950000 queries in each iteration (700K read queries and 250K write queries) with max of 50000 transactions in this test and 100K transactions in the part-I case. Here is the typical output from sysbench:

LTP test statistics:
    queries performed:
        read:                            700000
        write:                           250000
        other:                           0
        total:                           950000
    transactions:                        50000  (206.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 950000 (3914.01 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          242.7179s
    total number of events:              50000
    total time taken by event execution: 123984.2189
    per-request statistics:
         min:                            0.2133s
         avg:                            2.4797s
         max:                            15.6503s
         approx.  95 percentile:         4.0491s

Performance:

Here is the performance of various row formats with threads ranging from 1 to 512 for 6G buffer pool size for both concurrent reads and writes.

 Observations:

• The load time is almost same for both Compact and Dynamic row formats. In this table and data scenario, both perform same amount of transactions (as you can see from the graph, they overlap)
• The load/insert time increases based on the compression ratio from 8K, 4K, 2K, 1K etc
• When the whole data set can fit in memory; then compression will relatively slowdown the transaction rate
• When the whole data set can not fit in memory, then compression on the table seems to be a right choice (8K in this case)
• Creation of secondary index still takes lot of time when the compression is enabled. The 8K compression is much better and bit close to Compact/Dynamic; but 4K/2K seems to take ~6 times more time than 8K
• The best compression in terms of performance is 8K; but it may vary based on the table structure and data and its distribution.
• Part-I observations can be found from here
  

Looks like there is something wrong in terms of performance of 4K/2K compression when compared with 8K when the data set can fit everything in memory (as we have 6G buffer pool). I will dig further and see if we can find a solution for the actual problem or file a bug report..more later.

Convert from one row format to another:

Alter table seems to be a right choice to change from one row format to another one or to change the compression ratio and it is efficient. For example, here is a case where the table is converted from compressed to compact and compact to Compressed and InnoDB re-creates both .frm and .ibd by using temp files. Even here you can see that 4K takes much longer time than expected.

mysql> alter table sbtest ROW_FORMAT=Compact;
Query OK, 10000000 rows affected, 1 warning (2 min 56.48 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> alter table sbtest ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;
Query OK, 10000000 rows affected (4 min 11.56 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> alter table sbtest ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4;
Query OK, 10000000 rows affected (20 min 6.36 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> alter table sbtest ROW_FORMAT=Compact;
Query OK, 10000000 rows affected, 1 warning (2 min 56.61 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> show warnings;
+———+——+—————————————————————–+
| Level   | Code | Message                                                         |
+———+——+—————————————————————–+
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. |
+———+——+—————————————————————–+
1 row in set (0.00 sec)

mysql> alter table sbtest ROW_FORMAT=Dynamic;
Query OK, 10000000 rows affected, 1 warning (2 min 42.12 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> show warnings;
+———+——+—————————————————————–+
| Level   | Code | Message                                                         |
+———+——+—————————————————————–+
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4 unless ROW_FORMAT=COMPRESSED. |
+———+——+—————————————————————–+
1 row in set (0.00 sec)

mysql> alter table sbtest ROW_FORMAT=Compressed KEY_BLOCK_SIZE=2;
Query OK, 10000000 rows affected (20 min 53.76 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

Nice to have:

It will be nice if InnoDB team can extend the SHOW TABLE STATUS to show the compression details. Right now the Information schema.cmp will only lists general compression statistics but not on per table basis.

Also, it will be added advantage if they can support the following two config variables:

innodb_default_row_format (default compact or dynamic) and innodb_default_key_block_size (optionally, and make sure it does not raise a warning on default row_format)

Why use InnoDB?

InnoDB is commonly viewed as anything but performant, especially when compared to MyISAM. Many actually call it slow. This view is mostly supported by old facts and mis-information. In reality, you would be very hard-pressed to find a current, production-quality MySQL Database Engine with the CPU efficiency of InnoDB. It has its performance "quirks" and there are definitely workloads for which it is not optimal, but for standard OLTP (Online Transaction Processing) loads, it is tough to find a better, safer fit.

read more

10,000 Tables Can?t Be Wrong: Designing a Highly Scalable MySQL Architecture for Write-intensive Applications by Richard Chart

Chose MySQL for performance and stability, and less important but still there, experience and support. Support is becoming increasingly more and more important.

Starting point: 1 appliance supporting 200 devices
Problem/Goal: Extensible architecture with deep host and app monitoring, over 1000 devices with 100 mgmt points each
Distributed collection over a WAN, with latency and security concerns
Current reality: several times the scale of the original goal
Commercial embedded product, so they actually pay for the embedded MySQL server

Future: The fundamentals are sound: next generation of the product moves up another order of magnitude

Data Characteristics
>90% writes
ACID not important
Resilient to loss, because gaps in data do not invalidate the rest of the data
Data elements by themselves are valuable, but much more so when relationships are added.

Chose MyISAM because: (more…)