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
