I actually posted something yesterday, but it got bounced (HTML with long lines?). I'm starting to wonder whether the problem is my configuration. I just noticed yesterday that the 4.1 my sysadmin set up is running bin/safe_mysqld. Unfortunately it will take me some time to get back to where I was and rerun my tests not in safe mode.
Here again is the post in plain text: Running ANALYZE appears to help, narrowing the gap between what I'm seeing with 3.23.58 (3.23.58 is still more than 20% faster than 4.1.3beta on my queries) Here's some information about the tables, and EXPLAIN for a typical slow query: mysql> describe All_tid; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | term | varchar(100) | | PRI | | | | termID | int(11) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> describe All_tidpos; +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | termID | int(11) | | PRI | 0 | | | recID | int(11) | | PRI | 0 | | | pos | int(11) | | PRI | 0 | | | field | char(4) | | | | | +--------+---------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> explain select count(distinct tp1.recID) from All_tidpos tp1, All_tid tid1, All_tidpos tp2, All_tid tid2, All_tidpos tp3, All_tid tid3, All_tidpos tp4, All_tid tid4, All_tidpos tp5, All_tid tid5, All_tidpos tp6, All_tid tid6, All_tidpos tp7, All_tid tid7 where tid1.term='new' and tid1.termID=tp1.termID and tid2.term='york' and tid2.termID=tp2.termID and tid3.term='state' and tid3.termID=tp3.termID and tid4.term='commission' and tid4.termID=tp4.termID and tid5.term='education' and tid5.termID=tp5.termID and tid6.term='reform' and tid6.termID=tp6.termID and tid7.term='sound' and tid7.termID=tp7.termID and tp1.recID=tp2.recID and tp1.recID=tp3.recID and tp1.recID=tp4.recID and tp1.recID=tp5.recID and tp1.recID=tp6.recID and tp1.recID=tp7.recID; +----+-------------+-------+-------+---------------+---------+---------+---- -----------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+---- -----------------------+------+-------------+ | 1 | SIMPLE | tid1 | const | PRIMARY | PRIMARY | 100 | const | 1 | | | 1 | SIMPLE | tid2 | const | PRIMARY | PRIMARY | 100 | const | 1 | | | 1 | SIMPLE | tid3 | const | PRIMARY | PRIMARY | 100 | const | 1 | | | 1 | SIMPLE | tid4 | const | PRIMARY | PRIMARY | 100 | const | 1 | | | 1 | SIMPLE | tid5 | const | PRIMARY | PRIMARY | 100 | const | 1 | | | 1 | SIMPLE | tid6 | const | PRIMARY | PRIMARY | 100 | const | 1 | | | 1 | SIMPLE | tid7 | const | PRIMARY | PRIMARY | 100 | const | 1 | | | 1 | SIMPLE | tp1 | ref | PRIMARY | PRIMARY | 4 | const | 23 | Using index | | 1 | SIMPLE | tp2 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID | 1 | Using index | | 1 | SIMPLE | tp3 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID | 1 | Using index | | 1 | SIMPLE | tp4 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID | 1 | Using index | | 1 | SIMPLE | tp5 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID | 1 | Using index | | 1 | SIMPLE | tp6 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID | 1 | Using index | | 1 | SIMPLE | tp7 | ref | PRIMARY | PRIMARY | 8 | const,worldcat0.tp1.recID | 1 | Using index | +----+-------------+-------+-------+---------------+---------+---------+---- -----------------------+------+-------------+ 14 rows in set (0.11 sec) As possibly you can tell, this is full-text retrieval layered on top of straight SQL. The query is long but simple: 'new and york and state and commission and education and reform and sound', which should run fast since the result is empty, but each of the terms will result in many rows in the All_tidpos table. One thing that 4.1 is better at is speeding up repeated queries, so for testing we're forced to run thousands of queries through the system to avoid speed-ups across runs. --Th --Th -----Original Message----- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 10:17 PM To: [EMAIL PROTECTED] Subject: RE: 4.1 performance Sergei, Thom.. I am interested in seeing this thread followed through. As developers at my work have experienced similar performance issues between 3.23.x and 4. Our database is also of similar size and a full optimize has been run. Regards, Lachlan -----Original Message----- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: Tuesday, 13 July 2004 8:11 PM To: Hickey,Thom Cc: [EMAIL PROTECTED] Subject: Re: 4.1 performance Hi! On Jul 12, Hickey,Thom wrote: > I've been comparing the performance of 4.1 with the MySQL 3.23.58 that came > with our Rocks cluster software. > > I'm finding that 4.1 is running approximately 1/3 slower (e.g. a 0.075 > second query goes to 0.100 seconds) than 3.23.58. Size of buffers, etc. > seems to have little effect. The database is fairly large with about 3 gig > spread over a half-dozen tables. The largest table has 62 million rows. What kind of queires do you run ? What does EXPLAIN show ? Are all tables ANALYZE'd ? 4.1 can use more complex query transformations and join methods that should bring more performance. Of course if the statistical data are incorrect, the optimizer cannot jugde what is "more" and what it "less", so new features may expose problems that were hidden in the old version. Regards, Sergei -- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
