| Bug #39437 | Maria 50 times slower than MyISAM on table insertions | ||
|---|---|---|---|
| Submitted: | 14 Sep 2008 10:31 | Modified: | 16 Dec 2008 13:38 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Maria storage engine | Severity: | S5 (Performance) |
| Version: | 6.0.7 | OS: | Solaris (10) |
| Assigned to: | Christoffer Hall | CPU Architecture: | Any |
[14 Sep 2008 11:08]
Guilhem Bichot
Ideas: - could be just logging, we should retry with transactional=0 in the CREATE TABLE - "closing tables" state: could be the my_sync() in maria_close().
[15 Sep 2008 8:54]
Sveta Smirnova
Thank you for the report. Verified as described: $time ENG=Myisam perl mysql-test-run.pl --record bug39437 Logging: mysql-test-run.pl --record bug39437 ... Spent 3.057 of 11 seconds executing testcases real 0m10.948s user 0m3.344s sys 0m0.748s $time ENG=Maria perl mysql-test-run.pl --record bug39437 Logging: mysql-test-run.pl --record bug39437 ... Spent 115.258 of 123 seconds executing testcases real 2m2.743s user 0m7.108s sys 0m1.100s
[15 Sep 2008 10:20]
Guilhem Bichot
So, all is normal. Maria is crash-safe: a crash should not destroy it, a finished statement should never be lost on crash, so it has to have a log and sync this log to disk at the end of every statement, precisely in Maria the COMMIT is table unlocking. In the test as it is presented, tables are unlocked at end of each INSERT, so one log fsync() at end of each INSERT: 100*100 INSERTs: 10000 syncs; disks support ~150 syncs max per second so test cannot take less than one minute. My results: innodb (with full durability like Maria has, i.e. innodb flush_log_at_trx_commit=1) ====== 2m2.712s same test with START TRANS/COMMIT around the CALL (to have only one log sync and not 10000): 0m8.416s maria ===== 2m6.978s same test with LOCK/UNLOCK TABLES around the CALL (to have only one log sync and not 10000): 0m7.025s same test with TRANSACTIONAL=0 (no logging, MyISAM-like): 0m8.926s myisam ====== 0m9.236s same test with LOCK/UNLOCK around the CALL: 0m7.735s You can see that Maria is fine: - at same durability as InnoDB, same time - at same durability as MyISAM, same time
[15 Sep 2008 10:42]
Philip Stoev
Yes this is all true, however I filed this as a performance regression between MyISAM and Maria. People who start using Maria in place of MyISAM will naturally expect MyISAM-like performance and will be suprised to have entered the Innodb world where you have to measure your fsync()s per second. I would say that this needs to be documented -- otherwise people would assume that crash safety comes at no performance penalty (as I naively did). At the same time, both Innodb and Falcon are about 20% faster than Maria. Maybe this is due to batched transaction commits that are all flushed with a single fsync()?
[15 Sep 2008 11:10]
Philip Stoev
Here are the numbers from tor03, which has the most problematic numbers. It is a SunOS tor03 5.10 Generic_127127-11 sun4v sparc SUNW,SPARC-Enterprise-T5120, which has an UltraSparc T2 CPU. maria default: 152.956 maria transactional=0: 7.400 innodb default: 2.570 myisam default: 2.574
[25 Sep 2008 9:34]
Christoffer Hall
I have experimented a little bit with this and with Guilhem tracked down what is causingthe relative slowness. Maria with TRANSACTION = 0 runs with 8k pages and the new page format. I've rerun pstoevs test and played with the ROW_FORMAT, PAGE_CHECKSUM and TRANSACTIONAL options when creating the Maria table. After that I tryed starting mariawith --maria-block-size=1024 and thereby keeping it almost identical to MyISAM. Here are the results (from laptop): With LOCK/UNLOCK: MyISAM (with lock/unlock table before proc call) time: ~17 sec Without LOCK/UNLOCK: MyISAM time: ~28 sec. Maria TRANSACTIONAL = 0 ROW_FORMAT = DYNAMIC PAGE_CHECKSUM = 0 time: ~31 sec Maria TRANSACTIONAL = 0 ROW_FORMAT = DYNAMIC PAGE_CHECKSUM = 1 time: ~37 sec Maria TRANSACTIONAL = 0 ROW_FORMAT = PAGE PAGE_CHECKSUM = 0 time: ~43 sec Maria TRANSACTIONAL = 0 ROW_FORMAT = PAGE PAGE_CHECKSUM = 1 time: ~60 sec Maria TRANSACTIONAL = 1 time: ~3 min 59.85 sec Maria TRANSACTIONAL = 1 (with lock/unlock table before proc call) time: ~27 sec Now with --maria-block-size=1024 With LOCK/UNLOCK: Maria TRANSACTIONAL = 0 ROW_FORMAT = DYNAMIC PAGE_CHECKSUM = 0 time: ~17 sec Without LOCK/UNLOCK: Maria TRANSACTIONAL = 0 ROW_FORMAT = DYNAMIC PAGE_CHECKSUM = 0 time: ~25 sec It seems to be the 8k pages for data and index that is the root of the slowdown. (this time added to the right bug)
[15 Dec 2008 12:28]
Christoffer Hall
I've been working with this workload on sun03.norway.sun.com. I don't think it can be classified as a bug. It is more a case of needing to document two important differences between MyISAM and Maria. The default page size and being crash safe. The numers fall out like this for me: time ENG="myisam" perl mysql-test-run.pl --record bug39437 Runtime 10.1 seconds time ENG="maria" perl mysql-test-run.pl --record bug39437 Runtime 41.5 seconds time ENG="maria" perl mysql-test-run.pl --mysqld=--maria-block-size=1k --record bug39437 Runtime 38.1 seconds time ENG="maria transactional=0" perl mysql-test-run.pl --record bug39437 Runtime 13.8 seconds time ENG="maria transactional=0" perl mysql-test-run.pl --mysqld=--maria-block-size=1k --record bug39437 Runtime 11.0 seconds So an understanding of how maria is different from myisam needs good documentation.
[15 Dec 2008 12:55]
Christoffer Hall
The manual should have a paragraph how to get myisam-like behaviour. In best faq style: How do I get maria to behave like good old myisam? You have to do two things. Make sure maria is setup with a page size of 1k (maria-block-size=1k). Make sure your maria tables are created with a couple of options. The most important ones are TRANSACTIONAL=0, PAGE_CHECKSUM=0 and ROW_FORMAT=(FIXED|DYNAMIC).
[16 Dec 2008 13:37]
Christoffer Hall
After checking the innodb numbers we must conclude that this is not a bug at all. In fact maria beats innodb handsomely. The problem was that the testcase did not include innodb (--source include/have_innodb.inc). So basically the comparison was for myisam blasting data into memory and maria doing a ton of commits and being stuck in writing to log. Changing the testcase to include innodb and rerunning (og a slightly newer tree). These are the results I get on tor03.norway.sun.com. Maria: Spent 28.687 of 36 seconds executing testcases InnoDB: Spent 39.899 of 47 seconds executing testcases Maybe it should be filed as a performance bug against InnoDB :)
Description: When running a stored procedure that inserts values into a Maria table, inserts progress extremely slow (about 50 inserts per second on a 64-core Sun box). SHOW PROCESSLIST shows the thread is constantly in a "closing tables" state. Here are some numbers: for the Sun 64-core box: myisam: 2881 msec = 3 sec maria: 165124 ms = 165 sec for a 1.5Gz virtualized intel core duo: myisam: 1951 ms = 1.9 sec. maria: 11187 ms = 11 sec. How to repeat: Create this test case: eval CREATE TABLE account (id INT, bid INT, balance DECIMAL(10,2), filler CHAR(255), PRIMARY KEY(id))ENGINE=$ENG; delimiter |; CREATE PROCEDURE load() BEGIN DECLARE acct INT DEFAULT 10000; DECLARE brch INT DEFAULT 100; DECLARE tell INT DEFAULT 10000; DECLARE tmp INT DEFAULT 100; WHILE brch > 0 DO SET tmp = 100; WHILE tmp > 0 DO INSERT INTO account VALUES (acct, brch, 0.0, "FRESH ACCOUNT"); SET acct = acct - 1; SET tmp = tmp - 1; END WHILE; SET brch = brch - 1; END WHILE; END| delimiter ;| CALL load(); And then call it as follows: $ time ENG=Myisam perl mysql-test-run.pl --record testname $ time ENG=Maria perl mysql-test-run.pl --record testname