Richard, are you sure you do not have open transactions in your system?
How do you update the table raw or insert to it? Note that also an ordinary SELECT starts a transaction. The problem could be caused by old versions of rows in table raw. InnoDB cannot purge them if there is a transaction open which might still see the old versions. The table status means that your table is occupying about 183 MB. If you disconnect your application and wait for a while, does the table raw shrink to a realistic size because purge is done in the background? Regards, Heikki At 01:55 PM 11/19/01 -0000, you wrote: >There is definitly something to do with this row count that is causing a >problem. My table uses no blobs. >CREATE TABLE `raw` ( > `cid` int(11) default NULL, > `agent` char(255) default NULL, > `referer` char(255) default NULL, > `addr` char(15) default NULL, > `via` char(255) default NULL, > `forward` char(15) default NULL, > `ctime` datetime default NULL, > `uniq` int(1) default NULL, > KEY `age` (`ctime`) >) TYPE=InnoDB > >again just now the database slowed down to a crawl and a table status shows >| raw | InnoDB | Fixed | 178241 | 1027 | >183222272 | NULL | 4734976 | 0 | NULL | >NULL | NULL | NULL | | >InnoDB free: 3225600 kB | > >that again it seems to think is has 180K rows when there can't be more than >2K-5K rows. I tried changing the table truncate to a delete from which >solved nothing. To give you an example of the slowdown. Here is a query >running properly > > CREATE TEMPORARY TABLE `pagehits_tmp` ( > `cid` int(11) default NULL, > `referer` char(255) default NULL, > `hits` bigint(21) NOT NULL >default '0', > `ctime` datetime default NULL, > index day (ctime,cid,referer) > ) TYPE=innodb > >inserting summary of pagehits into pagehits_tmp - >insert into pagehits_tmp select cid,referer,count(*) as hits, >date_format(ctime,'%Y-%m-%d %H:00:00') as ctime from raw WHERE uniq=1 group >by cid,referer > >create sum of old hits + new hits - >create temporary table pagehits_sum select s1.cid as cid,s1.referer as >val,s1.hits+ifnull(s2.hits,0) as hits, s1.ctime from pagehits_tmp as s1 left >join pagehits_hr as s2 on s1.cid=s2.cid AND s1.referer=s2.val > >replace new sum's into pagehits_hr - >replace into pagehits_hr (cid,val,hits,ctime) select cid,val,hits,ctime from >pagehits_sum >drop temporary tables > >Time taken = 0.858478 secs > >... and here is after it starts slowing down > >CREATE TEMPORARY TABLE `pagehits_tmp` ( > `cid` int(11) default NULL, > `referer` char(255) default NULL, > `hits` bigint(21) NOT NULL >default '0', > `ctime` datetime default NULL, > index day (ctime,cid,referer) > ) TYPE=innodb > >inserting summary of pagehits into pagehits_tmp >insert into pagehits_tmp select cid,referer,count(*) as hits, >date_format(ctime,'%Y-%m-%d %H:00:00') as ctime from raw WHERE uniq=1 group >by cid,referer >create sum of old hits + new hits >create temporary table pagehits_sum select s1.cid as cid,s1.referer as >val,s1.hits+ifnull(s2.hits,0) as hits, s1.ctime from pagehits_tmp as s1 left >join pagehits_hr as s2 on s1.cid=s2.cid AND s1.referer=s2.val >replace new sum's into pagehits_hr >replace into pagehits_hr (cid,val,hits,ctime) select cid,val,hits,ctime from >pagehits_sum >drop temporary tables > >Time taken = 26.724891 secs (0 rows/sec) > > >........... > >Raw contains no more rows than before and pagehits_hr contains at most 20K >rows. > >Any ideas would be really welcome. Maybe I should change the delete from to >a drop and create. > >Rich > > >----- Original Message ----- >From: "Heikki Tuuri" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Monday, November 19, 2001 8:10 AM >Subject: Re: database slow down > > >> Hi! >> >> >This is a followup to my previous messages indicating database slow down. >> >After noticing the queries start to slow down earlier i decided to try >and >> >get some debug info. a show table status had one interesting thing. >> > >> >| raw | InnoDB | Fixed | 169681 | 1030 | >> >174817280 | >> >> Sorry this is a known bug in SHOW TABLE STATUS in InnoDB. It estimates the >> row count too big if you have big BLOBs in your table. >> >> >the 4th column being interesting. It seems to "think" there are 169,681 >rows >> >but this isn't true. The query operates on about 2000 rows each time then >> >deletes and commits them so why does it think there are 170K rows? Could >> >this be a source of the problem. Any suggestions welcome. >> >> No, the estimate has no effect on TRUNCATE TABLE. But if you are running >> MySQL-3.23, then TRUNCATE TABLE actually internally is DELETE FROM... and >it >> should not run any faster than DELETE FROM... >> >> If you need quick emptying in 3.23, use DROP TABLE + CREATE TABLE. >> >> >There is no particular query which causes this to happen because the same >> >batch of queries are happening over and over and over again. So What >could >> >be wrong. It takes about 2 minutes to do a shutdown when its gets like >this >> >and either way I can't afford to restart the database once its >implemented. >> > >> >As a note, a restart of the database sets the row count of raw to 0 so I >> >really think it is related to this. Most of the queries revolve around >this >> >table so any problems with it are going to cause me big problems. >> > >> >Any help much appreciated. >> >Richard >> >> Regards, >> >> Heikki >> >> >> >> --------------------------------------------------------------------- >> 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
