So you’re running the benchmark/stress test – how do you tell if MySQL server is really loaded ? This looks like the trivial question but in fact, especially when workload consists of simple queries I see the load generation and network really putting a lot less load on MySQL than expected. For example you may [...]
Star Schema Bechmark: InfoBright, InfiniDB and LucidDB
January 7, 2010 By 48 Comments
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 get results for MonetDB, will [...]
InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
One problem made me puzzled for couple hours, but it was really interesting to figure out what’s going on. So let me introduce problem at first. The table is
1 2 3 4 5 6 7 8 | CREATE TABLE `c` ( `tracker_id` int(10) unsigned NOT NULL, `username` char(20) character set latin1 collate latin1_bin NOT NULL, `time_id` date NOT NULL, `block_id` int(10) unsigned default NULL, PRIMARY KEY (`tracker_id`,`username`,`time_id`), KEY `block_id` (`block_id`) ) ENGINE=InnoDB |
Table has 11864696 rows and takes Data_length: 698,351,616 bytes on disk The problem is that after restoring table from mysqldump, the query that scans data [...]
JOIN Performance & Charsets
October 21, 2008 By 2 Comments
We have written before about the importance of using numeric types as keys, but maybe you’ve inherited a schema that you can’t change or have chosen string types as keys for a specific reason. Either way, the character sets used on joined columns can have a significant impact on the performance of your queries. Take [...]
MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
Few days ago I wrote about testing writing to many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables so I’ve got the script and ran tests for MyISAM and Innodb tables on ext3 filesystem. Here is what I found:
Extended EXPLAIN
July 24, 2006 By 9 Comments
One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is [...]
