On Fri, Feb 15, 2002 at 04:00:11PM +0100, Antoine wrote: > > 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.
As you'd expect. > Then come MYISAM tables (10 to 50 times slower than HEAP), and then > the transactional tables (BDB and InnoDB). Makes sense. You can probably get InnoDB closer to MyISAM speeds by tweaking the parameters just right. > However the INSERTs were not contained in a single transaction, so > this may improve performance. Indeed. > 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. Your results could have a lot to do with the fact that HEAP tables used hashed indexes rather than the B-Tree indexes of MyISAM. The set of queries for which MySQL can use a hashed index is far smaller than with B-Tree indexes. For example, with a MyISAM table, MySQL can use indexes for a range query (SELECT ... WHERE foo BETWEEN x AND y). But with HEAP tables, it'll result in a table scan. > Also, for disk-based tables, setting the 'noatime' flag was a real > benefit for lots of very small queries (some tens of percent > faster). Yep. > 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. That's rather odd. > This was done with MySQL 3.23.43 source distribution, on a > bi-processor Intel Redhat box. Which kernel version? Did the machine swap during your tests? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 10 days, processed 328,256,260 queries (377/sec. avg) --------------------------------------------------------------------- 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
