At 07:34 AM 1/23/2007, Laurent Manchon wrote:
Hi,
I have a slow response of my PostgreSQL database 7.4 using this query below
on a table with 800000 rows:
1= Upgrade to the latest stable version of pg. That would be
8.2.x You are very much in the Dark Ages pg version wise.
pg 8.x has significant IO enhancements. Especially compared to 7.4.
select count(*)from tbl;
PostgreSQL return result in 28 sec every time.
although MS-SQL return result in 0.02 sec every time.
2= pg actually counts how many rows there are in a table. MS-SQL
looks up a count value from a internal data table... ....which can be
wrong in extraordinarily rare circumstances in a MVCC DBMS (which
MS-SQL is !not!. MS-SQL uses the older hierarchical locking strategy
for data protection.)
Since pg actually scans the table for the count, pg's count will
always be correct. No matter what.
Since MS-SQL does not use MVCC, it does not have to worry about the
corner MVCC cases that pg does.
OTOH, MVCC _greatly_ reduces the number of cases where one
transaction can block another compared to the locking strategy used in MS-SQL.
This means in real day to day operation, pg is very likely to handle
OLTP loads and heavy loads better than MS-SQL will.
In addition, MS-SQL is a traditional Codd & Date table oriented
DBMS. pg is an object oriented DBMS.
Two very different products with very different considerations and
goals (and initially designed at very different times historically.)
Compare them under real loads using real queries if you are going to
compare them. Comparing pg and MS-SQL using "fluff" queries like
count(*) is both misleading and a waste of effort.
My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
with 3GBytes RAM
My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 50000
sort_mem = 10000
max_connections = 200
shared_buffers = 3000
vacuum_mem = 32000
wal_buffers = 8
max_fsm_pages = 2000
max_fsm_relations = 100
Can you tell me is there a way to enhence performance ?
There are extensive FAQs on what the above values should be for
pg. The lore is very different for pg 8.x vs pg 7.x
Thank you
You're welcome.
Ron Peacetree
