[Greylist-users] Greylisting with MySql I/O performance

[Evan Harris]

Can you tell if the delays are being caused mostly by the queries, or is it
by the updates?

Since you seem to be IO-bound, your solution will almost certainly be found
in optimizing that aspect.  Use something like bonnie to check your disk
subsystem performance.

Assuming you've checked the normal things (DMA enabled, kernel support for
your specific IDE chipset, etc), you may want to consider going to a SCSI
disk supporting TCQ as it can handle multiple read/write operations at once.
Using a raid0/raid1 setup may also help.

Depending on what table type you're using, you may see a performance
improvement by moving to a table type that supports row-level locking
(MyISAM only supports table locks).

As for the speed problems in the maintenance script, adding another index
will probably just slow it down more, as it's the index updates required by
the deletes that are the most expensive.  The current version in CVS has
been fixed to be much better behaved with large table sizes.

Evan


On Mon, 23 Aug 2004, Kent Ziebell wrote:

> Anyone seen I/O performance issues with MySql greylisting database?  We
> process roughly 3M pieces of email each week.  We are running our database
> on a 3Ghz system, and it is running almost 98% idle cpu.
>
> However, the database is on a single IDE disk, and it appears to be running
> 98-100% busy.  We know we would get some benefit from using a SCSI disk
> instead, but are wondering if we should move to hardware Raid 0 (striping)
> instead?
>
> We are seeing delays on our mail servers that are inquiring against the
> greylist database at this point.  Comments?
>
> We have tuned the MySql database as best we can with table-cache set to
> 1200, max_connections to 300, and key_buffer to 20M.  Any other suggestions?
>
> Also we added an index for record_expires to relaytofrom for the maintenance
> script that gets run nightly, but it too is taking way too long to run -
> again I suspect I/O is the problem.
>
> Thanks,
>
> Kent Ziebell