Re: [HACKERS] Reducing stats collection overhead
On 31-7-2007 5:07 Alvaro Herrera wrote: Arjen van der Meijden wrote: Afaik Tom hadn't finished his patch when I was testing things, so I don't know. But we're in the process of benchmarking a new system (dual quad-core Xeon) and we'll have a look at how it performs in the postgres 8.2dev we used before, the stable 8.2.4 and a fresh HEAD-checkout (which we'll call 8.3dev). I'll let you guys (or at least Tom) know how they compare in our benchmark. So, ahem, did it work? :-) The machine turned out to have a faulty mainboard, so we had to concentrate on first figuring out why it was unstable and then whether the replacement mainboard did make it stable in a long durability test Of course that behaviour only appeared with mysql and not with postgresql, so we had to run our mysql-version of the benchmark a few hundred times, rather than testing various versions, untill the machine had to go in production. So we haven't tested postgresql 8.3dev on that machine, sorry. Best regards, Arjen On 18-5-2007 15:12 Alvaro Herrera wrote: Tom Lane wrote: Arjen van der Meijden told me that according to the tweakers.net benchmark, HEAD is noticeably slower than 8.2.4, and I soon confirmed here that for small SELECT queries issued as separate transactions, there's a significant difference. I think much of the difference stems from the fact that we now have stats_row_level ON by default, and so every transaction sends a stats message that wasn't there by default in 8.2. When you're doing a few thousand transactions per second (not hard for small read-only queries) that adds up. So, did this patch make the performance problem go away? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Reducing stats collection overhead
Afaik Tom hadn't finished his patch when I was testing things, so I don't know. But we're in the process of benchmarking a new system (dual quad-core Xeon) and we'll have a look at how it performs in the postgres 8.2dev we used before, the stable 8.2.4 and a fresh HEAD-checkout (which we'll call 8.3dev). I'll let you guys (or at least Tom) know how they compare in our benchmark. Best regards, Arjen On 18-5-2007 15:12 Alvaro Herrera wrote: Tom Lane wrote: Arjen van der Meijden told me that according to the tweakers.net benchmark, HEAD is noticeably slower than 8.2.4, and I soon confirmed here that for small SELECT queries issued as separate transactions, there's a significant difference. I think much of the difference stems from the fact that we now have stats_row_level ON by default, and so every transaction sends a stats message that wasn't there by default in 8.2. When you're doing a few thousand transactions per second (not hard for small read-only queries) that adds up. So, did this patch make the performance problem go away? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] Sun Donated a Sun Fire T2000 to the PostgreSQL
On 22-6-2006 15:03, David Roussel wrote: Sureky the 'perfect' line ought to be linear? If the performance was perfectly linear, then the 'pages generated' ought to be G times the number (virtual) processors, where G is the gradient of the graph. In such a case the graph will go through the origin (o,o), but you graph does not show this. I'm a bit confused, what is the 'perfect' supposed to be? First of all, this graph has no origin. Its a bit difficult to test with less than one cpu. Anyway, the line actually is linear and would've gone through the origin, if there was one. What I did was take the level of the 'max'-line at 1 and then multiply it by 2, 4, 6 and 8. So if at 1 the level would've been 22000, the 2 would be 44000 and the 8 176000. Please do notice the distance between 1 and 2 on the x-axis is the same as between 2 and 4, which makes the graph a bit harder to read. Best regards, Arjen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] Sun Donated a Sun Fire T2000 to the PostgreSQL
On 17-6-2006 1:24, Josh Berkus wrote: Arjen, I can already confirm very good scalability (with our workload) on postgresql on that machine. We've been testing a 32thread/16G-version and it shows near-linear scaling when enabling 1, 2, 4, 6 and 8 cores (with all four threads enabled). Keen. We're trying to keep the linear scaling going up to 32 cores of course (which doesn't happen, presently). Would you be interested in helping us troubleshoot some of the performance issues? You can ask your questions, if I happen to do know the answer, you're a step further in the right direction. But actually, I didn't do much to get this scalability... So I won't be of much help to you, its not that I spent hours on getting this performance. I just started out with the normal attempts to get a good config. Currently the shared buffers is set to 30k. Larger settings didn't seem to differ much on our previous 4-core version, so I didn't even check it out on this one. I noticed I forgot to set the effective cache size to more than 6G for this one too, but since our database is smaller than that, that shouldn't make any difference. The work memory was increased a bit to 2K. So there are no magic tricks here. I do have to add its a recent checkout of 8.2devel compiled using Sun Studio 11. It was compiled using this as CPPFLAGS: -xtarget=ultraT1 -fast -xnolibmopt The -xnolibmopt was added because we couldn't figure out why it yielded several linking errors at the end of the compilation when the -xlibmopt from -fast was enabled, so we disabled that particular setting from the -fast macro. The workload generated is an abstraction and simplification of our website's workload, used for benchmarking. Its basically a news and price comparision site and it runs on LAMP (with the M of MySQL), i.e. a lot of light queries, many primary-key or indexed foreign-key lookups for little amounts of records. Some aggregations for summaries, etc. There are little writes and hardly any on the most read tables. The database easily fits in memory, the total size of the actively read tables is about 3G. This PostgreSQL-version is not a direct copy of the queries and tables, but I made an effort of getting it more PostgreSQL-minded as much as possible. I.e. I combined a few queries, I changed boolean-enum's in MySQL to real booleans in Postgres, I added specific indexes (including partials) etc. We use apache+php as clients and just open X apache processes using 'ab' at the same time to generate various amounts of concurrent workloads. Solaris scales really well to higher concurrencies and PostgreSQL doesn't seem to have problems with it either in our workload. So its not really a real-life scenario, but its not a synthetic benchmark either. Here is a graph of our performance measured on PostgreSQL: http://achelois.tweakers.net/~acm/pgsql-t2000/T2000-schaling-postgresql.png What you see are three lines. Each represents the amount of total page views processed in 600 seconds for a specific amount of Niagara-cores (i.e. 1, 2, 4, 6 and 8). Each core had all its threads enabled, so its actually 4, 8, 16, 24 and 32 virtual cpu's you're looking at. The Max-line displays the maximum generated page views on a specific core-amount for any concurrency, respectively: 5, 13, 35, 45 and 60. The Bij 50 is the amount of page views it generated with 50 apache-processes working at the same time (on two dual xeon machines, so 25 each). I took 50 a bit arbitrary but all core-configs seemed to do pretty well under that workload. The perfect line is based on the Max value for 1 core and then just multiplied by the amount of cores to have a linear reference. The Bij 50 and the perfect line don't differ too much in color, but the top-one is the perfect line. In the near future we'll be presenting an article on this on our website, although that will be in dutch the graphs should still be easy to read for you guys. And because of that I can't promise too much detailed information until then. I hope I clarified things a bit now, if not ask me about it, Best regards, Arjen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] Sun Donated a Sun Fire T2000 to the PostgreSQL community
On 16-6-2006 17:18, Robert Lor wrote: I think this system is well suited for PG scalability testing, among others. We did an informal test using an internal OLTP benchmark and noticed that PG can scale to around 8 CPUs. Would be really cool if all 32 virtual CPUs can be utilized!!! I can already confirm very good scalability (with our workload) on postgresql on that machine. We've been testing a 32thread/16G-version and it shows near-linear scaling when enabling 1, 2, 4, 6 and 8 cores (with all four threads enabled). The threads are a bit less scalable, but still pretty good. Enabling 1, 2 or 4 threads for each core yields resp 60 and 130% extra performance. Best regards, Arjen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] COUNT and Performance ...
For a more accurate view of the time used, use the \timing switch in psql.
That leaves out the overhead for forking and loading psql, connecting to
the database and such things.
I think, that it would be even nicer if postgresql automatically choose
to replace the count(*)-with-no-where with something similar.
Regards,
Arjen
Hans-Jürgen Schönig wrote:
This patch adds a note to the documentation describing why the
performance of min() and max() is slow when applied to the entire table,
and suggesting the simple workaround most experienced Pg users
eventually learn about (SELECT xyz ... ORDER BY xyz LIMIT 1).
Any suggestions on improving the wording of this section would be
welcome.
Cheers,
--
ORDER and LIMIT work pretty fast (no seq scan).
In special cases there can be another way to avoid seq scans:
action=# select tuple_count from pgstattuple('t_text');
tuple_count
-
14203
(1 row)
action=# BEGIN;
BEGIN
action=# insert into t_text (suchid) VALUES ('10');
INSERT 578606 1
action=# select tuple_count from pgstattuple('t_text');
tuple_count
-
14204
(1 row)
action=# ROLLBACK;
ROLLBACK
action=# select tuple_count from pgstattuple('t_text');
tuple_count
-
14203
(1 row)
If people want to count ALL rows of a table. The contrib stuff is pretty
useful. It seems to be transaction safe.
The performance boost is great (PostgreSQL 7.3, RedHat, 166Mhz):
root@actionscouts:~# time psql action -c select tuple_count from
pgstattuple('t_text');
tuple_count
-
14203
(1 row)
real0m0.266s
user0m0.030s
sys 0m0.020s
root@actionscouts:~# time psql action -c select count(*) from t_text
count
---
14203
(1 row)
real0m0.701s
user0m0.040s
sys 0m0.010s
I think that this could be a good workaround for huge counts (maybe
millions of records) with no where clause and no joins.
Hans
http://kernel.cybertec.at
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
