Re: speed on Postgresql compared to Mysql

On Mon, 9 Apr 2001, Lincoln Yeoh wrote:

> At 05:30 AM 08-04-2001 -0400, Joel Burton wrote:
> >On Tue, 3 Apr 2001, Livio Righetti wrote:
> >> Also we used Postgresql for Radius (authentication) et we have to make 3
> >> vacuum per day otherwise the first server is overload and the user go to
> the
> >> backup server.
> >>
> >> Is it normal or my Postgresql is not well configured ?
> >
> >Err, yes.
> >
> >Did you just do 40,000 inserts in a row, one after another? Realistic
> >speed tests often have many requests coming in together, to simulate
> >application- and web-usage.
> >
> >In addition, did you wrap this in a transaction? Otherwise, you're
> >performing one transaction for *every single* insert, which is much slower
> >than in a a transaction.
> >
> >(Generally speaking, if you want to just add 40,000 rows to a table, I'd
> >use COPY, not INSERT ;-) )
>
> I don't think COPY is useful or relevant in a normal ISP authentication
> logging scenario.
>
> Wrapping more than one insert doesn't help either. I think you would
> normally want to commit each customer's transaction individually.
>
> >From his figures he can sustain about 136 inserts a second. Is that good
> enough for peak loads at a medium to big ISP?

Well, I confess I was being a bit facetious. No, COPY isn't generally
useful web apps, and, in many cases, you would handle each transaction
separately.

However, scheduling 40,000 INSERTs, all neatly following one after
another, and measuring how long that takes isn't very realistic
either! :-)

136 of anything a second seems good to me -- unless one is tracking micro
things, like all TCP/IP requests made by all users at an ISP. Given the
inexpensive price of hardware and the expensive cost of programmer time,
it usually seems better to throw some money at 512MB, 7200RPM SCSI drives
and such, rather that at paying a technologist to code in lots of
Perl/PHP/Python/Pwhatever to build all the stuff into your web app that
MySQL can't do for you.


--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington