How to speed up commits?

At 02:51 PM 3/04/00 +0800, Lincoln Yeoh wrote:

Hi,

I was doing a few tests to optimize my perl web app, and this is what I got.

A few questions, if you don't do the commit, then you loose your data,
so there isn't much point in doing that is there ...

How much RAM does you machine have?

Where is the blocking happening, what does vmstat tell you
(is it blocking on disk, ram or what?)

What sort of disk drives are you using?

How many postmasters are you running, what is the maximum
you have set (I think the default is 32 or somthing)

Does you code pool database connections at all or does it reconnect for each statement (select, update, etc)

--
Mr Grumpy is now a virtual personality ...
http://www.cyber4.org/members/grumpy/camera/index.html

Lincoln Yeoh wrote:

Hi,

I was doing a few tests to optimize my perl web app, and this is what I got.

without database: 140 hits/sec
with a rollback/begin and a select: 90 hits/sec
with a rollback/begin, select and an update (but no commit): 70 hits/sec
with a rollback/begin, select + update + commit: 13 hits/sec

Any idea how to speed things up? Turning off sync would be dangerous right?

The minimum my web app does is:
1) rollback/begin
2) select session information
3) update session information (new session time out)
4) commit update

I written a simple script which does the same thing and the figures are the
same.

Any idea how to speed things up without switching to another database
engine or session control method? Or do I have to live with 13 hits/sec max?

)-;

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:

"You can now start mysqld on Win32 with the --flush option. This
will flush all tables to disk after each update. This makes
things much safer on NT/Win98 but also MUCH slower."

And in the change log for 3.22.18:

"Added option -O flush-time=# to mysqld. This is mostly useful on
Win32 and tells how often MySQL should close all unused tables
and flush all updated tables to disk."

These statements imply that unlike PostgreSQL, which defaults to
fsync() ON, mySQL defaults to fsync() OFF.

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...

Hope that helps,

Mike Mascari

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.

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...

OK tried it briefly with -o -F. Got 68 hits/sec! That's quite a remarkable
speed up from 13-15 hits/sec.

On a slower machine the speed up was only from 6 hits/s to 13 hits/s.

Switched back to sync tho. Too cowardly ;). Plus speed is not so critical
at the moment.

Well at least I know there's more juice on tap if I really need it...

Cheerio,

Link.