I've seen my posts on Ontime Air traffic and Star Schema
Benchmark got a lot of interest
(links:
I appreciate opportunity Jos van Dongen from Tholis Consulting gave me. He granted me access to servers with 8 attached Intel X-25M 80GB MLC cards. The cards attached to 2 Adaptec 5805 raid controllers, with 4 cards per controller.
The cost of setup is 8 x 260$ (X-25M) + 2x500$ (Adaptec 5805) =
~3000$.
Available space varies in depends on raid setup from 300GB to
600GB.
The logical comparison is to compare results with FusionIO 320GB MLC card, so I will copy results from FusionIO 320GB MLC benchmarks.
For benchmarks I used sysbench fileio
benchmark.
All raw results are available on Percona benchmarks wiki, there I will highlight
most interesting …
To continue fun with FusionIO cards, I wanted to check how MySQL
/ InnoDB performs here. For benchmark I took MySQL 5.1.42 with
built-in InnoDB, InnoDB-plugin 1.0.6, and XtraDB 1.0.6-9 ( InnoDB
with Percona patches).
As benchmark engine I used tpcc-mysql with 1000 warehouses (
which gives around 90GB of data + indexes) on my workhourse Dell
PowerEdge R900 ( details about box ).
On storage configuration: FusionIO 160GB SLC and 320GB MLC cards are configured in software RAID0 to store InnoDB datafiles. For InnoDB logs and system tablespace I used partition on regular RAID10 with regular hard drives, here I followed Yoshinori Matsunobu's recommendations http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html …
[Read more]After my previous benchmarks of FusionIO 160GB SLC card, FusionIO team sent me for the tests another card, FusionIO 320GB MLC. I should say I really appreciate an opportunity to play with this card and with combination of two cards.
This card is also not cheap, the price I can find on dell.com is $6,829.99, which is almost the
same as for 160GB card, but with doubled space,
and which gives me 21$/GB for 100% formatted card, and 29$/GB for
card with 75% of space allocated. Effect of 25% reserved space
you will see in the results.
The full numbers are available on our …
[Read more]In my previous rounds with DataWarehouse oriented engines I used single table without joins, and with small (as for DW) datasize (see http://www.mysqlperformanceblog.com/2009/10/02/analyzing-air-traffic-performance-with-infobright-and-monetdb/, http://www.mysqlperformanceblog.com/2009/10/26/air-traffic-queries-in-luciddb/, http://www.mysqlperformanceblog.com/2009/11/02/air-traffic-queries-in-infinidb-early-alpha/). Addressing these issues, I took Star Schema Benchmark, which is TPC-H modification, and tried run queries against InfoBright, InfiniDB, LucidDB and MonetDB. I did not …
[Read more]Peter took a look at Redis some time ago; and now, with the impending 1.2 release and a slew of new features, I thought it time to look again.
One of the more interesting features in 1.2 is the ability to operate in "append-only file persistence mode", meaning Redis has graduated from a semi-persistent to a fully-persistent system! Using the redis-benchmark script included, I ran the following command
./redis-benchmark
in five modes:
1 - In-Memory
I set "save 900000000 900000000" so nothing would be written to
disk during the tests.
2 - Semi-Persistent
I set "save 1 1" so that changes would be flushed to disk every
second (assuming there was at least one change the previous
second).
…
I posted about FusionIO couple times RAID vs SSD vs FusionIO and Testing FusionIO: strict_sync is too strict…. The problem was that FusionIO did not provide durability or results were too bad in strict mode, so I lost interest FusionIO for couple month. But I should express respect to FusionIO team, they did not ignore problem, and recently I was told that in last drivers FusionIO provides durability even without strict_mode (see http://www.mysqlperformanceblog.com/2009/06/15/testing-fusionio-strict_sync-is-too-strict/#comment-676717). While I do not fully understand how it works internally …
[Read more]I had an interesting case recently. The customer dealing with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements. The performance was extremely slow. I turned out it is caused by hundreds of daily partitions created for this table. What is the most interesting (and surprising) not every statement is affected equally as you can see from the benchmarks above:
I got the following test table created:
PLAIN TEXT SQL:
I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here.
The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable ... but I noticed one troubling thing: ~2GB of uncheckpointed data.
mysql> SHOW INNODB STATUS\G .... Database pages 2318457 Old database pages 855816 Modified db pages 457902 Log flushed up to 10026890404067 Last checkpoint at 10024612103454 ....
We enabled …
[Read more]In my previous post I looked into how large table_cache actually can decrease performance. The "miss" path is getting more expensive very quickly as table cache growths so if you're going to have high miss ratio anyway you're better off with small table cache.
What I have not checked though is how does table_cache (or table_open_cache in newer version) size affects the hit path.
I started with the same test as last time - created 100000 tables and read all of them at once to make sure all table cache entries are populated. When I tried repeatedly reading 1000 empty tables with table_cache of 20000 and 2000. With Table Cache of 2000 I got about 16400 selects/sec with Table Cache of 20000 13500 selects/sec. So there is some slow down in hit path as well though it is not as large as with miss …
[Read more]