Re: How to speed up commits?

At 04:23 AM 03-04-2000 -0400, Mike Mascari wrote:
>>
>> Does MySQL turn off sync? I don't think it does, but it seems to be able to
>> do updates (and thus syncs) a lot faster. I know postgresql has got
>> transactions and all that, but from the "time" statistics, the CPU isn't
>> really being pushed, so if it's not sync what's it waiting for?
>
>A statement in the mySQL documentation's change log for 3.22.9
>leads me to believe that mySQL does not flush dirty kernel
>buffers to disk with a call to fsync() on each
>insert/update/delete:

Yah, seems like it now. I must have been fooled by the statement that it
does a write() after every SQL statement.

I created a shell script with 100 syncs, and it took 6.25 seconds to run.
So I've got egg on my face now :*), and the bottleneck is sync not postgresql.

>By the way, we have been running a production PostgreSQL server
>on 6.5beta for over a year with fsync() off (-o -F) without
>problems. If your server doesn't suffer from kernel crashes, and
>is backed by a UPS, there's no reason in running PostgreSQL with
>fsync() on. It seems pretty clear that the mySQL folks didn't
>even consider a flushing option until the port to Win32, where
>the "kernel" was far from reliable...

Got UPS, linux 2.2.14 but I don't dare go fsyncless because another bunch
is using the postgresql engine for another app.

I'd like to run another postgres backend but the docs are sparse on running
multiple independent postmasters/postgres. Should be possible- just start
it on a different port, but the likely trouble areas will be the start/stop
scripts - whether they can cope with killing just the relevant postgres
stuff (instead of everything ;) ).

Actually a good balance would be to have a separate database engine just
for session handling, as syncs won't be important on this, it means more
memory used, but that's not too difficult to fix nowadays <grin>.

Any idea how much faster will it be without the sync?
e.g. how many (begin, select, update, commit) per second?

I'm guessing that with fsync off it's going to be just slightly slower than
the no commit version e.g. 50-70 per second, woohoo. In that case if MySQL
really doesn't do syncs on each SQL write, then things are about even and
that's another feather in the Postgresql developers' caps.

I seem to need to do vacuum analyze quite often to just maintain
performance, should I do it with cron or have something that does it during
low load times (which could mean never if I'm unlucky, or a death spiral as
things go slower and slower ;) ).

Cheerio,

Link.