SQL Redux

[Steve Freegard]

Hi Chris,

Thanks very much for offering this to the list - I hadn't realised that this
was available as it's a MySQL extension to SQL (and I usually use Oracle or
Postgres).

To quote from the docs:
'When you use INSERT DELAYED, the client will get an OK at once and the row
will be inserted when the table is not in use by any other thread.'

So it looks like by using this it will make the SQL logging routines as
cheap as writing to the temporary file - I'll give it a try on my production
box soon and if I don't get any problems I'll include this in the next
release of MailWatch.

Cheers,
Steve.

--
Steve Freegard
Systems Manager
Littlehampton Book Services Ltd.

-----Original Message-----
From: Christopher Hicks [mailto:chicks at CHICKS.NET]
Sent: 05 August 2003 06:48
To: MAILSCANNER at JISCMAIL.AC.UK
Subject: Re: SQL Redux


I haven't been following this discussion so if this has already been
mentioned please accept my apologies.

On Mon, 4 Aug 2003, Julian Field wrote:
> At 14:29 04/08/2003, you wrote:
> >If there's really no performance hit to logging after every email instead
of
> >in batches, I'll see about changing my code to do it that way too.
Nothing
> >worse than having to wait for all of the children to finish logging
before I
> >can do a full restart of MailScanner.
>
> There must be a significant performance hit. In one case you are just
> appending a line to a file. In the other case you are adding a record to a
> database table and updating various indices. This *has* to be a much
bigger
> operation than just adding 1 line to a file.

By doing an "insert delayed" you let MySQL batch things up for when it's
convenient to do it.  I'm sure there's still some overhead beyond simply
writing to a file, but maybe it's not so bad.  We use this for web logging
and haven't noticed any problems.

Keeping only a few indexes in the current logging table and periodically
moving that into a fully indexed historic data set table would let that
extra index processing be done in off times.

--
</chris>

The death of democracy is not likely to be an assassination from ambush. It
will be a slow extinction from apathy, indifference, and undernourishment.
-Robert Maynard Hutchins, educator (1899-1977)

--
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the sender and delete the message from your mailbox.

This footnote also confirms that this email message has been swept by
MailScanner (www.mailscanner.info) for the presence of computer viruses.