Re: Database performance

If you don't care about keeping historical data, you can comment out the
query starting "REPLACE INTO relayreport" from the db_maintenance script.
That should significantly speed it up.

Since you're turning off your mail relays during maintenance anyway, you can
also do the following things:

1. Set $sleep_secs 0.

2. Increase $chunk_size to be much larger than the default 1000 records.

If you didn't want to turn off your relays during maintenance, you could
decrease the $chunk_size so the db is locked for shorter periods of time
during the copy/delete queries.

Also, assuming they aren't already, you might want to look into converting
your Mysql tables from the default MyISAM type to the newer InnoDB type,
since InnoDB supports row-level locking instead of table locks during
updates, and won't delay other DB clients unless they are hitting the very
same block of rows that the maintenance script happens to be working with.

Evan


On Wed, 4 Jan 2006, Brian Ross wrote:

> Hello,
>
> I was wondering if anyone has any performance tuning tips for the MySQL
> db as implemented in relaydelay.
>
> We're running the db_maintenance script regularly, expiring
> approximately 1.25 million rows on a nightly basis with the maintenace
> taking approximately 1.5 hours.
>
> During the maintenance we're turning off our mail relays because the
> maintenance is so disk intensive that it causes relaydelay to slow down
> to the point of hitting the milter time out values (yes - I suppose I
> could look at altering those in the sendmail.cf).
>
> Any thoughts on optimization would be appreciated.
>
>
> -Brian
>
>
>
>
>
> +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
> | Name        | Type   | Row_format | Rows     | Avg_row_length |
> Data_length | Max_data_length | Index_length | Data_free |
> Auto_increment | Create_time         | Update_time         |
> Check_time          | Create_options | Comment |
> +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
> | dns_name    | MyISAM | Dynamic    | 11463906 |             57 |
> 654696476 |      4294967295 |    260299776 |         0 |           NULL
> | 2003-07-09 16:00:24 | 2006-01-04 22:44:01 | NULL
> |                |         |
> | mail_log    | MyISAM | Dynamic    |        0 |              0
> |           0 |      4294967295 |         1024 |         0
> |              1 | 2003-07-09 16:00:24 | 2003-07-09 16:00:24 |
> NULL                |                |         |
> | relayreport | MyISAM | Dynamic    | 21424674 |            118 |
> 2535895056 |      4294967295 |    994676736 |         0 |      372929009
> | 2003-12-07 01:05:17 | 2006-01-04 21:07:35 | NULL
> |                |         |
> | relaytofrom | MyISAM | Dynamic    |  3248361 |            131 |
> 426743244 |      4294967295 |    126875648 |         0 |      372993269
> | 2003-07-09 16:00:24 | 2006-01-04 22:44:15 | 2006-01-04 21:34:26
> |                |         |
> +-------------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+
> 4 rows in set (0.01 sec)
> _______________________________________________
> Greylist-users mailing list
> [email protected]
> http://lists.puremagic.com/cgi-bin/mailman/listinfo/greylist-users
>