Hello,
Well, for my project at work, I've done some small benchmarking of different table types for relatively small sizes (some tens or hundreds of thousands rows - small enough so that key buffer size and other memory factors should not limit performance) and lots of very simple operations. I have the following observation from it : - INSERTs : HEAP tables are incredibly faster than everything else. Then come MYISAM tables (10 to 50 times slower than HEAP), and then the transactional tables (BDB and InnoDB). However the INSERTs were not contained in a single transaction, so this may improve performance. - SELECTs using indexes : HEAP are the best again, but with quite erratic performance. On some occasions they were extremely fast (100 times faster than other tables), and with other record sets they were only twice faster than other tables (which had, on their side, very stable results) - which means even SELECT COUNT(*) FROM my_table is slower than an INSERT ! I find this quite disturbing, and wonder about the reliability of HEAP tables. I wonder if anyone has had performance "problems" with HEAP tables (erratic performance given that at some time they can be really light-speed, and at others just *a bit* faster than disk-based tables) ? Another problem for HEAP tables is the slowness of DELETE and even TRUNCATE TABLE (you'd better DROP then re-CREATE them). Also, for disk-based tables, setting the 'noatime' flag was a real benefit for lots of very small queries (some tens of percent faster). Endly, it seems that restarting MySQL has some positive impact on SELECTs (strangely enough, because one could think that it would have to reload the indexes and thus spend some more time), expect on HEAP tables (!) ; INSERTs weren't affected though. Are there explanations for some of these facts ?. For HEAP tables, I was wondering if the client side was the bottleneck, but 'top' showed me that MySQL really took most CPU time. This was done with MySQL 3.23.43 source distribution, on a bi-processor Intel Redhat box. The machine is at the same time a production machine but with small loads, and each test was run several times. I can send the test files (4 small PHP scripts ;-)) to the MySQL developpers if they find it interesting. Regards Antoine. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
