Re: [HACKERS] Large databases, performance

Shridhar Daithankar wrote:

>On 3 Oct 2002 at 11:57, Robert Treat wrote:
>
>
>
>>NOTE: Setting follow up to the performance list
>>
>>Funny that the status quo seems to be if you need fast selects on data
>>that has few inserts to pick mysql, otherwise if you have a lot of
>>inserts and don't need super fast selects go with PostgreSQL; yet your
>>data seems to cut directly against this.
>>
>>
>
>Well, couple of things..
>
>The number of inserts aren't few. it's 5000/sec.required in the field Secondly
>I don't know really but postgresql seems doing pretty fine in parallel selects.
>If we use mysql with transaction support then numbers are really close..
>
>May be it's time to rewrite famous myth that postgresql is slow. When properly
>tuned or given enough head room, it's almost as fast as mysql..
>
>

In the case of concurrent transactions MySQL does not do as well due to
very bad locking behavious. PostgreSQL is far better because it does row
level locking instead of table locking.
If you have many concurrent transactions MySQL performs some sort of
"self-denial-of-service". I'd choose PostgreSQL in order to make sure
that the database does not block.

>>I'm curious, did you happen to run the select tests while also running
>>the insert tests? IIRC the older mysql versions have to lock the table
>>when doing the insert, so select performance goes in the dumper in that
>>scenario, perhaps that's not an issue with 3.23.52?
>>
>>
>
>IMO even if it locks tables that shouldn't affect select performance. It would
>be fun to watch when we insert multiple chunks of data and fire queries
>concurrently. I would be surprised if mysql starts slowing down..
>
>

In the case of concurrent SELECTs and INSERT/UPDATE/DELETE operations
MySQL will slow down for sure. The more concurrent transactions you have
the worse MySQL will be.

>>It also seems like the vacuum after each insert is unnecessary, unless
>>your also deleting/updating data behind it. Perhaps just running an
>>ANALYZE on the table would suffice while reducing overhead.
>>
>>
>
>I believe that was vacuum analyze only. But still it takes lot of time. Good
>thing is it's not blocking..
>
>Anyway I don't think such frequent vacuums are going to convince planner to
>choose index scan over sequential scan. I am sure it's already convinced..
>
>

PostgreSQL allows you to improve execution plans by giving the planner a
hint.
In addition to that: if you need REAL performance and if you are running
similar queries consider using SPI.

Also: 7.3 will support PREPARE/EXECUTE.

If you are running MySQL you will not be able to add features to the
database easily.
In the case of PostgreSQL you have a broad range of simple interfaces
which make many things pretty simple (eg. optimized data types in < 50
lines of C code).

PostgreSQL is the database of the future and you can perform a lot of
tuning.
MySQL is a simple frontend to a filesystem and it is fast as long as you
are doing SELECT 1+1 operations.

Also: Keep in mind that PostgreSQL has a wonderful core team. MySQL is
built on Monty Widenius and the core team = Monty.
Also: PostgreSQL = ANSI compilant, MySQL = Monty compliant

In the past few years I have seen that there is no database system which
can beat PostgreSQL's flexibility and stability.
I am familiar with various database systems but believe: PostgreSQL is
the best choice.

Hans

>Regards,
> Shridhar
>
>-----------------------------------------------------------
>Shridhar Daithankar
>LIMS CPE Team Member, PSPL.
>mailto:shridhar_daithankar(at)persistent(dot)co(dot)in
>Phone:- +91-20-5678900 Extn.270
>Fax :- +91-20-5678901
>-----------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>

--
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at
<http://cluster.postgresql.at>, www.cybertec.at
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>