"Livio Righetti" wrote:
>Hi,
>
>Talking about insert, I know Mysql is fast than Postgresql.
>
>I've made the following test :
>
>40'000 insert (accouting context) using Perl and dbd :
>
>Postgresql :
> text : 4 min 53 s
> varchar : 4 min 49 s
> char : 4 min 49 s
>
>Mysql :
> text : 0 min 29 s
> varchar : 0 min 29 s
> char : 0 min 29 s
>
>So we can see Mysql is about 10 times fast.
That's extremely simplistic.
If these are separate inserts, without an enclosing transaction, they
will also be separate transactions. 40000 transactions is a lot of
overhead.
If you are not using transactions in MySql, you are inviting loss of
data integrity in the event of anything's going wrong. Of course, the
same applies if you are not making proper use of PostgreSQL's
transactions.
There are a lot of considerations apart from raw speed. For a
database, I think that data integrity is a lot more important.
>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 ?
If you are making extensive changes to tables, you need to vacuum them
frequently to recover wasted space and remove deleted entries.
Why are these tables changing so frequently? Perhaps the situation
could be improved by some redesign of the application?
Again, you may improve performance by installing a later version of
PostgreSQL. For example, a very recent change (in 7.1RC1, I think)
improved the speed of COPYing 167000 records into a table with RI
constraints from about 2 hours to 5 minutes.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Do not be anxious about anything, but in everything,
by prayer and supplication, with thanksgiving, present
your requests to God. And the peace of God, which
transcends all understanding, will guard your hearts
and your minds in Christ Jesus." Philippians 4:6,7