Re: [PERFORM] Deferred constraint and delete performance
2010/2/10 Tom Lane t...@sss.pgh.pa.us Franck Routier franck.rout...@axege.com writes: I am wondering if deferring foreign key constraints (instead of disableing them) would increase performance, compared to non deferred constraints No, it wouldn't make any noticeable difference AFAICS. It would postpone the work from end-of-statement to end-of-transaction, but not make the work happen any more (or less) efficiently. What about disc access? Won't working with one table, then another be faster than working with both at the same time?
[PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL on SMP Architectures
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 8.1 - 8.4 regression
(Apologies if this ends up coming through multiple times - my first attempts seem to have gotten stuck.) We recently took the much needed step in moving from 8.1.19 to 8.4.2. We took the downtime opportunity to also massively upgrade our hardware. Overall, this has been the major improvement you would expect, but there is at least one query which has degraded in performance quite a bit. Here is the plan on 8.4.2: http://wood.silentmedia.com/bench/842 Here is the very much less compact plan for the same query on 8.1.19: http://wood.silentmedia.com/bench/8119 I think the problem might be that 8.1.19 likes to use a few indexes which 8.4.2 doesn't seem to think would be worthwhile. Perhaps that's because on the new hardware almost everything fits into ram, but even so, it would be better if those indexes were used. The other differences I can think of are random_page_cost (2 on the new hardware vs. 2.5 on the old), a ten-fold increase in effective_cache_size, doubling work_mem from 8MB to 16MB, and that we analyze up to 100 samples per attribute on 8.4.2, while our 8.1.19 install does 10 at most. Still, the estimates for both plans seem fairly accurate, at least where there are differences in which indexes are getting used. Everything has been analyzed recently, and given that 8.4.2 already has 10x more analysis samples than 8.1.19, I'm not sure what to do to coax it towards using those indexes. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL on SMP Architectures
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] PostgreSQL on SMP Architectures
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/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
