Thread: postgres performance tunning

postgres performance tunning

From
selvi88
Date:
Dear Friends,
        I have a requirement for running more that 15000 queries per second.
Can you please tell what all are the postgres parameters needs to be changed
to achieve this.
        Already I have 17GB RAM and dual core processor and this machine is
dedicated for database operation.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-performance-tunning-tp3307846p3307846.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Re: postgres performance tunning

From
"Pierre C"
Date:
> Dear Friends,
>         I have a requirement for running more that 15000 queries per
> second.
> Can you please tell what all are the postgres parameters needs to be
> changed
> to achieve this.
>         Already I have 17GB RAM and dual core processor and this machine
> is dedicated for database operation.

That depends on your queries : for simple things like "SELECT * FROM table
WHERE primary_key = constant", no problem, a desktop dual core will do
it...
So, please provide more details...

Re: postgres performance tunning

From
Greg Smith
Date:
selvi88 wrote:
>         I have a requirement for running more that 15000 queries per second.
> Can you please tell what all are the postgres parameters needs to be changed
> to achieve this.
>         Already I have 17GB RAM and dual core processor and this machine is
> dedicated for database operation.
>

You can find a parameter tuning guide at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server that may
help you out.

You are unlikely to hit 15K queries/second with a dual core processor.
When I run really trivial queries using the pgbench program to simulate
database activity, that normally gives about 7K queries/second/core.  My
dual-core laptop will do 13K/second for example.  And real-world queries
tend to be a bit more intensive than that.  I would normally expect that
a quad-core system would be needed to reach 15K even with trivial
queries; my quad-core server at home will do 28K queries/second running
pgbench.  If your individual cores are really fast, you might just make it.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


Re: postgres performance tunning

From
Marti Raudsepp
Date:
On Thu, Dec 16, 2010 at 14:33, selvi88 <selvi.dct@gmail.com> wrote:
>        I have a requirement for running more that 15000 queries per second.
> Can you please tell what all are the postgres parameters needs to be changed
> to achieve this.

You have not told us anything about what sort of queries they are or
you're trying to do. PostgreSQL is not the solution to all database
problems. If all you have is a dual-core machine then other software
can possibly make better use of the available hardware.

First of all, if they're mostly read-only queries, you should use a
caching layer (like memcache) in front of PostgreSQL. And you can use
replication to spread the load across multiple machines (but you will
get some latency until the updates fully propagate to slaves).

If they're write queries, memory databases (like Redis), or disk
databases specifically optimized for writes (like Cassandra) might be
more applicable.

Alternatively, if you can tolerate some latency, use message queuing
middleware like RabbitMQ to queue up a larger batch and send updates
to PostgreSQL in bulk.

As for optimizing PostgreSQL itself, if you have a high connection
churn then you will need connection pooling middleware in front --
such as pgbouncer or pgpool. But avoiding reconnections is a better
idea. Also, use prepared queries to avoid parsing overheads for every
query.

Obviously all of these choices involve tradeoffs and caveats, in terms
of safety, consistency, latency and application complexity.

Regards,
Marti

Re: postgres performance tunning

From
Merlin Moncure
Date:
On Thu, Dec 16, 2010 at 7:33 AM, selvi88 <selvi.dct@gmail.com> wrote:
>
> Dear Friends,
>        I have a requirement for running more that 15000 queries per second.
> Can you please tell what all are the postgres parameters needs to be changed
> to achieve this.
>        Already I have 17GB RAM and dual core processor and this machine is
> dedicated for database operation.

15k tps is doable on cheap hardware if they are read only, and
trivial.   if you are writing, you are going to need some fancy
storage.  each disk drive can do about 100-300 tps depending on the
speed of the drive and other factors (you can enhance this
significantly by relaxing sync requirements).  a single dual core is
not going to cut it though -- you should bank on 4 cores at least.

please describe the problem you are trying to solve in more detail.
15k tps can be trivially done, or could require a massive engineering
effort.  it really depends on what you are trying to do.

merlin

Re: postgres performance tunning

From
selvi88
Date:
My requirement is more than 15 thousand queries will run,
It will be 5000 updates and 5000 insert and rest will be select.

Each query will be executed in each psql client, (let say for 15000 queries
15000 thousand psql connections will be made).

Since the connections are more for me the performance is low, I have tested
it with pgbench tool.

Configurations,
RAM     : 17.9GB
CPU     : 64-bit 2 cores each 5346 bogomips

Postgres Configurations,
Shared Memory Required (shmmax) : 1720320000 bytes
Wal Buffers : 1024KB
Maintenance work mem : 1024MB
Effective Cache Size : 9216MB
Work Memory : 32MB
Shared Buffer : 1536MB
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-performance-tunning-tp3307846p3309251.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Re: postgres performance tunning

From
selvi88
Date:

Thanks for ur suggestion, already I have gone through that url, with that
help I was able to make my configuration to work for 5K queries/second.
The parameters I changed was shared_buffer, work_mem, maintenance_work_mem
and effective_cache.
Still I was not able to reach my target.

Can u kindly tell me ur postgres configurations thereby I can get some idea
out of it.


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/postgres-performance-tunning-tp3307846p3310337.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Re: postgres performance tunning

From
Fernando Hevia
Date:


On Fri, Dec 17, 2010 at 07:48, selvi88 <selvi.dct@gmail.com> wrote:

My requirement is more than 15 thousand queries will run,
It will be 5000 updates and 5000 insert and rest will be select.


What IO system are you running Postgres on? With that kind of writes you should be really focusing on your storage solution. 
 
Each query will be executed in each psql client, (let say for 15000 queries
15000 thousand psql connections will be made).


You will benefit from a connection pooler. Try fiddling with maximum_connections till you hit a sweet spot. Probably you should start with 20 connections and go up till you see your tps decrease.

Still, without deeply looking into your storage I wonder if you'll ever reach your TPS objective.

Re: postgres performance tunning

From
Scott Marlowe
Date:
On Sat, Dec 18, 2010 at 2:34 AM, selvi88 <selvi.dct@gmail.com> wrote:
>
>
> Thanks for ur suggestion, already I have gone through that url, with that
> help I was able to make my configuration to work for 5K queries/second.
> The parameters I changed was shared_buffer, work_mem, maintenance_work_mem
> and effective_cache.
> Still I was not able to reach my target.
>
> Can u kindly tell me ur postgres configurations thereby I can get some idea
> out of it.

I already posted this on stack overflow, but you'll need more machine
than you have to do that.  Specifically you'll need either to go to
SSD hard drives (which have some issues with power loss data
corruption unless you spend the bucks on them with a super capacitor
to make sure their write caches can flush on power loss) or a half
dozen to a dozen or so spinning 15k drives with a battery backed
controller.

I can sustain about 5,000 transactions per second on a machine with 8
cores (2 years old) and 14 15k seagate hard drives.

Re: postgres performance tunning

From
Greg Smith
Date:
Scott Marlowe wrote:
> I can sustain about 5,000 transactions per second on a machine with 8
> cores (2 years old) and 14 15k seagate hard drives.
>

Right.  You can hit 2 to 3000/second with a relatively inexpensive
system, so long as you have a battery-backed RAID controller and a few
hard drives.  Doing 5K writes/second is going to take a giant pile of
hard drive or SSDs to pull off.  There is no possible way to meet the
performance objectives here without a lot more cores in the server and
some pretty beefy storage too.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


Re: postgres performance tunning

From
Scott Marlowe
Date:
On Mon, Dec 20, 2010 at 10:49 AM, Greg Smith <greg@2ndquadrant.com> wrote:
> Scott Marlowe wrote:
>>
>> I can sustain about 5,000 transactions per second on a machine with 8
>> cores (2 years old) and 14 15k seagate hard drives.
>>
>
> Right.  You can hit 2 to 3000/second with a relatively inexpensive system,
> so long as you have a battery-backed RAID controller and a few hard drives.
>  Doing 5K writes/second is going to take a giant pile of hard drive or SSDs
> to pull off.  There is no possible way to meet the performance objectives
> here without a lot more cores in the server and some pretty beefy storage
> too.

And it gets expensive fast as you need more and more tps capability.
Those machines listed up there were $10k two years ago.  Their
replacements are $25k machines with 48 cores, 128G RAM and 34 15k hard
drives, and they get about 8k tps.  Note that due to the nature of
these machines' jobs they are NOT tuned heavily towards tps in real
life, but more for handling a bunch of little reads and few big writes
and reads simultaneously.  The new machines are much more than 30 or
40% faster in real world testing, for our workload they're about 10x
as fast, since we were CPU bound before with 8 cores.

Re: postgres performance tunning

From
Robert Haas
Date:
On Mon, Dec 20, 2010 at 12:49 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Scott Marlowe wrote:
>> I can sustain about 5,000 transactions per second on a machine with 8
>> cores (2 years old) and 14 15k seagate hard drives.
>
> Right.  You can hit 2 to 3000/second with a relatively inexpensive system,
> so long as you have a battery-backed RAID controller and a few hard drives.
>  Doing 5K writes/second is going to take a giant pile of hard drive or SSDs
> to pull off.  There is no possible way to meet the performance objectives
> here without a lot more cores in the server and some pretty beefy storage
> too.

Is this with synchronous_commit on, or off?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: postgres performance tunning

From
Scott Marlowe
Date:
On Thu, Jan 6, 2011 at 2:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Mon, Dec 20, 2010 at 12:49 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>> Scott Marlowe wrote:
>>> I can sustain about 5,000 transactions per second on a machine with 8
>>> cores (2 years old) and 14 15k seagate hard drives.
>>
>> Right.  You can hit 2 to 3000/second with a relatively inexpensive system,
>> so long as you have a battery-backed RAID controller and a few hard drives.
>>  Doing 5K writes/second is going to take a giant pile of hard drive or SSDs
>> to pull off.  There is no possible way to meet the performance objectives
>> here without a lot more cores in the server and some pretty beefy storage
>> too.
>
> Is this with synchronous_commit on, or off?

Off.  It doesn't seem to make a lot of difference one you're running
on a good battery backed caching RAID controller.

Re: postgres performance tunning

From
Scott Marlowe
Date:
On Thu, Jan 6, 2011 at 2:41 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Jan 6, 2011 at 2:31 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Mon, Dec 20, 2010 at 12:49 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>>> Scott Marlowe wrote:
>>>> I can sustain about 5,000 transactions per second on a machine with 8
>>>> cores (2 years old) and 14 15k seagate hard drives.
>>>
>>> Right.  You can hit 2 to 3000/second with a relatively inexpensive system,
>>> so long as you have a battery-backed RAID controller and a few hard drives.
>>>  Doing 5K writes/second is going to take a giant pile of hard drive or SSDs
>>> to pull off.  There is no possible way to meet the performance objectives
>>> here without a lot more cores in the server and some pretty beefy storage
>>> too.
>>
>> Is this with synchronous_commit on, or off?
>
> Off.  It doesn't seem to make a lot of difference one you're running
> on a good battery backed caching RAID controller.

Sorry, that's ON not OFF.  Turning it off doesn't seem to ...