Обсуждение: PostgreSQL on SMP Architectures
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
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 >
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.
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.
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/
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