[Maia-users] Wierd DB-error

[Robert LeBlanc]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brian Møller wrote:

> I'm having a weird problem with Maia. I have approx. 40000 email in the
> spam quarantine and 14000 in HAM. The size of the table is 1.9GB. The
> machine is running postfix, amavis-maia, mysql and the webinterface. The
> machine has 1200mb of memory and approx 61MB free, 19Mb buffers, 380Mb
> cached. Only 44kb swap is used out of 512MB.
>  
> When I want to view the last page of all the spam-messages the
> maia-webinterface just writes:  DB Error: unknown error. The same
> happens if I set Maia to show 1000 messages a page at the moment Maia is
> set to show 500 messages a page. If i set it to 100 the same thing happens.

[snip]

> [mysqld]
> sort_buffer_size                        = 15M

[snip]

> In the mysql.log file Mysql writes: 070411 15:32:28 [ERROR]
> /usr/sbin/mysqld: Sort aborted

[snip]

>         1 Query       SELECT maia_mail.id, maia_mail.received_date,
> maia_mail.score, maia_mail.sender_email, maia_mail.subject,
> maia_mail.envelope_to FROM maia_mail_recipients LEFT JOIN maia_mail ON
> maia_mail.id = maia_mail_recipients.mail_id WHERE
> maia_mail_recipients.type = 'S' AND maia_mail_recipients.recipient_id =
> '3'  ORDER BY maia_mail.sender_email ASC LIMIT 0, 500

Your sort_buffer_size setting may be too small for a query that big.
The sort buffer in particular needs to be large enough to hold the
entire data set that needs to be sorted, as it creates temporary tables
in memory for the sort operation.  The number of items you want to
/display/ isn't important for this purpose--the number of items in the
table is what matters, since all of those items need to be sorted before
the "top n" items can be identified for display.  That's why it fails
for you even when you choose small page sizes--the sort operation still
needs to load 40,000 spam items into memory first.

For good measure, you may also want to check your memory-related limit
with respect to PHP.  The default memory limits in your php.ini file are
likely much too low to display large tables in any case.

In general, though, it's not a good idea to let your quarantines grow to
such a large size before managing them.  They should be managed at least
once a day (preferably more often if you have large volumes), and you
should set your expiry periods to a reasonably small number of days to
ensure that unconfirmed items don't hang around indefinitely.  This puts
a cap on the growth of your database, ensuring that this sort of thing
doesn't happen again.

- --
Robert LeBlanc <rjl at renaissoft.com>
Renaissoft, Inc.
Maia Mailguard <http://www.maiamailguard.com/>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGHxEuGmqOER2NHewRAtdyAJwNuU2GIf3SCxvFd2PGf9NHsrYvVgCdFaak
+PGrWls14qONgMm/JvLmEtE=
=rik+
-----END PGP SIGNATURE-----