Re: [PERFORM] help speeding up a query in postgres 8.4.5
On Tue, Apr 5, 2011 at 1:25 PM, Maria L. Wilson wrote: This bit: > left outer join INVENTORY IV on GV.GRANULE_ID = IV.GRANULE_ID, INVSENSOR > INVS has both an explicit and an implicit join. This can constrain join re-ordering in the planner. Can you change it to explicit joins only and see if that helps? -- 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] Postgres refusing to use >1 core
I suspect your app is doing lots of tiny single-row queries instead of efficiently batching things. It'll be wasting huge amounts of time waiting for results. Even if every query is individually incredibly fast, with the number of them you seem to be doing you'll lose a LOT of time if you loop over lots of little SELECTs. Using unix sockets, you can expect about 10-20.000 queries/s on small simple selects per core, which is quite a feat. TCP adds overhead, so it's slower. Over a network, add ping time. In plpgsql code, you avoid roundtrips, data serializing, and context switches, it can be 2-4x faster. But a big SQL query can process millions of rows/s, it is much more efficient. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 'Interesting' prepared statement slowdown on large table join
Hello everyone,
I have the following scenario:
There's a web service that updates some information in two tables,
every 5 minutes.
In order to do this it will issue a select on the tables, get some
data, think about it, and then update it if necessary.
Sometimes - about once every two weeks, I think, it will start using
an extremely inefficient plan where it will loop on many results from
the large table instead of getting the few results from small table and
looping on those.
The difference in performance is devastating - from 18 ms to 10-20
seconds, and of course drags everything down.
The situation will usually not resolve itself - but it will resolve
after i run "ANALYZE party; ANALYZE big_table" about... 3-5 times.
Interesting.
When the problem is occuring, it is completely reproducible using
local psql - thus probably not a connector issue.
I have tried to reconnect and to re-prepare the statement to allow it
to choose a new plan after the 'first' analyze, but it didn't help.
I have tried to increase ANALYZE statistics target on party_id (as the
join field) on both tables to 300, but it doesn't appear to help (not
even with the frequency of incidents).
The select is as follows:
prepare ps(varchar,varchar,varchar) as select party.party_id from party,
big_table where external_id = $1 and party.party_id = big_table.party_id
and attr_name = $2 and attr_value = $3;
PREPARE
execute ps('13','GroupId','testshop');
party_id
--
659178
The query will always return exactly one row.
I hope this is enough information to start a discussion on how to avoid
this. The only reliable solution we've come up with so far is to split
selects and do the join in Java, but this seems like a very unorthodox
solution and could cause other trouble down the road.
Thank you in advance,
Andrei Prodan
Systems Administator
testdb=# select count(1) from party where external_id='13';
count
---
4
(1 row)
testdb=# select count(1) from big_table where attr_name='GroupId';
count
-
1025867
(1 row)
testdb=# select count(1) from big_table where attr_value='testshop';
count
917704
(1 row)
Table party:
Rows: 1.8M
Table size: 163 MB
Indexes size: 465 MB
Table big_table:
- Frequently updated
Rows: 7.2M
Table size: 672 MB
Indexes size: 1731 MB
GOOD PLAN:
testdb=# explain analyze execute ps('13','GroupId','testshop');
QUERY
PLAN
-
--
Nested Loop (cost=0.00..19.11 rows=1 width=7) (actual
time=2.662..18.388 rows=1 loops=1)
-> Index Scan using partyext_id_idx on party (cost=0.00..8.47
rows=1 width=7) (actual time=2.439
..2.495 rows=4 loops=1)
Index Cond: ((external_id)::text = ($1)::text)
-> Index Scan using pk_big_table on big_table (cost=0.00..10.62
rows=1 width=7) (act ual time=3.972..3.972 rows=0 loops=4)
Index Cond: (((big_table.party_id)::text =
(party.party_id)::text) AND ((party_attribu te.attr_name)::text =
($2)::text))
Filter: ((big_table.attr_value)::text = ($3)::text) Total
runtime: 18.484 ms
(7 rows)
BAD PLAN:
testdb=# explain analyze execute ps('13','GroupId','testshop');
QUERY
PLAN
---
Nested Loop (cost=0.00..56.83 rows=4 width=7) (actual
time=355.569..9989.681 rows=1 loops=1)
-> Index Scan using attr_name_value on big_table (cost=0.00..22.85
rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1)
Index Cond: (((attr_name)::text = ($2)::text) AND
((attr_value)::text = ($3)::text))
-> Index Scan using pk_party on party (cost=0.00..8.48 rows=1
width=7) (actual time=0.010..0.010 rows=0 loops=914786)
Index Cond: ((party.party_id)::text =
(big_table.party_id)::text)
Filter: ((party.external_id)::text = ($1)::text) Total runtime:
9989.749 ms
(7 rows)
name |
current_setting
-+--
-
version | PostgreSQL 8.4.4 on
x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red
Hat 4.1.2-48), 64-bit
autovacuum_analyze_scale_factor | 0.05
autovacuum_max_workers | 9
autovacuum_vacuum_scale_factor | 0.1
checkpoint_segments | 30
effective_cache_size| 6GB
effective_io_concurrency| 6
lc_collate | en_US.UTF-8
lc_ctype| en_US.UTF-8
listen_addresses| *
log_autovacuum_min_duration | 1s
log_checkpoints | on
log_destination | stderr
log_directo
Re: [PERFORM] Postgres NoSQL emulation
why even have multiple rows? just jam it all it there! :-D LOL But seriously, when using an ORM to stuff an object hierarchy into a database, you usually get problems with class inheritance, and all solutions suck more or less (ie, you get a zillion tables, with assorted pile of JOINs, or stinky key/attributes schemes where all attributes end up as TEXT, or a table with 200 columns, most of them being NULL for a given line). NoSQL guys say "hey just use NoSQL !". In a (common) case where the classes have some fields in common and othen searched, and that the DB needs to know about and access easily, those become columns, with indexes. Then the other fields which only occur in some derived class and are not very interesting to the DB get shoved into a hstore. The big bonus being that you use only one table, and the "extra" fields can still be accessed and indexed (but a little slower than a normal column). However I believe hstore can only store TEXT values... Could be interesting. Or not. -- 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] partition query on multiple cores
On Tue, May 10, 2011 at 12:22 PM, Shaun Thomas wrote: > On 05/10/2011 10:06 AM, Maciek Sakrejda wrote: > > I have 8-core server, I wanted to ask whether a query can be divided for >>> multiple processors or cores, if it could be what to do in postgresql >>> >> >> No, at this time (and for the foreseeable future), a single query will >> run on a single core. >> > > It can *kinda* be done. Take a look at GridSQL. It's really good for > splitting up reporting-like queries that benefit from parallel access of > large tables. It's not exactly Hadoop, but I ran a test on a single system > with two separate instances of PostgreSQL, and a single query over those two > nodes cut execution time in half. > > It's meant for server parallelism, so I wouldn't necessarily recommend > splitting your data up across nodes on the same server. But it seems to > deliver as promised when used in the right circumstances. > > Yes, GridSQL is useful even in multi-core scenarios on a single server for query parallelism. You can also use the same PostgreSQL instance (cluster), as the virtual node databases are named distinctly, which simplifies configuration. Mason
Re: [PERFORM] help speeding up a query in postgres 8.4.5
On Tue, May 10, 2011 at 2:20 PM, Maria L. Wilson wrote: > haven't tested a composite index > > invsensor is 2,003,980 rows and 219MB > granver is 5,138,730 rows and 556MB > the machine has 32G memory > seq_page_cost, random_page_costs & effective_cache_size are set to the > defaults (1,4, and 128MB) - looks like they could be bumped up. > Got any recommendations? Yeah, I'd try setting effective_cache_size=24GB, seq_page_cost=0.1, random_page_cost=0.1 and see if you get a better plan. If possible, can you post the EXPLAIN ANALYZE output with those settings for us? If that doesn't cause the planner to use the indexes, then I'd be suspicious that there is something wrong with those indexes that makes the planner think it *can't* use them. It would be helpful to see the EXPLAIN output after SET enable_seqscan=off. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] partition query on multiple cores
On Tue, May 10, 2011 at 2:57 PM, Tomas Vondra wrote: > Dne 10.5.2011 18:22, Shaun Thomas napsal(a): > > On 05/10/2011 10:06 AM, Maciek Sakrejda wrote: > > > >>> I have 8-core server, I wanted to ask whether a query can be divided > for > >>> multiple processors or cores, if it could be what to do in postgresql > >> > >> No, at this time (and for the foreseeable future), a single query will > >> run on a single core. > > > > It can *kinda* be done. Take a look at GridSQL. > > Or pgpool-II, that can give you something similar. > > http://pgpool.projects.postgresql.org/ > > Last time I tested parallelism in pgpool-II, I saw that if your query is fairly simple, pgpool-II will help. If it is more complex with joins and aggregates, GridSQL will typically outperform it. GridSQL pushes down joins as much as possible, minimizes row shipping, and parallelizes aggregates and grouping. Mason Sharp
Re: [PERFORM] help speeding up a query in postgres 8.4.5
Scott Marlowe writes: > On Tue, Apr 5, 2011 at 1:25 PM, Maria L. Wilson > wrote: > This bit: >> left outer join INVENTORY IV on GV.GRANULE_ID = IV.GRANULE_ID, INVSENSOR >> INVS > has both an explicit and an implicit join. This can constrain join > re-ordering in the planner. Can you change it to explicit joins only > and see if that helps? Since there's a WHERE constraint on IV, the outer join is going to be strength-reduced to an inner join (note the lack of any outer joins in the plan). So that isn't going to matter. AFAICS this is just plain an expensive query. The two filter constraints are not very selective, each passing more than a million rows up to the join. You can't expect to join millions of rows in no time flat. About all you can do is try to bump up work_mem enough that the join won't use temp files --- for something like this, that's likely to require a setting of hundreds of MB. I'm not sure whether Maria is using a version in which EXPLAIN ANALYZE will show whether a hash join was batched, but that's what I'd be looking at. 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] 'Interesting' prepared statement slowdown on large table join
On 05/11/2011 06:08 AM, Prodan, Andrei wrote: Index Scan using attr_name_value on big_table (cost=0.00..22.85 rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1) Holy inaccurate statistics, Batman! Try increasing your statistics target for attr_name and attr_value in your big table. I know you said you set it to 300 on party_id, but what happened here is that the optimizer thought this particular name/value combo in your big table would return less rows, and it was horribly, horribly wrong. You might think about bumping up your default_statistics_target anyway to prevent problems like this in general. But definitely increase it on those two columns and reanalyze. My guess is that your big_table is big enough that each analyze gets a different random sample of the various attr_name and attr_value combinations, so occasionally it'll get too few and start badly skewing query plans. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- 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] Postgres refusing to use >1 core
On 05/10/2011 11:26 PM, Scott Marlowe wrote: I.e. don't grab 1,000 rows and work on them on the client side and then insert data, do the data mangling in the query in the database. My experience has been that moving things like this into the database can result in performance gains of several factors, taking hour long processes and making them run in minutes. This is a problem I encounter constantly wherever I go. Programmer selects millions of rows from giant table. Programmer loops through results one by one doing some magic on them. Programmer submits queries back to the database. Even in batches, that's going to take ages. Databases are beasts at set-based operations. If the programmer can build a temp table of any kind and load that, they can turn their update/insert/whatever into a simple JOIN that runs several orders of magnitude faster. Going the route of parallelism will probably work too, but I doubt it's the right solution in this case. When there are tables with millions of rows involved, processing 111 per second is a bug. Even with ten perfectly balanced threads, 30 hours only becomes three. On decent hardware, you can probably drop, reload, and index the entire table faster than that. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- 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] 'Interesting' prepared statement slowdown on large table join
Shaun Thomas writes: > On 05/11/2011 06:08 AM, Prodan, Andrei wrote: >> Index Scan using attr_name_value on big_table (cost=0.00..22.85 >> rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1) > Holy inaccurate statistics, Batman! > Try increasing your statistics target for attr_name and attr_value in > your big table. Actually, the big problem here is probably not lack of statistics, but the insistence on using a parameterized prepared plan in the first place. If you're going to be doing queries where the number of selected rows varies that much, using a generic parameterized plan is just a recipe for shooting yourself in the foot. The planner cannot know what the actual search values will be, and thus has no way of adapting the plan based on how common those search values are. Having more stats won't help in that situation. Forget the prepared plan and just issue the query the old-fashioned way. I do suspect that the reason the plan is flipping back and forth is instability of the collected statistics, which might be improved by increasing the stats target, or then again maybe not. But that's really rather irrelevant. 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] Poor performance when joining against inherited tables
On Mon, Apr 11, 2011 at 4:11 PM, Lucas Madar wrote: > I have a database that contains many tables, each with some common > characteristics. For legacy reasons, they have to be implemented in a way so > that they are *all* searchable by an older identifier to find the newer > identifier. To do this, we've used table inheritance. > > Each entry has an id, as well as a legacyid1 and legacyid2. There's a master > table that the application uses, containing a base representation and common > characteristics: > > objects ( id, ... ) > item ( id, legacyid1, legacyid2 ) > | - itemXX > | - itemYY > > There is nothing at all in the item table, it's just used for inheritance. > However, weird things happen when this table is joined: > > EXPLAIN ANALYZE SELECT * FROM objects INNER JOIN item f USING ( id ); > > QUERY PLAN > > Hash Join (cost=457943.85..1185186.17 rows=8643757 width=506) > Hash Cond: (f.id = objects.id) > -> Append (cost=0.00..224458.57 rows=8643757 width=20) > -> Seq Scan on item f (cost=0.00..26.30 rows=1630 width=20) > -> Seq Scan on itemXX f (cost=0.00..1.90 rows=90 width=20) > -> Seq Scan on itemYY f (cost=0.00..7.66 rows=266 width=20) > -> Seq Scan on itemZZ f (cost=0.00..1.02 rows=2 width=20) > ... > -> Hash (cost=158447.49..158447.49 rows=3941949 width=490) > -> Seq Scan on objects (cost=0.00..158447.49 rows=3941949 > width=490) > > This scans everything over everything, and obviously takes forever (there > are millions of rows in the objects table, and tens of thousands in each > itemXX table). > > However, if I disable seqscan (set enable_seqscan=false), I get the > following plan: > > QUERY PLAN > > Hash Join (cost=10001298843.53..290002337961.71 rows=8643757 width=506) > Hash Cond: (f.id = objects.id) > -> Append (cost=100.00..29536334.43 rows=8643757 width=20) > -> Seq Scan on item f (cost=100.00..126.30 > rows=1630 width=20) > -> Index Scan using xxx_pkey on itemXX f (cost=0.00..10.60 rows=90 > width=20) > -> Index Scan using yyy_pkey on itemYY f (cost=0.00..25.24 > rows=266 width=20) > -> Index Scan using zzz_pkey on itemZZ f (cost=0.00..9.28 rows=2 > width=20) > ... > -> Hash (cost=999347.17..999347.17 rows=3941949 width=490) > -> Index Scan using objects_pkey on objects (cost=0.00..999347.17 > rows=3941949 width=490) > > This seems like a much more sensible query plan. I don't think so. Scanning the index to extract all the rows in a table is typically going to be a lot slower than a sequential scan. A more interesting question is why you're not getting a plan like this: Nested Loop -> Seq Scan on objects -> Append -> Index Scan using xxx_pkey on itemXX -> Index Scan using yyy_pkey on itemYY -> Index Scan using zzz_pkey on itemZZ > But it seems to think doing > a sequential scan on the *empty* item table is excessively expensive in this > case. > > Aside from enable_seqscan=false, is there any way I can make the query > planner not balk over doing a seqscan on an empty table? Why would you care? A sequential scan of an empty table is very fast. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Poor performance when joining against inherited tables
Robert Haas writes: > A more interesting question is why you're not getting a plan like this: > Nested Loop > -> Seq Scan on objects > -> Append >-> Index Scan using xxx_pkey on itemXX >-> Index Scan using yyy_pkey on itemYY >-> Index Scan using zzz_pkey on itemZZ Probably because there are 4 million rows in the objects table. Or maybe it's a pre-8.2 database and can't even generate such a plan. But if it did generate it, it would almost certainly have decided that this was more expensive than a hash or merge join. People have this weird idea that the existence of an index ought to make enormous joins free ... 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] Postgres refusing to use >1 core
This is a problem I encounter constantly wherever I go. Programmer selects millions of rows from giant table. Programmer loops through results one by one doing some magic on them. Programmer submits queries back to the database. Even in batches, that's going to take ages. Reminds me of a recent question on stackoverflow : http://stackoverflow.com/questions/5952020/how-to-optimize-painfully-slow-mysql-query-that-finds-correlations And the answer : http://stackoverflow.com/questions/5952020/how-to-optimize-painfully-slow-mysql-query-that-finds-correlations/5954041#5954041 OP was thinking "row-based", with subqueries in the role of "doing some magicm". Using a set-based solution with cascading WITH CTEs (and using the previous CTE as a source in the next one for aggregation) => 100x speedup ! -- 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] 'Interesting' prepared statement slowdown on large table join
On Wed, May 11, 2011 at 4:08 AM, Prodan, Andrei
wrote:
>
...
>
>
> The select is as follows:
> prepare ps(varchar,varchar,varchar) as select party.party_id from party,
> big_table where external_id = $1 and party.party_id = big_table.party_id
> and attr_name = $2 and attr_value = $3;
> PREPARE
> execute ps('13','GroupId','testshop');
>
> BAD PLAN:
> testdb=# explain analyze execute ps('13','GroupId','testshop');
> QUERY
...
> -> Index Scan using attr_name_value on big_table (cost=0.00..22.85
> rows=4 width=7) (actual time=0.176..757.646 rows=914786 loops=1)
> Index Cond: (((attr_name)::text = ($2)::text) AND
> ((attr_value)::text = ($3)::text))
So it expects 4 rows and finds 914786, essentially the whole table.
So that is bad. But what is it thinking during the GOOD PLAN state?
A possible way to get that information is to prepare a simpler
prepared statement that omits the join to party and explain analyze it
with the same params for attr_name and attr_value. If that gives you
the full table scan rather than index scan, then you can "set
enable_seqscan=off" try to force the index scan.
Cheers,
Jeff
--
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] Postgres refusing to use >1 core
Original message >Date: Wed, 11 May 2011 11:04:49 -0500 >From: pgsql-performance-ow...@postgresql.org (on behalf of Shaun Thomas >) >Subject: Re: [PERFORM] Postgres refusing to use >1 core >To: Scott Marlowe >Cc: Craig Ringer ,Aren Cambre >, > >On 05/10/2011 11:26 PM, Scott Marlowe wrote: > >> I.e. don't grab 1,000 rows and work on them on the client side and >> then insert data, do the data mangling in the query in the database. >> My experience has been that moving things like this into the database >> can result in performance gains of several factors, taking hour long >> processes and making them run in minutes. > >This is a problem I encounter constantly wherever I go. Programmer >selects millions of rows from giant table. Programmer loops through >results one by one doing some magic on them. Programmer submits queries >back to the database. Even in batches, that's going to take ages. > >Databases are beasts at set-based operations. If the programmer can >build a temp table of any kind and load that, they can turn their >update/insert/whatever into a simple JOIN that runs several orders of >magnitude faster. Going the route of parallelism will probably work too, >but I doubt it's the right solution in this case. > >When there are tables with millions of rows involved, processing 111 per >second is a bug. Even with ten perfectly balanced threads, 30 hours only >becomes three. On decent hardware, you can probably drop, reload, and >index the entire table faster than that. > >-- >Shaun Thomas >OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 >312-676-8870 >stho...@peak6.com > >__ > >See http://www.peak6.com/email_disclaimer.php >for terms and conditions related to this email > >-- >Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance So, the $64 question: how did you find an engagement where, to bend Shakespeare, "first thing we do, is kill all the coders" isn't required? This RBAR mentality, abetted by xml/NoSql/xBase, is utterly pervasive. They absolutely refuse to learn anything different from the COBOL/VSAM messes of their grandfathers; well modulo syntax, of course. The mere suggestion, in my experience, that doing things faster with fewer lines of code/statements in the engine is met with overt hostility. Regards, 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] Postgres refusing to use >1 core
On Wed, May 11, 2011 at 1:53 PM, wrote: > So, the $64 question: how did you find an engagement where, to bend > Shakespeare, "first thing we do, is kill all the coders" isn't required? > This RBAR mentality, abetted by xml/NoSql/xBase, is utterly pervasive. They > absolutely refuse to learn anything different from the COBOL/VSAM messes of > their grandfathers; well modulo syntax, of course. The mere suggestion, in > my experience, that doing things faster with fewer lines of code/statements > in the engine is met with overt hostility. It really depends. For a lot of development scaling to large numbers of users is never needed, and it's often more economical to develop quickly with a less efficient database layer. In my last job all our main development was against a large transactional / relational db. But some quick and dirty internal development used some very inefficient MVC methods but it only had to handle 45 users at a time, max, and that was 45 users who accessed the system a few minutes at a time. I've seen EVA systems that people tried to scale that were handling thousands of queries a second that when converted to real relational dbs needed dozens of queries a second to run, required a fraction of db horsepower, and could scale to the same number of users with only 1/10th to 1/100th the database underneath it. In those instances, you only have to show the much higher efficiency to the people who pay for the database servers. -- 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] Postgres refusing to use >1 core
On 05/11/2011 02:53 PM, gnuo...@rcn.com wrote: So, the $64 question: how did you find an engagement where, to bend Shakespeare, "first thing we do, is kill all the coders" isn't required? It's just one of those things you have to explain. Not just how to fix it, but *why* doing so fixes it. It's also not really a fair expectation in a lot of ways. Even when a coder uses all SQL, their inexperience in the engine can still ruin performance. We spend years getting to know PostgreSQL, or just general DB techniques. They do the same with coding. And unless they're a developer for a very graphics intensive project, they're probably not well acquainted with set theory. Just today, I took a query like this: UPDATE customer c SET c.login_counter = a.counter FROM (SELECT session_id, count(*) as counter FROM session WHERE date_created >= CURRENT_DATE GROUP BY session_id) a WHERE c.process_date = CURRENT_DATE AND c.customer_id = a.session_id And suggested this instead: CREATE TEMP TABLE tmp_login_counts AS SELECT session_id, count(1) AS counter FROM auth_token_arc WHERE date_created >= CURRENT_DATE GROUP BY session_id UPDATE reporting.customer c SET login_counter = a.counter FROM tmp_login_counts a WHERE c.process_date = CURRENT_DATE AND c.customer_id = a.session_id The original query, with our very large tables, ran for over *two hours* thanks to a nested loop iterating over the subquery. My replacement ran in roughly 30 seconds. If we were using a newer version of PG, we could have used a CTE. But do you get what I mean? Temp tables are a fairly common technique, but how would a coder know about CTEs? They're pretty new, even to *us*. We hold regular Lunch'n'Learns for our developers to teach them the good/bad of what they're doing, and that helps significantly. Even hours later, I see them using the techniques I showed them. The one I'm presenting soon is entitled '10 Ways to Ruin Performance' and they're all specific examples taken from day-to-day queries and jobs here, all from different categories of mistake. It's just a part of being a good DBA. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- 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] Postgres refusing to use >1 core
Original message >Date: Wed, 11 May 2011 17:04:50 -0500 >From: pgsql-performance-ow...@postgresql.org (on behalf of Shaun Thomas >) >Subject: Re: [PERFORM] Postgres refusing to use >1 core >To: >Cc: Scott Marlowe ,Craig Ringer >,Aren Cambre >, > >On 05/11/2011 02:53 PM, gnuo...@rcn.com wrote: > >> So, the $64 question: how did you find an engagement where, to bend >> Shakespeare, "first thing we do, is kill all the coders" isn't >> required? > >It's just one of those things you have to explain. Not just how to fix >it, but *why* doing so fixes it. It's also not really a fair expectation >in a lot of ways. Even when a coder uses all SQL, their inexperience in >the engine can still ruin performance. We spend years getting to know >PostgreSQL, or just general DB techniques. They do the same with coding. >And unless they're a developer for a very graphics intensive project, >they're probably not well acquainted with set theory. > >Just today, I took a query like this: > > UPDATE customer c > SET c.login_counter = a.counter > FROM (SELECT session_id, count(*) as counter > FROM session >WHERE date_created >= CURRENT_DATE >GROUP BY session_id) a >WHERE c.process_date = CURRENT_DATE > AND c.customer_id = a.session_id > >And suggested this instead: > > CREATE TEMP TABLE tmp_login_counts AS > SELECT session_id, count(1) AS counter > FROM auth_token_arc >WHERE date_created >= CURRENT_DATE >GROUP BY session_id > > UPDATE reporting.customer c > SET login_counter = a.counter > FROM tmp_login_counts a >WHERE c.process_date = CURRENT_DATE > AND c.customer_id = a.session_id > >The original query, with our very large tables, ran for over *two hours* >thanks to a nested loop iterating over the subquery. My replacement ran >in roughly 30 seconds. If we were using a newer version of PG, we could >have used a CTE. But do you get what I mean? Temp tables are a fairly >common technique, but how would a coder know about CTEs? They're pretty >new, even to *us*. > >We hold regular Lunch'n'Learns for our developers to teach them the >good/bad of what they're doing, and that helps significantly. Even hours >later, I see them using the techniques I showed them. The one I'm >presenting soon is entitled '10 Ways to Ruin Performance' and they're >all specific examples taken from day-to-day queries and jobs here, all >from different categories of mistake. It's just a part of being a good DBA. > >-- >Shaun Thomas >OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 >312-676-8870 >stho...@peak6.com > >__ > >See http://www.peak6.com/email_disclaimer.php >for terms and conditions related to this email > >-- >Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance You're (both) fortunate to have Suits and colleagues who are open to doing this A Better Way. Bless you. Regards, 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] Postgres refusing to use >1 core
On 5/11/11 3:04 PM, Shaun Thomas wrote: > The original query, with our very large tables, ran for over *two hours* > thanks to a nested loop iterating over the subquery. My replacement ran > in roughly 30 seconds. If we were using a newer version of PG, we could > have used a CTE. But do you get what I mean? Temp tables are a fairly > common technique, but how would a coder know about CTEs? They're pretty > new, even to *us*. For that matter, it would be even better if PostgreSQL realized that a materialize of the subquery was a better execution plan, and just did it for you. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Postgres refusing to use >1 core
> > I suspect your app is doing lots of tiny single-row queries instead of > efficiently batching things. It'll be wasting huge amounts of time > waiting for results. Even if every query is individually incredibly > fast, with the number of them you seem to be doing you'll lose a LOT of > time if you loop over lots of little SELECTs. > So here's what's going on. I have a table of about 12,000,000 traffic tickets written by the Texas Department of Public Safety (TxDPS). Each ticket has a route name and a reference marker. On Interstate highways, reference marker = mile post. On all other roads, from US highways down to Farm to Market roads, the reference marker is based on a grid superimposed over the state. Basically that reference marker increments as the road crosses a grid line, so unless the road is perfectly N-S or E-W, these reference markers are more than a mile apart. I have a separate table with data from the Texas Department of Transportation (TxDOT). It is a database of almost all the state's reference markers, along with latitude and longitude for each. I am trying to geolocate each ticket by correlating the ticket's route/reference marker to the same in the TxDOT database. And it's not straightforward for a few reasons: *1. TxDPS and TxDOT formats are different.* TxDPS uses 1-5 to denote route type. 1 = Interstate. 2 = US or any state highway except Farm to Market. 3 = Farm to Market, 4 = county road, 5 = local road. So if the route name is 0071 and route type is 2, it could mean US 71 or TX 71, both of which really exist in Texas but are on different parts of the state. I haven't proven it yet, but it is possible that no two routes of the same number are in the same county. You wouldn't find both TX 71 and US 71 in the same county. For now, I am looking up the TxDOT database based on route type, name, and county, and I may need to repeat the lookup until I get a match. In the above example, if the ticket is written for route_name = 0071, route_type = 2, and county = 206, then I need to do searches against the TxDOT database for: 1. rte_nm = 'US71' AND county_num='206' 2. rte_nm = 'SH71' AND county_num='206' 3. rte_nm = 'UA71' AND county_num='206' 4. rte_nm = 'UP71' AND county_num='206' 5. ... *2. Not TxDPS reference markers correspond to TxDOT reference markers.* Now, if I've matched a route, I have to find the reference marker. The TxDOT database is pretty good but not 100% complete, so some TxDPS tickets' reference markers may not exist in the TxDOT table. Plus, it's possible that some TxDPS tickets have the wrong marker. To compensate, I am looking for the closest reference marker along the route that is not more than 50 marker units away, either direction. I've again implemented that with multiple queries, where I don't stop until I find a match. Suppose I am searching for reference marker 256 on TX 71. The queries will be like this: 1. rte_nm = 'SH71' AND rm = '256' (base marker) 2. rte_nm = 'SH71' AND rm = '257' (+1) 3. rte_nm = 'SH71' AND rm = '255' (-1) 4. rte_nm = 'SH71' AND rm = '258' (+2) 5. rte_nm = 'SH71' AND rm = '254' (-2) 6. ... 7. rte_nm = 'SH71' AND rm = '306' (+50) 8. rte_nm = 'SH71' AND rm = '206' (-50) Assuming a matching route name was found in the prior step, the app will have 1 to 101 of these queries for each ticket. Assuming steps 1 and 2 above worked out, now I have a reference marker. So I write to a third table that has four columns: 1. *HA_Arrest_Key* (varchar(18) that refers back to the TxDPS tickets table 2. *gid* (integer that refers to the unique identity of the reference marker in the TxDOT table) 3. *distance* (integer that is the distance, in reference markers, between that noted in the TxDPS ticket and the nearest marker found in the TxDOT table) 4. *hasLatLong* (Boolean that is true if TxDPS also recorded latitude and longitude for the ticket, presumably from an in-car device. These don't appear to be that accurate, plus a substantial portion of tickets have no lat/long.) Right now, I am doing a separate INSERT for each of the 12,000,000 rows inserted into this table. I guess the app is chatty like you suggest? HOWEVER, if I am reading system activity correctly, the master thread that is going through the 12,000,000 tickets appears to have its own Postgres process, and based on how quickly RAM usage initially shoots up the first ~60 seconds or so the app runs, it may be reading all these rows into memory. But I am consulting with Npgsql developers separately to make sure I am really understanding correctly. They suspect that the PLINQ stuff (basically "multithreading in a can") may not be dispatching threads as expected because it may be misreading things. By using a producer/consumer model like that you can ensure that thread > 1 is always talking to the database, keeping Pg busy, and thread 2 is > always working the CPUs. Thanks for the example and illustrati
Re: [PERFORM] Postgres refusing to use >1 core
> > > Using one thread, the app can do about 111 rows per second, and it's > > only exercising 1.5 of 8 CPU cores while doing this. 12,000,000 rows / > > 111 rows per second ~= 30 hours. > > I don't know how I missed that. You ARE maxing out one cpu core, so > you're quite right that you need more threads unless you can make your > single worker more efficient. > And the problem is my app already has between 20 and 30 threads. Something about C#'s PLINQ may not be working as intended... Aren
Re: [PERFORM] Postgres refusing to use >1 core
> > > I suspect your app is doing lots of tiny single-row queries instead of >> efficiently batching things. It'll be wasting huge amounts of time >> waiting for results. Even if every query is individually incredibly >> fast, with the number of them you seem to be doing you'll lose a LOT of >> time if you loop over lots of little SELECTs. >> > > Using unix sockets, you can expect about 10-20.000 queries/s on small > simple selects per core, which is quite a feat. TCP adds overhead, so it's > slower. Over a network, add ping time. > I'm talking to a Postgres on localhost, so in theory, I ought to be getting really good throughput, but again, the problem may be with the way C#'s PLINQ "multithreading in a can" is managing things. Aren
Re: [PERFORM] HashJoin order, hash the large or small table? Postgres likes to hash the big one, why?
On Wed, Apr 13, 2011 at 1:22 PM, Scott Carey wrote: > A pathological skew case (all relations with the same key), should be > _cheaper_ to probe. There should be only _one_ entry in the hash (for > the one key), and that entry will be a list of all relations matching the > key. Therefore, hash probes will either instantly fail to match on an > empty bucket, fail to match the one key with one compare, or match the one > key and join on the matching list. > > In particular for anti-join, high skew should be the best case scenario. I think this argument may hold some water for an anti-join, and maybe for a semi-join, but it sure doesn't seem right for any kind of join that has to iterate over all matches (rather than just the first one); that is, inner, left, right, or full. > A hash structure that allows multiple entries per key is inappropriate for > skewed data, because it is not O(n). One that has one entry per key > remains O(n) for all skew. Furthermore, the hash buckets and # of entries > is proportional to n_distinct in this case, and smaller and more cache and > memory friendly to probe. I don't think this argument is right. The hash table is sized for a load factor significantly less than one, so if there are multiple entries in a bucket, it is fairly likely that they are all for the same key. Granted, we have to double-check the keys to figure that out; but I believe that the data structure you are proposing would require similar comparisons. The only difference is that they'd be required when building the hash table, rather than when probing it. > You can put either relation on the outside with an anti-join, but would > need a different algorithm and cost estimator if done the other way > around. Construct a hash on the join key, that keeps a list of relations > per key, iterate over the other relation, and remove the key and > corresponding list from the hash when there is a match, when complete the > remaining items in the hash are the result of the join (also already > grouped by the key). It could be terminated early if all entries are > removed. > This would be useful if the hash was small, the other side of the hash too > large to fit in memory, and alternative was a massive sort on the other > relation. This would be a nice extension of commit f4e4b3274317d9ce30de7e7e5b04dece7c4e1791. > Does the hash cost estimator bias towards smaller hashes due to hash probe > cost increasing with hash size due to processor caching effects? Its not > quite O(n) due to caching effects. I don't think we account for that (and I'm not convinced we need to). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] DBT-5 & Postgres 9.0.3
On Tue, Apr 12, 2011 at 3:51 AM, Sethu Prasad wrote: > Anyone lucky to have dbt5 run for PostgreSQL 9.0.3?! > > I am trying on Novell SuSE Linux Enterprise Server 11 SP1 x86_64 with a > virtual machine and bit hard with no success run yet. If you can help me > with any docs will be more of a support. What's going wrong for you? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Postgres refusing to use >1 core
On Wed, May 11, 2011 at 9:20 PM, Aren Cambre wrote: >> Using unix sockets, you can expect about 10-20.000 queries/s on small >> simple selects per core, which is quite a feat. TCP adds overhead, so it's >> slower. Over a network, add ping time. > > I'm talking to a Postgres on localhost, so in theory, I ought to be getting > really good throughput, but again, the problem may be with the way C#'s > PLINQ "multithreading in a can" is managing things. local tcp is gonna be slower not faster than unix sockets, not faster. But the big issue is that you need to exlpore doing the work in a large set not iteratively. Operations on sets are often much faster in aggregate. -- 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] Checkpoint execution overrun impact?
On Fri, Apr 22, 2011 at 5:21 AM, drvillo wrote: > -given the configuration attached (which is basically a vanilla one) and the > number of buffers written at each execution, are these execution times > normal or above average? They seem fine. Remember that the write is deliberately spread out; it's not as if the system couldn't write out 130-160 8k blocks in less than 30 s. > -in the case of the execution that overruns past the timeout, what are the > implications wrt the client application? Not sure what you are referring to here. > -AFAIU client connections are basically stalled during checkpoints. Is it > reasonable to infer that the fact that the application blocking on a > getConnection() might be related to checkpoints being executed? > -considering some tuning on the PG side, should I try increasing > checkpoint_timeout and rising checkpoint_completion_target to lessen the > impact of IO on the client or should I shorten the period so there's less > stuff to write? from the number of buffers written on average I'd assume the > first option is the one to go for but I might miss some bit of reasoning > here... I'm a bit puzzled by all of this because the logs you posted seem to reflect a system under very light load. Each checkpoint is writing no more than 4% of shared_buffers and the sync phases are generally completing in less than one second. I don't see why that would be causing stalls. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] tuning on ec2
On Tue, Apr 26, 2011 at 11:15 AM, Joel Reymont wrote: > I'm running pgsql on an m1.large EC2 instance with 7.5gb available memory. > > The free command shows 7gb of free+cached. My understand from the docs is > that I should dedicate 1.75gb to shared_buffers (25%) and set > effective_cache_size to 7gb. Sounds like a reasonable starting point. You could certainly fiddle around a bit - especially with shared_buffers - to see if some other setting works better, but that should be in the ballpark. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Postgres refusing to use >1 core
On 5/11/2011 9:17 PM, Aren Cambre wrote: So here's what's going on. If I were doing this, considering the small size of the data set, I'd read all the data into memory. Process it entirely in memory (with threads to saturate all the processors you have). Then write the results to the DB. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
