Re: [PERFORM] hyperthreaded cpu still an issue in 8.4?
On Wed, 29 Jul 2009, Stefan Kaltenbrunner wrote: Well the real problem is that pgbench itself does not scale too well to lots of concurrent connections and/or to high transaction rates so it seriously skews the result. Sure, but that's what the multi-threaded pgbench code aims to fix, which didn't show up until after you ran your tests. I got the 90K select TPS with a completely unoptimized postgresql.conf, so that's by no means the best it's possible to get out of the new pgbench code on this hardware. I've seen as much as a 40% improvement over the standard pgbench code in my limited testing so far, and the patch author has seen a 450% one. You might be able to see at least the same results you got from sysbench out of it. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] hyperthreaded cpu still an issue in 8.4?
Greg Smith wrote: On Wed, 29 Jul 2009, Stefan Kaltenbrunner wrote: Well the real problem is that pgbench itself does not scale too well to lots of concurrent connections and/or to high transaction rates so it seriously skews the result. Sure, but that's what the multi-threaded pgbench code aims to fix, which didn't show up until after you ran your tests. I got the 90K select TPS with a completely unoptimized postgresql.conf, so that's by no means the best it's possible to get out of the new pgbench code on this hardware. I've seen as much as a 40% improvement over the standard pgbench code in my limited testing so far, and the patch author has seen a 450% one. You might be able to see at least the same results you got from sysbench out of it. oh - the 90k tps are with the new multithreaded pgbench? missed that fact. As you can see from my results I managed to get 83k with the 8.4 pgbench on a slightly slower Nehalem which does not sound too impressive for the new code... Stefan -- 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] hyperthreaded cpu still an issue in 8.4?
On Wed, 29 Jul 2009, Stefan Kaltenbrunner wrote: oh - the 90k tps are with the new multithreaded pgbench? missed that fact. As you can see from my results I managed to get 83k with the 8.4 pgbench on a slightly slower Nehalem which does not sound too impressive for the new code... I got 96K with the default postgresql.conf - 32MB shared_buffers etc. - and I didn't even try to find the sweet spot yet for things like number of threads, that's just the first useful number that popped out. I saw as much as 87K with the regular one too. I already planned to run the test set you did for comparison sake at some point. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] select query performance question
Kevin Grittner wrote:
Thomas Zaksek zak...@ptt.uni-due.de wrote:
Is this query plan near to optimal or are their any serious flaws?
I didn't see any problem with the query, but with the information
provided, we can't really tell if you need to reconfigure something,
or maybe add an index.
The plan generated for the query is doing an index scan and on one
table and randomly accessing related rows in another, with an average
time per result row of about 4ms. Either you've got *really* fast
drives or you're getting some benefit from cache. Some obvious
questions:
What version of PostgreSQL is this?
What OS is the server on?
What does the server hardware look like? (RAM, drive array, etc.)
What are the non-default lines in the postgresql.conf file?
What are the definitions of these two tables? How many rows?
-Kevin
Postgresql 8.3
Freebsd 7.2
A HP Server with Dual Opteron, 8GB Ram and a RAID 5 SCSI System
\d+ de_mw;
Table de_mw
Column | Type | Modifiers
| Description
-+--++-
nr | integer | not null default nextval('de_mw_nr_seq'::regclass) |
j_ges | smallint ||
mw_abh | integer ||
mw_test | bit(19) ||
Indexes:
de_mw_pkey PRIMARY KEY, btree (nr)
de_mw_j_ges_key UNIQUE, btree (j_ges, mw_abh, mw_test)
de_nw_nr_idx btree (nr)
Has OIDs: no
\d+ messungen_v_dat_2009_04_13
Table messungen_v_dat_2009_04_13
Column | Type | Modifiers | Description
---+--+---+-
ganglinientyp | character(1) | not null |
minute_tag| smallint | not null |
zs_nr | integer | not null |
mw_nr | integer | |
Indexes:
messungen_v_dat_2009_04_13_pkey PRIMARY KEY, btree (ganglinientyp,
minute_tag, zs_nr)
messungen_v_dat_2009_04_13_gtyp_minute_tag_idx btree
(ganglinientyp, minute_tag)
messungen_v_dat_2009_04_13_gtyp_minute_tag_zs_nr_idx btree
(ganglinientyp, minute_tag, zs_nr)
messungen_v_dat_2009_04_13_minute_tag_idx btree (minute_tag)
Foreign-key constraints:
messungen_v_dat_2009_04_13_mw_nr_fkey FOREIGN KEY (mw_nr)
REFERENCES de_mw(nr)
messungen_v_dat_2009_04_13_zs_nr_fkey FOREIGN KEY (zs_nr)
REFERENCES de_zs(zs)
Inherits: messungen_v_dat
Has OIDs: no
select count(*) from messungen_v_dat_2009_04_13
traffic_nrw_0_4_0-# ;
count
-
6480685
(1 row)
traffic_nrw_0_4_0=# select count(*) from de_mw;
count
--
23853134
(1 row)
--
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] hyperthreaded cpu still an issue in 8.4?
On Tue, 28 Jul 2009, Dave Youatt wrote: Unlikely. Different threads on the same CPU core share their resources, so they don't need an explicit communication channel at all (I'm simplifying massively here). A real interconnect is only needed between CPUs and between different cores on a CPU, and of course to the outside world. Scott's explanation of why SMT works better now is much more likely to be the real reason. Actually, no, I wrote that. Please give at least some indication when replying to an email which parts of it are your words and which are quotes from someone else. Emails can be incredibly confusing without that distinction. You actually wrote: :-) there's also this interconnect thingie between sockets, cores and memory. Nehalem has a new one (for Intel), integrated memory controller, that is. And a new on-chip cache organization. This, (like I mention elsewhere) will make the CPU faster overall, but is unlikely to increase the performance gain of switching SMT on. In fact, having a lower latency memory controller is more likely to reduce some of the problem that SMT is trying to address - that of a single thread stalling on memory access. Having said that, memory access latency is not scaling as quickly as CPU speed, so over time SMT is going to get more important. Matthew -- Take care that thou useth the proper method when thou taketh the measure of high-voltage circuits so that thou doth not incinerate both thee and the meter; for verily, though thou has no account number and can be easily replaced, the meter doth have one, and as a consequence, bringeth much woe upon the Supply Department. -- The Ten Commandments of Electronics -- 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] hyperthreaded cpu still an issue in 8.4?
On Tue, Jul 28, 2009 at 7:21 PM, Greg Smithgsm...@gregsmith.com wrote: On Tue, 28 Jul 2009, Scott Marlowe wrote: Just FYI, I ran the same basic test but with -c 10 since -c shouldn't really be greater than -s That's only true if you're running the TPC-B-like or other write tests, where access to the small branches table becomes a serious hotspot for contention. The select-only test has no such specific restriction as it only operations on the big accounts table. Often peak throughput is closer to a very small multiple on the number of cores though, and possibly even clients=cores, presumably because it's more efficient to approximately peg one backend per core rather than switch among more than one on each--reduced L1 cache contention etc. That's the behavior you measured when your test showed better results with c=10 than c=16 on a 8 core system, rather than suffering less from the c must be s contention limitation. Sadly I don't have or expect to have a W5580 in the near future though, the X5550 @ 2.67GHz is the bang for the buck sweet spot right now and accordingly that's what I have in the lab at Truviso. As Merlin points out, that's still plenty to spank any select-only pgbench results I've ever seen. The multi-threaded pgbench batch submitted by Itagaki Takahiro recently is here just in time to really exercise these new processors properly. Can I trouble you for a single client run, say: pgbench -S -c 1 -t 25 I'd like to see how much of your improvement comes from SMT and how much comes from general improvements to the cpu... merlin -- 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] Full text search with ORDER BY performance issue
On Mon, Jul 20, 2009 at 8:12 AM, Oleg Bartunovo...@sai.msu.su wrote:
Here's a couple of queries:
archive= explain analyze select * from a where comment_tsv @@
plainto_tsquery('love') order by timestamp desc limit 24 offset 0;
QUERY PLAN
--
Limit (cost=453248.73..453248.79 rows=24 width=281) (actual
time=188441.047..188441.148 rows=24 loops=1)
- Sort (cost=453248.73..453882.82 rows=253635 width=281) (actual
time=188441.043..188441.079 rows=24 loops=1)
Sort Key: timestamp
Sort Method: top-N heapsort Memory: 42kB
- Bitmap Heap Scan on a (cost=17782.16..446166.02 rows=253635
width=281) (actual time=2198.930..187948.050 rows=256378 loops=1)
Recheck Cond: (comment_tsv @@ plainto_tsquery('love'::text))
- Bitmap Index Scan on timestamp_comment_gin
(cost=0.00..17718.75 rows=253635 width=0) (actual time=2113.664..2113.664
rows=259828 loops=1)
Index Cond: (comment_tsv @@
plainto_tsquery('love'::text))
Total runtime: 188442.617 ms
(9 rows)
archive= explain analyze select * from a where comment_tsv @@
plainto_tsquery('love') limit 24 offset 0;
QUERY PLAN
--
Limit (cost=0.00..66.34 rows=24 width=281) (actual time=14.632..53.647
rows=24 loops=1)
- Seq Scan on a (cost=0.00..701071.49 rows=253635 width=281) (actual
time=14.629..53.588 rows=24 loops=1)
Filter: (comment_tsv @@ plainto_tsquery('love'::text))
Total runtime: 53.731 ms
(4 rows)
First one runs painfully slow.
Hmm, everything is already written in explain :) In the first query 253635
rows should be readed from disk and sorted, while in the
second query only 24 (random) rows readed from disk, so there is 4
magnitudes
difference and in the worst case you should expected time for the 1st query
about 53*10^4 ms.
If love is an uncommon word, there's no help for queries of this type
being slow unless the GIN index can return the results in order. But
if love is a common word, then it would be faster to do an index scan
by timestamp on the baserel and then treat comment_tsv @@
plainto_tsquery('love') as a filter condition. Is this a selectivity
estimation bug?
...Robert
--
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] Full text search with ORDER BY performance issue
Robert Haas robertmh...@gmail.com writes:
If love is an uncommon word, there's no help for queries of this type
being slow unless the GIN index can return the results in order. But
if love is a common word, then it would be faster to do an index scan
by timestamp on the baserel and then treat comment_tsv @@
plainto_tsquery('love') as a filter condition. Is this a selectivity
estimation bug?
Doesn't look like it: estimated number of matches is 253635, actual is
259828, which is really astonishingly close considering what we have to
work with. It's not clear though what fraction of the total that
represents.
regards, tom lane
--
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] Full text search with ORDER BY performance issue
If love is an uncommon word, there's no help for queries of this type
being slow unless the GIN index can return the results in order. But
if love is a common word, then it would be faster to do an index scan
by timestamp on the baserel and then treat comment_tsv @@
plainto_tsquery('love') as a filter condition. Is this a selectivity
estimation bug?
If you have really lots of documents to index (this seems the case)
perhaps you should consider Xapian. It is very easy to use (although, of
course, tsearch integrated in Postgres is much easier since you have
nothing to install), and it is *incredibly* fast.
In my tests (2 years ago) with many gigabytes of stuff to search into,
differences became obvious when the data set is much bigger than RAM.
- Postgres' fulltext was 10-100x faster than MySQL fulltext on searches
(lol) (and even a lot more faster on INSERTs...)
- and Xapian was 10-100 times faster than Postgres' fulltext.
(on a small table which fits in RAM, differences are small).
Of course Xapian is not Postgres when you talk about update
concurrency..
(single writer = fulltext index updating background job is needed, a
simple Python script does the job)
--
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] Full text search with ORDER BY performance issue
On Wed, Jul 29, 2009 at 10:22 AM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
If love is an uncommon word, there's no help for queries of this type
being slow unless the GIN index can return the results in order. But
if love is a common word, then it would be faster to do an index scan
by timestamp on the baserel and then treat comment_tsv @@
plainto_tsquery('love') as a filter condition. Is this a selectivity
estimation bug?
Doesn't look like it: estimated number of matches is 253635, actual is
259828, which is really astonishingly close considering what we have to
work with. It's not clear though what fraction of the total that
represents.
Hmm, good point. It seems like it would be useful to force the
planner into use the other plan and get EXPLAIN ANALYZE output for
that for comparison purposes, but off the top of my head I don't know
how to do that.
...Robert
--
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] Full text search with ORDER BY performance issue
Robert Haas robertmh...@gmail.com writes: Hmm, good point. It seems like it would be useful to force the planner into use the other plan and get EXPLAIN ANALYZE output for that for comparison purposes, but off the top of my head I don't know how to do that. The standard way is begin; drop index index_you_dont_want_used; explain problem-query; rollback; Ain't transactional DDL wonderful? (If this is a production system, you do have to worry about the DROP transiently locking the table; but if you put the above in a script rather than doing it by hand, it should be fast enough to not be a big problem.) regards, tom lane -- 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] Full text search with ORDER BY performance issue
On Wed, Jul 29, 2009 at 11:29 AM, Tom Lanet...@sss.pgh.pa.us wrote: Ain't transactional DDL wonderful? Yes. :-) ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] autovacuum 'stuck' ?
When reviewing the vacuum logs, I notice that on any given day autovacuum only seems to touch four of the tables in one of our schemas (not counting toast tables). However, if I look at the pgstatspack output for the same day, I see that there are plenty of other tables receiving a high number of inserts and deletes. How can I tell if autovacuum is accurately choosing the tables that need its attention (these four tables apparently) or if autovacuum is simply never making it to the other tables cause its too busy with these tables (my suspicion)? This is on 8.3.7 with the following settings in postgresql.conf: autovacuum = on log_autovacuum_min_duration = 0 autovacuum_vacuum_threshold = 250 autovacuum_analyze_threshold = 125 autovacuum_vacuum_scale_factor = 0.2 autovacuum_analyze_scale_factor = 0.1 autovacuum_naptime = 5min Any/all other information can be provided as needed. TIA, again. -- Douglas J Hunley, RHCT doug.hun...@gmail.com : http://douglasjhunley.com : Twitter: @hunleyd Obsessively opposed to the typical. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
