Re: Bayes innodb problems
On Wed, 26 Sep 2007, Micah Anderson wrote: > SELECT count(*) >FROM bayes_token > WHERE id = '4' > AND ('1190846660' - atime) > '345600'; Who the hell wrote *that* query? Is MySQL smart enough to rearrange that equation to give an indexable comparison? -- John Hardin KA7OHZhttp://www.impsec.org/~jhardin/ [EMAIL PROTECTED]FALaholic #11174 pgpk -a [EMAIL PROTECTED] key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 --- Pelley: Will you pledge not to test a nuclear weapon? Ahmadeinejad: CIA! Secret prison in Europe! Abu Ghraib! -- Mahmoud Ahmadeinejad clumsily dodges a question (60 minutes interview, 9/20/2007) --- 242 days until the Mars Phoenix lander arrives at Mars
Re: Bayes innodb problems
On Wed, 26 Sep 2007 17:54:05 -0700, John D. Hardin wrote: > On Wed, 26 Sep 2007, Micah Anderson wrote: > >> SELECT count(*) >>FROM bayes_token >> WHERE id = '4' >> AND ('1190846660' - atime) > '345600'; > > Who the hell wrote *that* query? Is MySQL smart enough to rearrange that > equation to give an indexable comparison? That comes from /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pl line 243. It seems to calculate the expire delta, but in a way that can't use an index. Maybe that query should be changed from: AND (? - atime) > ?" to: AND atime < ? + ?" does that make sense? The ? gets replaced by variables later (its a prepared statement), namely $newest_atime and $start * $i, so this change would make it so it can utilize the index, thus examining less rows (which could be locked) because mysql can't look up $newest_atime - atime from the index because that value is made up, so it uses the "id" portion of the index on (id, atime)... so if it doesn't have to calculate the value for $newest_atime + $something for every row and only needs to check the index then it would be much faster. Micah ps - nils from #mysql is really the genius behind this information, not me
Re: Bayes innodb problems
micah wrote: > On Wed, 26 Sep 2007 17:54:05 -0700, John D. Hardin wrote: > >> On Wed, 26 Sep 2007, Micah Anderson wrote: >> >>> SELECT count(*) >>>FROM bayes_token >>> WHERE id = '4' >>> AND ('1190846660' - atime) > '345600'; >> Who the hell wrote *that* query? Is MySQL smart enough to rearrange that >> equation to give an indexable comparison? > > That comes from /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pl line > 243. It seems to calculate the expire delta, but in a way that can't use > an index. > > Maybe that query should be changed from: > > AND (? - atime) > ?" > > to: > > AND atime < ? + ?" > Can someone please open up a Bugzilla bug for this so it can be tracked? Thanks Michael
Re: Bayes innodb problems
On Thu, 27 Sep 2007 07:48:02 +0300, Henrik Krohns wrote: > On Thu, Sep 27, 2007 at 03:27:51AM +, micah wrote: >> On Wed, 26 Sep 2007 17:54:05 -0700, John D. Hardin wrote: >> >> > On Wed, 26 Sep 2007, Micah Anderson wrote: >> > >> >> SELECT count(*) >> >>FROM bayes_token >> >> WHERE id = '4' >> >> AND ('1190846660' - atime) > '345600'; >> > >> > Who the hell wrote *that* query? Is MySQL smart enough to rearrange >> > that equation to give an indexable comparison? >> >> That comes from /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pl >> line 243. It seems to calculate the expire delta, but in a way that >> can't use an index. >> >> Maybe that query should be changed from: >> >> AND (? - atime) > ?" returns: 1039123 >> >> to: >> >> AND atime < ? + ?" returns: 1039231 > I think you mean: > > AND atime < ? - ? returns: 0 > It does make a difference in rows checked, 129266 vs 1280748. It seems like the one that returns the same result as the original is the right one here, not the one that returns zero results. micah ps - yes the difference between the first and second result are not the same, but thats due to the atime changing, they are much more similar than 1039123 is to 0.
Re: Bayes innodb problems
On Thu, 27 Sep 2007 05:11:09 +, micah wrote: > On Thu, 27 Sep 2007 07:48:02 +0300, Henrik Krohns wrote: > >> On Thu, Sep 27, 2007 at 03:27:51AM +, micah wrote: >>> On Wed, 26 Sep 2007 17:54:05 -0700, John D. Hardin wrote: >>> >>> > On Wed, 26 Sep 2007, Micah Anderson wrote: >>> > >>> >> SELECT count(*) >>> >>FROM bayes_token >>> >> WHERE id = '4' >>> >> AND ('1190846660' - atime) > '345600'; >>> > >>> > Who the hell wrote *that* query? Is MySQL smart enough to rearrange >>> > that equation to give an indexable comparison? >>> >>> That comes from /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pl >>> line 243. It seems to calculate the expire delta, but in a way that >>> can't use an index. >>> >>> Maybe that query should be changed from: >>> >>> AND (? - atime) > ?" > > returns: 1039123 Sorry, I am wrong... I changed the > in the original query to < when I did this, I am wrong. >> AND atime < ? - ? This is right, apologies for the confusion. Micah
Re: Bayes innodb problems
On Thu, 27 Sep 2007 08:22:31 +0300, Henrik Krohns wrote: > On Thu, Sep 27, 2007 at 05:11:09AM +, micah wrote: >> On Thu, 27 Sep 2007 07:48:02 +0300, Henrik Krohns wrote: >> >> > On Thu, Sep 27, 2007 at 03:27:51AM +, micah wrote: >> >> On Wed, 26 Sep 2007 17:54:05 -0700, John D. Hardin wrote: >> >> >> >> > On Wed, 26 Sep 2007, Micah Anderson wrote: >> >> > >> >> >> SELECT count(*) >> >> >>FROM bayes_token >> >> >> WHERE id = '4' >> >> >> AND ('1190846660' - atime) > '345600'; >> >> > >> >> > Who the hell wrote *that* query? Is MySQL smart enough to >> >> > rearrange that equation to give an indexable comparison? >> >> >> >> That comes from /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pl >> >> line 243. It seems to calculate the expire delta, but in a way that >> >> can't use an index. >> >> >> >> Maybe that query should be changed from: >> >> >> >> AND (? - atime) > ?" >> >> returns: 1039123 >> >> >> >> to: >> >> >> >> AND atime < ? + ?" >> >> returns: 1039231 >> >> > I think you mean: >> > >> > AND atime < ? - ? >> >> returns: 0 > > If you think about it, minus is the logical way. You must have some > strange atime values. No, what I did was screw up the operator... what I meant to originally say was it should be changed to this: AND atime > ? + ? but I switched the operator around, and you corrected my math, by keeping the operator: AND atime < ? - ? which is functionally the same... although I think the first one is better because it keeps the operator the same as the original query. micah
Re: Bayes innodb problems
> > >> >> SELECT count(*) > > >> >>FROM bayes_token > > >> >> WHERE id = '4' > > >> >> AND ('1190846660' - atime) > '345600'; > > >> > Who the hell wrote *that* query? Is MySQL smart enough to rearrange > > >> > that equation to give an indexable comparison? It doesn't seem to make any difference in timing, seems the expression optimizer is smart enough. But it is ugly indeed. On Thursday September 27 2007 07:22:31 Henrik Krohns wrote: > > > I think you mean: > > > AND atime < ? - ? Actually both parameters are constants during execution of a select, so the following would do just fine: Index: lib/Mail/SpamAssassin/BayesStore/SQL.pm === --- lib/Mail/SpamAssassin/BayesStore/SQL.pm (revision 579950) +++ lib/Mail/SpamAssassin/BayesStore/SQL.pm (working copy) @@ -241,7 +241,7 @@ my $sql = "SELECT count(*) FROM bayes_token WHERE id = ? -AND (? - atime) > ?"; +AND atime < ?"; my $sth = $self->{_dbh}->prepare_cached($sql); @@ -251,7 +251,7 @@ } for (my $i = 1; $i <= $max_expire_mult; $i<<=1) { -my $rc = $sth->execute($self->{_userid}, $newest_atime, $start * $i); +my $rc = $sth->execute($self->{_userid}, $newest_atime - $start * $i); unless ($rc) { dbg("bayes: calculate_expire_delta: SQL error: ".$self->{_dbh}->errstr()); Mark
Re: Bayes innodb problems
Micah Anderson schrieb am 27.09.2007 02:20: processing has ground down to really slow. I'm seeing some incredibly long queries now in my slow-query log, such as: Try an "optimize table " for each of the sa tables. You just filled the database from scratch, so perhaps the counters/statistics do not reflect the actual value distribution yet. # Time: 070926 17:10:53 # [EMAIL PROTECTED]: spamass[spamass] @ [10.0.2.4] # Query_time: 758 Lock_time: 0 Rows_sent: 1 Rows_examined: 2205327 SELECT count(*) FROM bayes_token WHERE id = '4' AND ('1190846660' - atime) > '345600'; More than 10 minutes for counting 2 mio rows is a bit long. You can try to look what Mysql is doing all the time. Execute a "show full processlist" from a mysql command line while the above query is running and look at the "State" column. If a SA-initiated query is waiting for a lock and actually doing nothing, you should see it there. You also see all the other queries that are currently running at this point and may be hogging the database server. The database design and query design of Spamassassin is ok, even the appearently non-indexable term "('1190846660' - atime) > '345600'", since Mysql would not use the index on an optimized term anyway. Try an EXPLAIN of this statement - Mysql will always use only the first half for lookup (4 bytes) of the index, which covers only the id part. innodb_flush_log_at_trx_commit=1 Use value 0 for more performance and a small sacrifice of safety. See the comment in the default *.ini file: # If set to 1, InnoDB will flush (fsync) the transaction logs to the # disk at each commit, which offers full ACID behavior. If you are # willing to compromise this safety, and you are running small # transactions, you may set this to 0 or 2 to reduce disk I/O to the # logs. Value 0 means that the log is only written to the log file and # the log file flushed to disk approximately once per second. Value 2 # means the log is written to the log file at each commit, but the log # file is only flushed to disk approximately once per second. innodb_flush_log_at_trx_commit=0
Re: Bayes innodb problems
On Thu, 27 Sep 2007, Henrik Krohns wrote: > mysql> SELECT count(*) FROM bayes_token WHERE id = '1' AND (1190870335 - > atime) > 345600; > +--+ > | count(*) | > +--+ > | 1710591 | > +--+ > 1 row in set (5.69 sec) > > mysql> SELECT count(*) FROM bayes_token WHERE id = '1' AND atime < 1190870335 > - 345600; > +--+ > | count(*) | > +--+ > | 1710582 | > +--+ > 1 row in set (5.72 sec) >From those performance numbers it looks like there is not an index on the atime column... -- John Hardin KA7OHZhttp://www.impsec.org/~jhardin/ [EMAIL PROTECTED]FALaholic #11174 pgpk -a [EMAIL PROTECTED] key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79 --- Pelley: Will you pledge not to test a nuclear weapon? Ahmadeinejad: CIA! Secret prison in Europe! Abu Ghraib! -- Mahmoud Ahmadeinejad clumsily dodges a question (60 minutes interview, 9/20/2007) --- 241 days until the Mars Phoenix lander arrives at Mars
Re: Bayes innodb problems
* Alex Woick <[EMAIL PROTECTED]> [070927 02:14]: > Micah Anderson schrieb am 27.09.2007 02:20: > >> processing has ground down to really slow. I'm seeing some incredibly >> long queries now in my slow-query log, such as: > > Try an "optimize table " for each of the sa tables. You just > filled the database from scratch, so perhaps the counters/statistics do not > reflect the actual value distribution yet. Actually this bayes DB has been around for a few months, and has been built up over time. This does make me wonder what regular DB maintenance tasks should be performed on the bayes DB. It sounds like some people let the code auto-expire, while some run cron jobs to expire data? What are the benefits of each? Should I be running an optimize table every so often? >> # Time: 070926 17:10:53 >> # [EMAIL PROTECTED]: spamass[spamass] @ [10.0.2.4] >> # Query_time: 758 Lock_time: 0 Rows_sent: 1 Rows_examined: 2205327 >> SELECT count(*) >>FROM bayes_token >> WHERE id = '4' >> AND ('1190846660' - atime) > '345600'; > > More than 10 minutes for counting 2 mio rows is a bit long. You can try to > look what Mysql is doing all the time. Execute a "show full processlist" > from a mysql command line while the above query is running and look at the > "State" column. If a SA-initiated query is waiting for a lock and actually > doing nothing, you should see it there. You also see all the other queries > that are currently running at this point and may be hogging the database > server. Since I've adjusted the SQL query to use the index, I haven't seen this problem, so I can't look at the State column to see what is going on. This DB server isn't doing anything else, for any other database, so there was no possibility of other things hogging the resources on the server. > The database design and query design of Spamassassin is ok, even the > appearently non-indexable term "('1190846660' - atime) > '345600'", since > Mysql would not use the index on an optimized term anyway. Try an EXPLAIN > of this statement - Mysql will always use only the first half for lookup (4 > bytes) of the index, which covers only the id part. That is if I am optimizing... mysql> explain SELECT count(*) FROM bayes_token WHERE id = '4' AND ('1190846660' - atime) > '345600'; ++-+-+--+--+--+-+---++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+-+--+--+--+-+---++--+ | 1 | SIMPLE | bayes_token | ref | PRIMARY,bayes_token_idx2 | bayes_token_idx2 | 2 | const | 229946 | Using where; Using index | ++-+-+--+--+--+-+---++--+ >> innodb_flush_log_at_trx_commit=1 > > Use value 0 for more performance and a small sacrifice of safety. See the > comment in the default *.ini file: Mine doesn't have a comment... but looking at http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html does lead me to want to change this since I dont care about transaction-level ACID compliance with the bayes database, if I have issues with that DB, I can always restore the backup from the day before. Micah
Re: Bayes innodb problems
processing has ground down to really slow. I'm seeing some incredibly long queries now in my slow-query log, such as: Try an "optimize table " for each of the sa tables. You just filled the database from scratch, so perhaps the counters/statistics do not reflect the actual value distribution yet. Actually this bayes DB has been around for a few months, and has been built up over time. I experienced a problem with a custom innodb-stored database, where at one point a certain query suddenly took minutes instead of microseconds. Using EXPLAIN, I saw that indexes were used not as intended any more. I changed the query to include a FORCE INDEX clause, but later detected that an OPTIMIZE TABLE remedied the situation also. I saw this on two different databases that were built up over time from totally empty to many million records. OPTIMIZE TABLE on innodb tables only defragments the index and updates the index statistics. This does make me wonder what regular DB maintenance tasks should be performed on the bayes DB. It sounds like some people let the code auto-expire, while some run cron jobs to expire data? I added a TIMESTAMP column to the bayes_seen table, so I can expire this table by date. The other tables are maintained internally by SA, so don't do anything with them. Should I be running an optimize table every so often? No, I don't think so. In my above mentioned databases, the problem never came back, and I didn't run optimize table since then (more than 1 year ago). mysql> explain SELECT count(*) FROM bayes_token WHERE id = '4' AND ('1190846660' - atime) > '345600'; ++-+-+--+--+--+-+---++--+ | id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra| ++-+-+--+--+--+-+---++--+ | 1 | SIMPLE | bayes_token | ref | PRIMARY,bayes_token_idx2 | bayes_token_idx2 | 2 | const | 229946 | Using where; Using index | ++-+-+--+--+--+-+---++--+ The "2" in the key_len column makes me wonder. That means only 2 bytes of the index are used. The index at my system includes id (INTEGER=4 bytes) and atime (INTEGER=4 bytes). 2 Bytes would be half of the id field, which is impossible. The key_len field always shows 4 at my system, so it uses the id part (the first half) of the index for lookup. Have you changed id to smallint? Then you have altered the table which has the same (side-)effect as optimize table. I don't think truncating the field to 2 bytes did the speedup, I think the side-effect of recreating the index did it.
Re: Bayes innodb problems
* Michael Parker <[EMAIL PROTECTED]> [070926 21:14]: > micah wrote: > > On Wed, 26 Sep 2007 17:54:05 -0700, John D. Hardin wrote: > > > >> On Wed, 26 Sep 2007, Micah Anderson wrote: > >> > >>> SELECT count(*) > >>>FROM bayes_token > >>> WHERE id = '4' > >>> AND ('1190846660' - atime) > '345600'; > >> Who the hell wrote *that* query? Is MySQL smart enough to rearrange that > >> equation to give an indexable comparison? > > > > That comes from /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pl line > > 243. It seems to calculate the expire delta, but in a way that can't use > > an index. > > > > Maybe that query should be changed from: > > > > AND (? - atime) > ?" > > > > to: > > > > AND atime < ? + ?" > > > > Can someone please open up a Bugzilla bug for this so it can be tracked? http://issues.apache.org/SpamAssassin/show_bug.cgi?id=5661 Micah
Re: Bayes innodb problems
> Micah Anderson schrieb am 27.09.2007 02:20: > >> processing has ground down to really slow. I'm seeing >> some incredibly long queries now in my slow-query log, >> such as: > > Try an "optimize table " for each of the sa > tables. You just filled the database from scratch, so > perhaps the counters/statistics do not reflect the actual > value distribution yet. Optimize table does not work with InnoDB.
Re: Bayes innodb problems
On Fri, 28 Sep 2007 at 21:57 +0300, [EMAIL PROTECTED] confabulated: Micah Anderson schrieb am 27.09.2007 02:20: processing has ground down to really slow. I'm seeing some incredibly long queries now in my slow-query log, such as: Try an "optimize table " for each of the sa tables. You just filled the database from scratch, so perhaps the counters/statistics do not reflect the actual value distribution yet. Optimize table does not work with InnoDB. Are you sure? An excerpt from the MySQL 5.0 documentation found here: http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html "OPTIMIZE TABLE works only for MyISAM, InnoDB, and (as of MySQL 5.0.16) ARCHIVE tables. It does not work for tables created using any other storage engine." I've never tried it myself as I've been using MyISAM without issues in a per account set up. -- _|_ (_| |
Re: Bayes innodb problems
> On Fri, 28 Sep 2007 at 21:57 +0300, [EMAIL PROTECTED] > confabulated: > >> >> Optimize table does not work with InnoDB. > > Are you sure? An excerpt from the MySQL 5.0 documentation > found here: > May be old information what I told. I tried and no errors from optimize table with InnoDB table. Writing optimize scripts here now;)
Re: Bayes innodb problems
processing has ground down to really slow. I'm seeing some incredibly long queries now in my slow-query log, such as: Try an "optimize table " for each of the sa tables. You just filled the database from scratch, so perhaps the counters/statistics do not reflect the actual value distribution yet. Optimize table does not work with InnoDB. Surely it does. According to the Mysql documentation, it defragments the indexes (it probably rebuilds them) and it updates the index statistics.
Re: Bayes innodb problems
On Sat, 29 Sep 2007 03:24:17 +0200, Alex Woick <[EMAIL PROTECTED]> wrote: processing has ground down to really slow. I'm seeing some incredibly long queries now in my slow-query log, such as: >>> Try an "optimize table " for each of the sa >>> tables. You just filled the database from scratch, so >>> perhaps the counters/statistics do not reflect the actual >>> value distribution yet. >> >> Optimize table does not work with InnoDB. > >Surely it does. According to the Mysql documentation, it defragments the >indexes (it probably rebuilds them) and it updates the index statistics. I use the MySQL Tools to handle compacting and repair etc. For other jobs I use DBTools. Neither is perfect, but between them they get the job done. I do most of my admin from windows but afaik the MySQL tools are cross platform. Despite the above, I was (perhaps mistakenly) under the impression that the daily admin for bayes handled compaction etc. Perhaps your problem lies elsewhere in your system? I know form ages back that lack of a PTR for the SQL server can slow things significantly if your DB is on a different box to your SA. HTH Nigel