Innodb too slow
Hi, why innodb queries work MUCH slower (100 times) than if the table was of myisam type? It's mysql 4.0.3 on FreeBSD 4.5 server. The innodb monitor outputs many messages like the following, why are they there and what do they mean? Purge done for trx's n:o 0 782 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 3811, ACTIVE 0 sec, OS thread id 10250 fetching rows MySQL thread id 2, query id 3044 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3812, sees 0 3807 ---TRANSACTION 0 3810, ACTIVE 1 sec, OS thread id 12300 fetching rows MySQL thread id 4, query id 3043 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3809, ACTIVE 1 sec, OS thread id 13325 fetching rows MySQL thread id 5, query id 3042 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3808, ACTIVE 1 sec, OS thread id 14350 fetching rows MySQL thread id 6, query id 3041 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3809, sees 0 3804 ---TRANSACTION 0 3807, ACTIVE 1 sec, OS thread id 11275 fetching rows MySQL thread id 3, query id 3040 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3808, sees 0 3803 Any help will be appreciated, thanks! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
Re: Innodb too slow
Hi Alexander, I believe that the the 'count()' function works differently under INNODB type tables. With MyISAM count(*) is stored in a 'table status' area, but INNODB must scan the tables and count the rows (very slow). All other types of queries should perform much better for you, but 'count(*)' is not impelemented the same way. Hope this helps, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 3:56 AM Subject: Innodb too slow Hi, why innodb queries work MUCH slower (100 times) than if the table was of myisam type? It's mysql 4.0.3 on FreeBSD 4.5 server. The innodb monitor outputs many messages like the following, why are they there and what do they mean? Purge done for trx's n:o 0 782 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 3811, ACTIVE 0 sec, OS thread id 10250 fetching rows MySQL thread id 2, query id 3044 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3812, sees 0 3807 ---TRANSACTION 0 3810, ACTIVE 1 sec, OS thread id 12300 fetching rows MySQL thread id 4, query id 3043 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3809, ACTIVE 1 sec, OS thread id 13325 fetching rows MySQL thread id 5, query id 3042 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3808, ACTIVE 1 sec, OS thread id 14350 fetching rows MySQL thread id 6, query id 3041 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3809, sees 0 3804 ---TRANSACTION 0 3807, ACTIVE 1 sec, OS thread id 11275 fetching rows MySQL thread id 3, query id 3040 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3808, sees 0 3803 Any help will be appreciated, thanks! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 - 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
Re: Innodb too slow
Thank you Ken for the answer, but here the cause was different from what you say. count() works fast enough for innodb as well, expecially if the query cache feature is turned on. In my case, I used a mysql 4.0.3 compiled from ports, which had a debug option turned on, and it appeared to be critical for performance which dropped more then by 100 times because of it! As I was going further, 4.0.3 version went out of control after about 10 minutes of being uptime - it suddenly just silently refused showing all configured databases :( So I'm now installing 4.0.2, hope it'll be behaving better. Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 3 Sep 2002, Ken Menzel wrote: Date: Tue, 3 Sep 2002 10:34:06 -0400 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Innodb too slow Hi Alexander, I believe that the the 'count()' function works differently under INNODB type tables. With MyISAM count(*) is stored in a 'table status' area, but INNODB must scan the tables and count the rows (very slow). All other types of queries should perform much better for you, but 'count(*)' is not impelemented the same way. Hope this helps, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 3:56 AM Subject: Innodb too slow Hi, why innodb queries work MUCH slower (100 times) than if the table was of myisam type? It's mysql 4.0.3 on FreeBSD 4.5 server. The innodb monitor outputs many messages like the following, why are they there and what do they mean? Purge done for trx's n:o 0 782 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 3811, ACTIVE 0 sec, OS thread id 10250 fetching rows MySQL thread id 2, query id 3044 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3812, sees 0 3807 ---TRANSACTION 0 3810, ACTIVE 1 sec, OS thread id 12300 fetching rows MySQL thread id 4, query id 3043 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3809, ACTIVE 1 sec, OS thread id 13325 fetching rows MySQL thread id 5, query id 3042 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3808, ACTIVE 1 sec, OS thread id 14350 fetching rows MySQL thread id 6, query id 3041 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3809, sees 0 3804 ---TRANSACTION 0 3807, ACTIVE 1 sec, OS thread id 11275 fetching rows MySQL thread id 3, query id 3040 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3808, sees 0 3803 Any help will be appreciated, thanks! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 - 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
