dbmail-util physmessage integrity

[Casper Langemeijer]

Hi All,

Recently I found the following behaviour in my cron logs,

Repairing DBMAIL physmessage integrity...
Ok. Found [156629] unconnected physmessages
Sep 26 18:40:51 mail01 dbmail-util[13321]: Error:[sql] dbmysql.c,db_query(+290): [The total number of locks exceeds the lock table size] [DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM dbmail_messages)]
Warning: could not delete orphaned physmessages. Check log.

I'm using dbmail 2.2.10

I think this happens because dbmail-util tries to remove all 156629 physmessages in a single statement, therefore all other threads have to wait until this query is finished. Also I'm guessing that this http://www.mail-archive.com/dbmail@.../msg15575.html bug report by Michael Monnerie led to the current situation.

Because I needed to fix this I built my own php script. It basically works like this:

SELECT id FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM dbmail_messages)

per 100 id's (per id would be way too slow. I found 100 to be a good number of records):

DELETE FROM dbmail_physmessage WHERE id IN ( __100idshere__ )

Greetings,
Casper
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Michael Monnerie-5]

On Sonntag 27 September 2009 Casper Langemeijer wrote:

> dbmysql.c,db_query(+290): [The total number of locks exceeds the lock
> table size] [DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT
> physmessage_id FROM dbmail_messages)]
> Warning: could not delete orphaned physmessages. Check log.
>
> I'm using dbmail 2.2.10
>
> I think this happens because dbmail-util tries to remove all 156629
> physmessages in a single statement, therefore all other threads have
> to wait until this query is finished. Also I'm guessing that this
> http://www.mail-archive.com/dbmail@.../msg15575.html bug
> report by Michael Monnerie led to the current situation.
>
> Because I needed to fix this I built my own php script. It basically
> works like this:
>
> SELECT id FROM dbmail_physmessage WHERE id NOT IN (SELECT
> physmessage_id FROM dbmail_messages)
>
> per 100 id's (per id would be way too slow. I found 100 to be a good
> number of records):

That was the behaviour from earlier releases (or was it a single delete
per ID?). The change was done because a single transaction normally is
much faster in the db server.

But I think you're going the wrong way: It should not happen that you
get this message:
The total number of locks exceeds the lock table size

I've googled, and found this:
http://bugs.mysql.com/bug.php?id=15667

Recommendation by Valeriy Kravchuk
Please, increase your innodb_buffer_pool_size ten times at least if you
have a default size.

and later:
So, this is not a bug. You should just set server variables properly.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail