Re: Performance Issues on Opteron Dual Core - Mailing list pgsql-performance

Gregory Stewart wrote:
> Hello,
>
> We are currently developing a web application and have the webserver and
> PostgreSQL with our dev db running on a machine with these specs:
>
> Win 2003 standard
> AMD Athlon XP 3000 / 2.1 GHZ
> 2 Gig ram
> 120 gig SATA HD
> PostgreSQL 8.1.0
> Default pgsql configuration + shared buffers = 30,000
>
> The performance of postgresql and our web application is good on that
> machine, but we decided to build a dedicated database server for our
> production database that scales better and that we can also use for internal
> applications (CRM and so on).
>
> To make a long story short, we built a machine with these specs:
>
> Windows 2003 Standard
> AMD Opteron 165 Dual Core / running at 2 GHZ
> 2 gig ram
> 2 x 150 Gig SATA II HDs in RAID 1 mode (mirror)
> PostgreSQL 8.1.3
> Default pgsql configuration + shared buffers = 30,000
>
> Perfomance tests in windows show that the new box outperforms our dev
> machine quite a bit in CPU, HD and memory performance.
>
> I did some EXPLAIN ANALYZE tests on queries and the results were very good,
> 3 to 4 times faster than our dev db.
>
> However one thing is really throwing me off.
> When I open a table with 320,000 rows / 16 fields in the pgadmin tool (v
> 1.4.0) it takes about 6 seconds on the dev server to display the result (all
> rows). During these 6 seconds the CPU usage jumps to 90%-100%.
>
> When I open the same table on the new, faster, better production box, it
> takes 28 seconds!?! During these 28 seconds the CPU usage jumps to 30% for 1
> second, and goes back to 0% for the remaining time while it is running the
> query.
>
> What is going wrong here? It is my understanding that postgresql supports
> multi-core / cpu environments out of the box, but to me it appears that it
> isn't utilizing any of the 2 cpu's available. I doubt that my server is that
> fast that it can perform this operation in idle mode.
>
> I played around with the shared buffers and tried out versions 8.1.3, 8.1.2,
> 8.1.0 with the same result.
>
> Has anyone experienced this kind of behaviour before?
> How representative is the query performance in pgadmin?
>

Pgadmin can give misleading times for queries that return large result
sets over a network, due to:

1/ It takes time to format the (large) result set for display.
2/ It has to count the time spent waiting for the (large) result set to
travel across the network.

You aren't running Pgadmin off the dev server are you? If not check your
network link to dev and prod  - is one faster than the other? (etc).

To eliminate Pgadmin and the network as factors try wrapping your query
in a 'SELECT count(*) FROM (your query here) AS a', and see if it
changes anything!

Cheers

Mark