Thread: PostgreSQL on SMP Architectures

From:
Reydan Cankur
Date:

Hi,

I have seen some work done about scaling PostgreSQL on SMP machines
and see that PostgreSQL has been executed on several numbers of cores.
I have some questions about scaling PostgreSQL on SMP architectures.

System Details that I am running PostgreSQL-8.4.0:
Server: HP Integrity Superdome SD32B
Processor: Intel Itanium2 1.6 GHz (dual-core)
Number of processors: 32
Number of compute cores: 64
Memory architecture: Shared
Memory amount: 128 GB
Disk amount: 4.6 TB
High performance network: InfiniBand 20 Gbps
Operating system: RHEL 5.1 IA64

Question 1) How can I run PostgreSQL on 2,4,8,16,32 and 64 cores and
get performance results?
I have seen some work about scaling POstgreSQL on SMP architectures
and in these slides there were performance results for PostgreSQL
running on different number of cpu cores. I want to execute PostgreSQL
for different number of cpu cores like 2,4,8,16,32,64 on above server.
But I do not know how to compile or run PostgreSQL on multiple cpu
cores. What is the exact command to tell Postgres to run on specified
number of cores? I need to run Postgres on multiple CPU cores and
document this work.

Question 2)Are there any official benchmarks for PostgreSQL
performance measurements?

I mean when someone wants to run an official performance test on
PostgreSQL which queries and tables must be used. I have executed my
queries on a 5 million record table in order to measure performance,
but I also want to use the official methods.

Question 3) How can I change the allocated memory and measure the
performance of PostgreSQL?

Thanks for all answers,

Reydan

From:
Arjen van der Meijden
Date:

Hi Reydan,

PostgreSQL is unaware of the amount of cpu's and cores and doesn't
really care about that. Its up to your OS to spread the work across the
system.

Be careful with what you test, normal PostgreSQL cannot spread a single
query over multiple cores. But it will of course spread its full
workload of separate queries over the cores (or more precisely, allows
the OS to do that).

You could tell Linux to disable some cores from the scheduler (or
perhaps itanium-systems support to disable processors completely), it
works quite easy. You can just do something like:
echo 0 >> /sys/devices/system/cpu/cpu1/online

This is probably not exactly the same as removing a cpu from the system,
but may be close enough for your needs. You should be careful which
cores/cpus you disable, i.e. a supported dual-cpu configuration might
correspond to cpu's 0 and 7 in a fully populated system, rather than
just 0 and 1. And even more important, if you'd remove a cpu, you'd
remove 2 cores and you should disable them together as well (not that
Linux cares, but it might yield somewhat skewed results).

It is quite hard to benchmark PostgreSQL (or any software) on such a
system, in the sense that all kinds of scalability issues may occur that
are not normally ran into with PostgreSQL (simply because most people
have 16 cores or less in their systems). I'm not sure what to advice to
you on that matter, we've used a read-mostly internal benchmark that
scaled quite well to 32 (Sun T2) cores/threads, but that is probably not
a very realistic benchmark for your system.

The only advice I can give you here is: make sure you understand all
parts of the system and software well. Benchmarking is hard enough as it
is, if you don't understand the software and/or system its running on,
you may mess things up.

Best regards,

Arjen

On 14-2-2010 17:36 Reydan Cankur wrote:
> Hi,
>
> I have seen some work done about scaling PostgreSQL on SMP machines and
> see that PostgreSQL has been executed on several numbers of cores. I
> have some questions about scaling PostgreSQL on SMP architectures.
>
> System Details that I am running PostgreSQL-8.4.0:
> Server: HP Integrity Superdome SD32B
> Processor: Intel Itanium2 1.6 GHz (dual-core)
> Number of processors: 32
> Number of compute cores: 64
> Memory architecture: Shared
> Memory amount: 128 GB
> Disk amount: 4.6 TB
> High performance network: InfiniBand 20 Gbps
> Operating system: RHEL 5.1 IA64
>
> Question 1) How can I run PostgreSQL on 2,4,8,16,32 and 64 cores and get
> performance results?
> I have seen some work about scaling POstgreSQL on SMP architectures and
> in these slides there were performance results for PostgreSQL running on
> different number of cpu cores. I want to execute PostgreSQL for
> different number of cpu cores like 2,4,8,16,32,64 on above server. But I
> do not know how to compile or run PostgreSQL on multiple cpu cores. What
> is the exact command to tell Postgres to run on specified number of
> cores? I need to run Postgres on multiple CPU cores and document this work.
>
> Question 2)Are there any official benchmarks for PostgreSQL performance
> measurements?
>
> I mean when someone wants to run an official performance test on
> PostgreSQL which queries and tables must be used. I have executed my
> queries on a 5 million record table in order to measure performance, but
> I also want to use the official methods.
>
> Question 3) How can I change the allocated memory and measure the
> performance of PostgreSQL?
>
> Thanks for all answers,
>
> Reydan
>

From:
Scott Marlowe
Date:

With all that info, your question is still kind of vague.  Do you want
to be able to run a single query across all 64 cores?  Or do you have
1,000 users and you want them to be spread out on all cores.
PostgreSQL can do the second but has no native features to do the
first.

From:
Reydan Cankur
Date:

I want to do the second. I want to spread out the workload on all
cores. But also I want to set the core number; for example first I
want to spread out the workload to 32 cores then 64 cores and see the
scalability.
On Feb 14, 2010, at 8:25 PM, Scott Marlowe wrote:

> With all that info, your question is still kind of vague.  Do you want
> to be able to run a single query across all 64 cores?  Or do you have
> 1,000 users and you want them to be spread out on all cores.
> PostgreSQL can do the second but has no native features to do the
> first.


From:
Boszormenyi Zoltan
Date:

Reydan Cankur írta:
> I want to do the second. I want to spread out the workload on all
> cores. But also I want to set the core number; for example first I
> want to spread out the workload to 32 cores then 64 cores and see the
> scalability.

You can use taskset(1) or schedtool(8) to set the CPU affinity
of the postmaster. New backends inherit the setting.

> On Feb 14, 2010, at 8:25 PM, Scott Marlowe wrote:
>
>> With all that info, your question is still kind of vague.  Do you want
>> to be able to run a single query across all 64 cores?  Or do you have
>> 1,000 users and you want them to be spread out on all cores.
>> PostgreSQL can do the second but has no native features to do the
>> first.
>
>


--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


From:
"Kevin Grittner"
Date:

Reydan Cankur  wrote:

> I want to spread out the workload on all cores. But also I want to
> set the core number; for example first I want to spread out the
> workload to 32 cores then 64 cores and see the scalability.

PostgreSQL itself won't use more cores than you have active
connections, so one way to deal with this might be to use one of the
available connection poolers, like pgpool or pgbouncer.  That should
allow you to control the number of cores used pretty well, although
it won't support targeting particular connections to particular
cores (although this technique could be combined with other
suggestions).  The OS might use another core or two to help with
network or disk I/O, but that should be minimal.

-Kevin