asked on

Postgres performance bench mark

Hi PG Gurus,

I'm (a newbie db admin) planning to start db performance tune up from the scratch. This will start from server side parameters tune up, and Postgres configuration fine tune etc. Postgres 9.2 on Ubuntu and it's Hot standby cluster too. So there might be some synchronization tune up too. WAL segments, Archiving, WAL Keep, etc.

Version string	PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

Open in new window

And now I need to have a bench mark of the current performance state of the database cluster.

What is the best approach for this ?
What are the triggering point of performance ?
Please recommend good GUI tools for this ?

Thanks a lot for your time !

gheist

The best approach is to drop word GUI from the question.

First you need to estimate what your workload is (e.g you import 1TB in 1h, or you have 500 concurrent connections doing 90%oltp and 10% olap..), because there is no recipe for "lets double the speed right now!

I suspect that by triggering point you mean "common bottlenecks".

Are you trying to build a new DB system or learning to improve old one?

And sometimes it goes speed vs integrity.

Shakthi777

ASKER

Thanks a lot for the attention gheist !

Usually this system gets about at least 4000 concurrent connections in the peak hours, and there are some heavy loads coming for reports and those requests are pointed in to the read-only node. maximum connections are set to 400 recently. No connection pooling enabled further from the application I can see about 20 to 30 connections initiating in the peak time.

#Are you trying to build a new DB system or learning to improve old one?
I'm trying study the current DB and improve it's performance.

And I know that I will need a lot of guidance from an experience person like you.

ASKER CERTIFIED SOLUTION

gheist

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shakthi777

ASKER

I think it's a great explanation, and I will go through the points you have escalated. However the disks are on SSD but not the NODE2. I will posted my next bunch of questions in next few days.