Re: How to speed up commits?

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

>
> (benchmark does 100 sets of the four steps).
>
> time ./benchmark
> 0.26user 0.02system 0:07.65elapsed 3%CPU (0avgtext+0avgdata 0maxresident)k
> 0inputs+0outputs (295major+221minor)pagefaults 0swaps
>
> Whereas if I remove the commit:
>
> time ./benchmark
> 0.21user 0.02system 0:01.57elapsed 14%CPU (0avgtext+0avgdata 0maxresident)k
> 0inputs+0outputs (294major+223minor)pagefaults 0swaps
>
> Any suggestions welcome!
>
> Cheerio,
> Link.