pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres performance tunning
14+ messages / 8 participants
[nested] [flat]
* postgres performance tunning
@ 2010-12-16 12:33 selvi88 <selvi.dct@gmail.com>
2010-12-17 10:07 ` Re: postgres performance tunning Pierre C <lists@peufeu.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2010-12-17 14:01 ` Re: postgres performance tunning Marti Raudsepp <marti@juffo.org>
2010-12-17 22:13 ` Re: postgres performance tunning Merlin Moncure <mmoncure@gmail.com>
0 siblings, 4 replies; 14+ messages in thread
From: selvi88 @ 2010-12-16 12:33 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
@ 2010-12-17 10:07 ` Pierre C <lists@peufeu.com>
2010-12-17 10:48 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Pierre C @ 2010-12-17 10:07 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, selvi88 <selvi.dct@gmail.com>
> 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...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 10:07 ` Re: postgres performance tunning Pierre C <lists@peufeu.com>
@ 2010-12-17 10:48 ` selvi88 <selvi.dct@gmail.com>
2010-12-20 15:50 ` Re: postgres performance tunning Fernando Hevia <fhevia@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: selvi88 @ 2010-12-17 10:48 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 10:07 ` Re: postgres performance tunning Pierre C <lists@peufeu.com>
2010-12-17 10:48 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
@ 2010-12-20 15:50 ` Fernando Hevia <fhevia@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Fernando Hevia @ 2010-12-20 15:50 UTC (permalink / raw)
To: selvi88 <selvi.dct@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
@ 2010-12-17 13:20 ` Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Greg Smith @ 2010-12-17 13:20 UTC (permalink / raw)
To: selvi88 <selvi.dct@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
@ 2010-12-18 09:34 ` selvi88 <selvi.dct@gmail.com>
2010-12-20 17:26 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: selvi88 @ 2010-12-18 09:34 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
@ 2010-12-20 17:26 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-12-20 17:49 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 14+ messages in thread
From: Scott Marlowe @ 2010-12-20 17:26 UTC (permalink / raw)
To: selvi88 <selvi.dct@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-20 17:26 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-12-20 17:49 ` Greg Smith <greg@2ndquadrant.com>
2010-12-20 18:19 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
2011-01-06 21:31 ` Re: postgres performance tunning Robert Haas <robertmhaas@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Greg Smith @ 2010-12-20 17:49 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: selvi88 <selvi.dct@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-20 17:26 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
2010-12-20 17:49 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
@ 2010-12-20 18:19 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2010-12-20 18:19 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: selvi88 <selvi.dct@gmail.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-20 17:26 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
2010-12-20 17:49 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
@ 2011-01-06 21:31 ` Robert Haas <robertmhaas@gmail.com>
2011-01-06 21:41 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Robert Haas @ 2011-01-06 21:31 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; selvi88 <selvi.dct@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-20 17:26 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
2010-12-20 17:49 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2011-01-06 21:31 ` Re: postgres performance tunning Robert Haas <robertmhaas@gmail.com>
@ 2011-01-06 21:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-01-06 21:41 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Scott Marlowe @ 2011-01-06 21:41 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Greg Smith <greg@2ndquadrant.com>; selvi88 <selvi.dct@gmail.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 13:20 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` Re: postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-20 17:26 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
2010-12-20 17:49 ` Re: postgres performance tunning Greg Smith <greg@2ndquadrant.com>
2011-01-06 21:31 ` Re: postgres performance tunning Robert Haas <robertmhaas@gmail.com>
2011-01-06 21:41 ` Re: postgres performance tunning Scott Marlowe <scott.marlowe@gmail.com>
@ 2011-01-06 21:41 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Scott Marlowe @ 2011-01-06 21:41 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Greg Smith <greg@2ndquadrant.com>; selvi88 <selvi.dct@gmail.com>; pgsql-performance
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 ...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
@ 2010-12-17 14:01 ` Marti Raudsepp <marti@juffo.org>
3 siblings, 0 replies; 14+ messages in thread
From: Marti Raudsepp @ 2010-12-17 14:01 UTC (permalink / raw)
To: selvi88 <selvi.dct@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: postgres performance tunning
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
@ 2010-12-17 22:13 ` Merlin Moncure <mmoncure@gmail.com>
3 siblings, 0 replies; 14+ messages in thread
From: Merlin Moncure @ 2010-12-17 22:13 UTC (permalink / raw)
To: selvi88 <selvi.dct@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2011-01-06 21:41 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-16 12:33 postgres performance tunning selvi88 <selvi.dct@gmail.com>
2010-12-17 10:07 ` Pierre C <lists@peufeu.com>
2010-12-17 10:48 ` selvi88 <selvi.dct@gmail.com>
2010-12-20 15:50 ` Fernando Hevia <fhevia@gmail.com>
2010-12-17 13:20 ` Greg Smith <greg@2ndquadrant.com>
2010-12-18 09:34 ` selvi88 <selvi.dct@gmail.com>
2010-12-20 17:26 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-12-20 17:49 ` Greg Smith <greg@2ndquadrant.com>
2010-12-20 18:19 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-01-06 21:31 ` Robert Haas <robertmhaas@gmail.com>
2011-01-06 21:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-01-06 21:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-12-17 14:01 ` Marti Raudsepp <marti@juffo.org>
2010-12-17 22:13 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox