postgresql-performance using sysbench

[Claus Guttesen]

> >>>> Ubuntu 7.10:
> >>>>
> >>>> grep "transactions:" sysbench-clients-24|sort
> >>>> transactions:                        10000  (2354.49 per sec.)
> >>>> transactions:                        10001  (2126.28 per sec.)
> >>>> transactions:                        10001  (2215.52 per sec.)
> >>>> transactions:                        10001  (2236.03 per sec.)
> >>>>
> >>>> FreeBSD 7.0 stable as of Jan. 28'th:
> >>>>
> >>>> grep "transactions:" sysbench-clients-24|sort
> >>>> transactions:                        10001  (1600.36 per sec.)
> >>>> transactions:                        10002  (1963.95 per sec.)
> >>>> transactions:                        10005  (1973.17 per sec.)
> >>>>
> >>>> In other runs FreeBSD also seems to trail Ubuntu. Are there any knobs
> >>>> I could try on FreeBSD?
> >>> I think the excellent results Kris got with FreeBSD were significantly
> >>> helped by patching postgresql to remove setproctitle().
> >> You don;t need to patch postgresql for that, all you need to do is turn that
> >> off.
> >>
> >> update_process_title = off in postgresql.conf and then restart the daemon.
> >
> > I found the setting and set it to off but no real difference in performance.
> >
> >>>   from the sysbench line I see this is OLTP benchmark which should mean
> >>> a lot of write transactions, and I've consistently seen much better file
> >>> system write performance on Linux than on FreeBSD. No tuning can help here.
> >
> > Yes, that is correct. I wanted to conduct a r/w test. But if it's down
> > to the fs itself I will just leave it atm. I will probably deploy the
> > server on FreeBSD anyway since we probably won't reach that many
> > writes in the foreseable future and FreeBSD is what I do best.
> >
> > Will zfs be able to achieve better performance? I guess that ufs2 will
> > remain more or less in the state it is in now.

I wouldn't deploy zfs on a prod. db-server so this is just to hear if
some had tried zfs (with compression enabled).

>
> I went through this in detail in a thread on -stable recently (Subject:
> Performance!).  Rather than me going over all of this again, can you
> please read that thread in detail and get back to me once you have
> applied all of the discussion there to your case.

I had (allready) saved the thread in my mail-account so I could look
it up before I started testing. :-) So I compiled postgresql with the
option WITH_THREADSAFE=true and used sysbench with --pgsql-host="" .
As pointed out by Ivan my test also involved r/w whereas the thread
you (probably) mention at
http://groups.google.com/group/mailing.freebsd.stable/browse_thread/thread/e224cd4f76e9ec2d
is a read-only test.

I forgot to mention in my first post that I'm using ULE. The p800
controller has a (factory set) 25/75 read/write cache ratio.

-- 
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare