Re: Anyone working on better transaction locking? - Mailing list pgsql-hackers

On Saturday 12 April 2003 20:29, you wrote:
> Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
> > But database is not webserver. It is not suppose to handle tons of
> > concurrent requests. That is a fundamental difference.
>
> And in one fell swoop you've dismissed the entire OLTP database industry.
>
> Have you ever called a travel agent and had him or her look up a fare in
> the airline database within seconds? Ever placed an order over the
> telephone? Ever used a busy database-backed web site?

Well, I was involved in designing a database solution for a telco for their 
support system. That was a fairly big database, aroung 600GB.  There was a 
response time limit as well. Though it was not millisecond.

Though project did not go thr. for non-technical reasons, the bechmark we did 
with postgresql/mysql/oracle left an impression that any of mysql/postgresql 
would handle that kind of load with server clustering. Furthermore postgresql 
would have been the choice given the mature SQL capabilities it has. Even 
with oracle, the database had to be clustered to keep the cost low enough.

> On database-backed web sites, probably the main application for databases
> today, almost certainly the main application for free software databases,
> every web page request translates into at least one, probably several
> database queries.

Queries != connection. We are talking about reducing number of connections 
required, not number of queries sent across.

> All those database queries must complete within a limited time, measured in
> milliseconds. When they complete another connection needs to be context
> switched in and run again within milliseconds.
>
> On a busy web site the database machine will have several processors and be
> processing queries for several web pages simultaneously, but what really
> matters is precisely the context switch time between one set of queries and
> another.

Well, If the application is split between application server and database 
server, I would rather put a cluster of low end database machines and have an 
data consolidating layer in middleware. That is cheaper than big iron 
database machine and can be expanded as required.

However this would not work in all cases unless you are able to partition the 
data. Otherwise you need a database that can have single database image 
across machines. 

If and when postgresql moves to mmap based model, postgresql running on mosix 
should be able to do it. Using PITR mechanism, it would get clustering 
abilities as well. This is been discussed before.

Right now, postgresql does not have any of these capabilities. So using 
application level data consolidation is the only answer

> The test I'm most interested in in the benchmarks effort is simply an index
> lookup or update of a single record from a large table. How many thousands
> of transactions per second is postgres going to be able to handle on the
> same machine as mysql and oracle? How many hundreds of thousands of
> transactions per second will they be able to handle on a 4 processor
> hyperthreaded machine with a raid array striped across ten disks?

I did the same test on a 4 way xeon machine with 4GB of RAM and 40GB of data. 
Both mysql and postgresql did lookups at approximately 80% speed of oracle. 
IIRC they were doing 600 queries per second but I could be off. It was more 
than 6 months ago.
However testing number of clients was not a criteria. We only tested with 10 
concurrent clients. Mysql freezes at high database loads and high number of 
concurrent connection. Postgresql has tendency to hold the load muh longer. 
So more the number of connections, faster will be response time. That should 
be a fairly flat curve for upto 100 concurrent connection. Good enough 
hardware assumed.
Shridhar