Re: [PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?
Thanks for your suggestions. I had pretty much given up on this idea. At first, I had thought there would only be 2 or 3 different constraint cases to consider. I had thought of using distinct credentials for my connection and using RLS to give different cuts on the same table. The different policies could be established in advance and never touched. But then it became clear that I actually would need a very large number of different restrictions on the tables - too many to create in advance. At this point it's easiest to apply constraints on each select rather than apply a policy every time. Thanks, Joe On 10/17/2017 03:06 PM, Tom Lane wrote: Tomas Vondra <tomas.von...@2ndquadrant.com> writes: On 10/17/2017 10:44 PM, Joe Carlson wrote: What I was wondering is what is the performance differences between a row level security implementation: ... and an implementation where I add on the constraints as part of each select statement: The main point of the RLS is enforcing an order in which the conditions are evaluated. Yeah. Because of that, I would *not* recommend RLS if you can equally well stick the equivalent conditions into your queries. There is way too much risk of taking a serious performance hit due to a bad plan. An alternative you might consider, if simplifying the input queries is useful, is to put the fixed conditions into a view and query the view instead. That way there's not an enforced evaluation order. 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
[PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?
Hello. I have not used row level security policies in the past but am considering using them for a project in which I would like to restrict the set returned in a query based on specific fields. This is more as a convenience issue (for me) rather than a security issue. What I was wondering is what is the performance differences between a row level security implementation: CREATE POLICY ON TO USING (=ANY()); DROP POLICY and an implementation where I add on the constraints as part of each select statement: SELECT FROM WHERE AND =ANY() In my (admittedly small) number of EXPLAINs I've looked at, it appears that the policy logic is added to the SELECT statement as a constraint. So I would not expect any fundamental performance difference in the 2 different forms. Is this true? Or is there some extra behind-the-scenes things to be aware of? Can there be excessive overhead from the CREATE/DROP POLICY statements? Thanks, Joe -- 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] Improving PostgreSQL insert performance
On 06/10/2017 07:32 PM, Alvaro Herrera wrote:
> Frits Jalvingh wrote:
>
>> So, I am still very interested in getting normal inserts faster, because
>> that will gain speed for all work.. If Oracle can do it, and Postgres is
>> able to insert fast with copy- where lies the bottleneck with the insert
>> command? There seems to be quite a performance hit with the JDBC driver
>> itself (as the stored procedure is a lot faster), so I can look into that.
>> But even after that there is quite a gap..
>
> Did you try inserting multiple tuples in one command? Something like
> INSERT INTO .. VALUES ('col1', 'col2'), ('col1', 'col2'), ('col1', 'col2')
> It's supposed to be faster than single-row inserts, though I don't
> know by how much.
When I did the testing of the patch originally I saw significant
improvements, e.g. 8x in early versions. The thread is here:
https://www.postgresql.org/message-id/flat/44C4451A.4010906%40joeconway.com#44c4451a.4010...@joeconway.com
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] MYSQL Stats
My Apologies , was in the wrong email/forum, please disregard my email! From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Joe Proietti Sent: Friday, September 30, 2016 8:03 AM To: Jake Nielsen <jake.k.niel...@gmail.com>; Tom Lane <t...@sss.pgh.pa.us> Cc: pgsql-performance@postgresql.org Subject: [PERFORM] MYSQL Stats Hi, I am relatively new to MYSQL and not really sure I am in the right forum for this. I have a situation which I am not understanding. I am performing a simple query : Select * from tableA Where date >= ‘2016’06-01’ And date < ‘2016-07-01’ Index is on date Query returns 6271 rows When doing explain on the same query The rows column shows 11462, nearly twice the amount (this result is consistent on most all tables) When selecting count from the table , returns 2668664 When selecting from information_schema.tables table_rows column shows 2459114 While this is indicative of out dated statistics Have done an analyze table but no changes. Thanks, Joe From: pgsql-performance-ow...@postgresql.org<mailto:pgsql-performance-ow...@postgresql.org> [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Jake Nielsen Sent: Wednesday, September 28, 2016 2:11 PM To: Tom Lane <t...@sss.pgh.pa.us<mailto:t...@sss.pgh.pa.us>> Cc: pgsql-performance@postgresql.org<mailto:pgsql-performance@postgresql.org> Subject: Re: [PERFORM] Unexpected expensive index scan On Wed, Sep 28, 2016 at 6:04 AM, Tom Lane <t...@sss.pgh.pa.us<mailto:t...@sss.pgh.pa.us>> wrote: [ Please don't re-quote the entire damn thread in each followup. Have some respect for your readers' time, and assume that they have already seen the previous traffic, or could go look it up if they haven't. The point of quoting at all is just to quickly remind people where we are in the discussion. ] Sorry, understood. If you say "well yeah, but it seems to perform fine when I force it to use that index anyway", the answer may be that you need to adjust random_page_cost. The default value is OK for tables that are mostly sitting on spinning rust, but if your database is RAM-resident or SSD-resident you probably want a value closer to 1. Ahhh, this could absolutely be the key right here. I could totally see why it would make sense for the planner to do what it's doing given that it's weighting sequential access more favorably than random access. Beautiful! After changing the random_page_cost to 1.0 the original query went from ~3.5s to ~35ms. This is exactly the kind of insight I was fishing for in the original post. I'll keep in mind that the query planner is very tunable and has these sorts of hardware-related trade-offs in the future. I can't thank you enough! Cheers!
[PERFORM] MYSQL Stats
Hi, I am relatively new to MYSQL and not really sure I am in the right forum for this. I have a situation which I am not understanding. I am performing a simple query : Select * from tableA Where date >= ‘2016’06-01’ And date < ‘2016-07-01’ Index is on date Query returns 6271 rows When doing explain on the same query The rows column shows 11462, nearly twice the amount (this result is consistent on most all tables) When selecting count from the table , returns 2668664 When selecting from information_schema.tables table_rows column shows 2459114 While this is indicative of out dated statistics Have done an analyze table but no changes. Thanks, Joe From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Jake Nielsen Sent: Wednesday, September 28, 2016 2:11 PM To: Tom Lane <t...@sss.pgh.pa.us> Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Unexpected expensive index scan On Wed, Sep 28, 2016 at 6:04 AM, Tom Lane <t...@sss.pgh.pa.us<mailto:t...@sss.pgh.pa.us>> wrote: [ Please don't re-quote the entire damn thread in each followup. Have some respect for your readers' time, and assume that they have already seen the previous traffic, or could go look it up if they haven't. The point of quoting at all is just to quickly remind people where we are in the discussion. ] Sorry, understood. If you say "well yeah, but it seems to perform fine when I force it to use that index anyway", the answer may be that you need to adjust random_page_cost. The default value is OK for tables that are mostly sitting on spinning rust, but if your database is RAM-resident or SSD-resident you probably want a value closer to 1. Ahhh, this could absolutely be the key right here. I could totally see why it would make sense for the planner to do what it's doing given that it's weighting sequential access more favorably than random access. Beautiful! After changing the random_page_cost to 1.0 the original query went from ~3.5s to ~35ms. This is exactly the kind of insight I was fishing for in the original post. I'll keep in mind that the query planner is very tunable and has these sorts of hardware-related trade-offs in the future. I can't thank you enough! Cheers!
Re: [PERFORM] Odd behavior with indices
Here's the distribution of parameter_id's
select count(parameter_id), parameter_id from datavalue group by parameter_id
88169 142889171 815805 178570124257262 213947049 151225902
24091090 3103877 10633764 11994442 1849232 2014935 4563638
132955919 7
On Fri, Feb 26, 2016 at 2:02 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Fri, Feb 26, 2016 at 12:43 PM, joe meiring <josephmeir...@gmail.com>
> wrote:
>
>> Also available on S.O.:
>>
>>
>> http://stackoverflow.com/questions/35658238/postgres-odd-behavior-with-indices
>>
>> I've got a datavalue table with ~200M rows or so, with indices on both
>> site_id and parameter_id. I need to execute queries like "return all
>> sites with data" and "return all parameters with data". The site table
>> has only 200 rows or so, and the parameter table has only 100 or so rows.
>>
>> The site query is fast and uses the index:
>>
>> EXPLAIN ANALYZEselect *from sitewhere exists (
>> select 1 from datavalue
>> where datavalue.site_id = site.id limit 1);
>>
>> Seq Scan on site (cost=0.00..64.47 rows=64 width=113) (actual
>> time=0.046..1.106 rows=89 loops=1)
>> Filter: (SubPlan 1)
>> Rows Removed by Filter: 39
>> SubPlan 1
>> -> Limit (cost=0.44..0.47 rows=1 width=0) (actual time=0.008..0.008
>> rows=1 loops=128)
>> -> Index Only Scan using ix_datavalue_site_id on datavalue
>> (cost=0.44..8142.71 rows=248930 width=0) (actual time=0.008..0.008 rows=1
>> loops=128)
>> Index Cond: (site_id = site.id)
>> Heap Fetches: 0
>> Planning time: 0.361 ms
>> Execution time: 1.149 ms
>>
>> The same query for parameters is rather slow and does NOT use the index:
>>
>> EXPLAIN ANALYZEselect *from parameterwhere exists (
>> select 1 from datavalue
>> where datavalue.parameter_id = parameter.id limit 1);
>>
>> Seq Scan on parameter (cost=0.00..20.50 rows=15 width=2648) (actual
>> time=2895.972..21331.701 rows=15 loops=1)
>> Filter: (SubPlan 1)
>> Rows Removed by Filter: 6
>> SubPlan 1
>> -> Limit (cost=0.00..0.34 rows=1 width=0) (actual
>> time=1015.790..1015.790 rows=1 loops=21)
>> -> Seq Scan on datavalue (cost=0.00..502127.10 rows=1476987
>> width=0) (actual time=1015.786..1015.786 rows=1 loops=21)
>> Filter: (parameter_id = parameter.id)
>> Rows Removed by Filter: 7739355
>> Planning time: 0.123 ms
>> Execution time: 21331.736 ms
>>
>> What the deuce is going on here? Alternatively, whats a good way to do
>> this?
>>
>> Any help/guidance appreciated!
>>
>>
>>
>> Some of the table description:
>>
>> \d datavalue
>>
>> id BIGINT DEFAULT nextval('datavalue_id_seq'::regclass) NOT NULL,
>> value DOUBLE PRECISION NOT NULL,
>> site_id INTEGER NOT NULL,
>> parameter_id INTEGER NOT NULL,
>> deployment_id INTEGER,
>> instrument_id INTEGER,
>> invalid BOOLEAN,
>> Indexes:
>> "datavalue_pkey" PRIMARY KEY, btree (id)
>> "datavalue_datetime_utc_site_id_parameter_id_instrument_id_key" UNIQUE
>> CONSTRAINT, btree (datetime_utc, site_id, parameter_id, instrument_id)
>> "ix_datavalue_instrument_id" btree (instrument_id)
>> "ix_datavalue_parameter_id" btree (parameter_id)
>> "ix_datavalue_site_id" btree (site_id)
>> "tmp_idx" btree (site_id, datetime_utc)
>> Foreign-key constraints:
>> "datavalue_instrument_id_fkey" FOREIGN KEY (instrument_id) REFERENCES
>> instrument(id) ON UPDATE CASCADE ON DELETE CASCADE
>> "datavalue_parameter_id_fkey" FOREIGN KEY (parameter_id) REFERENCES
>> parameter(id) ON UPDATE CASCADE ON DELETE CASCADE
>> "datavalue_site_id_fkey" FOREIGN KEY (site_id) REFERENCES
>> coastal.site(id) ON UPDATE CASCADE ON DELETE CASCADE
>> "datavalue_statistic_type_id_fkey"
>>
>>
>> I'm not great with the details but the short answer - aside from the
> fact that you should consider increasing the statistics on these columns -
> is that at a certain point querying the index and then subsequently
> checking the table for visibility is more expensive than simply scanning
> and then discarding the extra rows.
>
> The fact that you could perform an INDEX ONLY scan in the first query
> makes that cost go away since no subsequent heap check is required. In the
> parameters query the planner thinks it needs 1.5 million of the rows and
> will have to check each of them for visibility. It decided that scanning
> the entire table was more efficient.
>
> The LIMIT 1 in both queries should not be necessary. The planner is smart
> enough to stop once it finds what it is looking for. In fact the LIMIT's
> presence may be a contributing factor...but I cannot say for sure.
>
> A better query seems like it would be:
>
> WITH active_sites AS (
> SELECT DISTINCT site_id FROM datavalues;
> )
> SELECT *
> FROM sites
> JOIN active_sites USING (site_id);
>
> David J.
>
[PERFORM] Odd behavior with indices
Also available on S.O.:
http://stackoverflow.com/questions/35658238/postgres-odd-behavior-with-indices
I've got a datavalue table with ~200M rows or so, with indices on both
site_id and parameter_id. I need to execute queries like "return all sites
with data" and "return all parameters with data". The site table has only
200 rows or so, and the parameter table has only 100 or so rows.
The site query is fast and uses the index:
EXPLAIN ANALYZEselect *from sitewhere exists (
select 1 from datavalue
where datavalue.site_id = site.id limit 1);
Seq Scan on site (cost=0.00..64.47 rows=64 width=113) (actual
time=0.046..1.106 rows=89 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 39
SubPlan 1
-> Limit (cost=0.44..0.47 rows=1 width=0) (actual
time=0.008..0.008 rows=1 loops=128)
-> Index Only Scan using ix_datavalue_site_id on datavalue
(cost=0.44..8142.71 rows=248930 width=0) (actual time=0.008..0.008
rows=1 loops=128)
Index Cond: (site_id = site.id)
Heap Fetches: 0
Planning time: 0.361 ms
Execution time: 1.149 ms
The same query for parameters is rather slow and does NOT use the index:
EXPLAIN ANALYZEselect *from parameterwhere exists (
select 1 from datavalue
where datavalue.parameter_id = parameter.id limit 1);
Seq Scan on parameter (cost=0.00..20.50 rows=15 width=2648) (actual
time=2895.972..21331.701 rows=15 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 6
SubPlan 1
-> Limit (cost=0.00..0.34 rows=1 width=0) (actual
time=1015.790..1015.790 rows=1 loops=21)
-> Seq Scan on datavalue (cost=0.00..502127.10
rows=1476987 width=0) (actual time=1015.786..1015.786 rows=1 loops=21)
Filter: (parameter_id = parameter.id)
Rows Removed by Filter: 7739355
Planning time: 0.123 ms
Execution time: 21331.736 ms
What the deuce is going on here? Alternatively, whats a good way to do this?
Any help/guidance appreciated!
Some of the table description:
\d datavalue
id BIGINT DEFAULT nextval('datavalue_id_seq'::regclass) NOT NULL,
value DOUBLE PRECISION NOT NULL,
site_id INTEGER NOT NULL,
parameter_id INTEGER NOT NULL,
deployment_id INTEGER,
instrument_id INTEGER,
invalid BOOLEAN,
Indexes:
"datavalue_pkey" PRIMARY KEY, btree (id)
"datavalue_datetime_utc_site_id_parameter_id_instrument_id_key"
UNIQUE CONSTRAINT, btree (datetime_utc, site_id, parameter_id,
instrument_id)
"ix_datavalue_instrument_id" btree (instrument_id)
"ix_datavalue_parameter_id" btree (parameter_id)
"ix_datavalue_site_id" btree (site_id)
"tmp_idx" btree (site_id, datetime_utc)
Foreign-key constraints:
"datavalue_instrument_id_fkey" FOREIGN KEY (instrument_id)
REFERENCES instrument(id) ON UPDATE CASCADE ON DELETE CASCADE
"datavalue_parameter_id_fkey" FOREIGN KEY (parameter_id)
REFERENCES parameter(id) ON UPDATE CASCADE ON DELETE CASCADE
"datavalue_site_id_fkey" FOREIGN KEY (site_id) REFERENCES
coastal.site(id) ON UPDATE CASCADE ON DELETE CASCADE
"datavalue_statistic_type_id_fkey"
Re: [PERFORM] querying jsonb for arrays inside a hash
You're right, brain fart. Nevermind! :)
On Sat, Nov 7, 2015 at 4:00 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Joe Van Dyk <j...@tanga.com> writes:
> > I noticed that querying for
> >product_attributes @> '{"upsell":["true"]}'
> > is much slower than querying for
> >product_attributes @> '{"upsell": 1}'
>
> > Is that expected?
>
> Your EXPLAIN results say that the first query matched 135843 rows and the
> second one none at all, so a significant variation in runtime doesn't seem
> that surprising to me ...
>
> regards, tom lane
>
[PERFORM] querying jsonb for arrays inside a hash
I noticed that querying for
product_attributes @> '{"upsell":["true"]}'
is much slower than querying for
product_attributes @> '{"upsell": 1}'
Is that expected? I have a gin index on product_attributes. I'm using 9.4.1.
explain analyze
select count(*) from products where product_attributes @>
'{"upsell":["true"]}' and site_id = '1';
QUERY
PLAN
Aggregate (cost=1382.92..1382.93 rows=1 width=0) (actual
time=410.498..410.499 rows=1 loops=1)
-> Bitmap Heap Scan on products (cost=46.94..1382.52 rows=157 width=0)
(actual time=31.747..363.145 rows=45165 loops=1)
Recheck Cond: (product_attributes @> '{"upsell": ["true"]}'::jsonb)
Filter: (site_id = '1'::text)
Rows Removed by Filter: 90330
Heap Blocks: exact=12740
-> Bitmap Index Scan on products_attributes_idx
(cost=0.00..46.90 rows=386 width=0) (actual time=29.585..29.585
rows=135843 loops=1)
Index Cond: (product_attributes @> '{"upsell":
["true"]}'::jsonb)
Planning time: 0.851 ms
Execution time: 410.825 ms
(10 rows)
Time: 413.172 ms
explain analyze
select count(*) from products where product_attributes @> '{"upsell": 1}'
and site_id = '1';
QUERY PLAN
-
Aggregate (cost=1382.92..1382.93 rows=1 width=0) (actual
time=0.110..0.111 rows=1 loops=1)
-> Bitmap Heap Scan on products (cost=46.94..1382.52 rows=157 width=0)
(actual time=0.107..0.107 rows=0 loops=1)
Recheck Cond: (product_attributes @> '{"upsell": 1}'::jsonb)
Filter: (site_id = '1'::text)
-> Bitmap Index Scan on products_attributes_idx
(cost=0.00..46.90 rows=386 width=0) (actual time=0.105..0.105 rows=0
loops=1)
Index Cond: (product_attributes @> '{"upsell": 1}'::jsonb)
Planning time: 0.091 ms
Execution time: 0.140 ms
(8 rows)
Time: 1.264 ms
Re: [PERFORM] Do work_mem and shared buffers have 1g or 2g limit on 64 bit linux?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/13/2015 10:43 AM, Joshua D. Drake wrote: On 06/13/2015 10:27 AM, Kaijiang Chen wrote: Hi, I am using postgresql 9.2.10 on centos 6.2, 64 bit version. The server has 512 GB mem. The jobs are mainly OLAP like. So I need larger work_mem and shared buffers. From the source code, there is a constant MaxAllocSize==1GB. So, I wonder whether work_mem and shared buffers can exceed 2GB in the 64 bit Linux server? Work_mem IIRC can go past 2GB but has never been proven to be effective after that. It does depend on the version you are running. Starting with 9.4 work_mem and maintenance_work_mem can be usefully set to 2 GB. I've done testing with index creation, for example, and you can set maintenance_work_mem high enough (obviously depending on how much RAM you have and how big the sort memory footprint is) to get the entire sort to happen in memory without spilling to disk. In some of those cases I saw time required to create indexes drop by a factor of 3 or more...YMMV. I have not tested with large work_mem to encourage hash aggregate plans, but I suspect there is a lot to be gained there as well. HTH, Joe - -- Joe Conway -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.22 (GNU/Linux) iQIcBAEBAgAGBQJVfHITAAoJEDfy90M199hlGvcP/ijyCsXnWZAeZSUAW4qb20YJ AHKn0Gl8D9mH9cfPfJeCO+60dcWINzUE6l7qOWWN8JtT6pgbRPGvQsCkx9xRzq+V aXv/d/r5wW4g06krcootliQJ1TWnLbPBCQiqmI27HSvnEgDKmJ3kOdDji1FMrcdm tuBdNxppoSx0sIFMJ6Xe/brt9O8wG/a81E0lAnsyh2nncaaXba96ldIhUbKvU0ie 7In88Rn1UYZDXnoQEtZLmF6ArdTN5dQZkyEZvNKR0CHrPVddVYXP/gMWm/XwnOu6 k3Rg/evCY2yCyxveuQXU5AZhDFXB/VLoOQoZ5MhLxnoLCNDJrqJzymE1shsgIIji i8PfXkKU92/N2kxfDBGwO0LdBpjZzzgg8zMHBsk8FIpXiJvVQKtAfCxYpYkSaL8y L0g4Qi16s2/fFZcn1ORH23BaBlcmS1cnRWWyx/amyqPHX0v4XZvp3/kSj2jCSw+E V7HD8qLut4rEAxwA5AGCy+9iugZp8DKQUUNiXOYbuysAdjceAa9LzPE0BbB4kuFC OfOOjRstr97RyDKwRHjfGs2EnJSENGGcPdGz2HYgup0d4DlIctKww8xeSo55Khp/ HhBjtk7rpnqqEmEeA8+N8w5Z60x4mK900Anr1xhX2x4ETTIG2g9mYkEEZL/OZRUC lihTXLyUhvd57/v7li5p =s0U8 -END PGP SIGNATURE- -- 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] Query performance
On Sat, Jan 24, 2015 at 11:14 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
2015-01-25 7:38 GMT+01:00 Joe Van Dyk j...@tanga.com:
On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hi
this plan looks well
Regards
Pavel
Here's one that's not quite as well: http://explain.depesz.com/s/SgT
I see a possible issue
(product_id '81716'::citext) .. this operation is CPU expensive and
maybe nonsense
product_id should be integer -- and if it isn't - it should not be on 4M
rows extremly fast - mainly on citext
try to force a opposite cast - you will safe a case insensitive text
comparation
product_id::int 81716
It might not always be an integer, just happens to be so here. Should I try
text instead? I don't have to have the case-insensitive matching.
Joe
Regards
Pavel
Joe
2015-01-25 6:45 GMT+01:00 Joe Van Dyk j...@tanga.com:
Oops, didn't run vacuum analyze after deleting the events. Here is
another 'explain analyze': http://explain.depesz.com/s/AviN
On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk j...@tanga.com wrote:
On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk j...@tanga.com wrote:
I have an events table that records page views and purchases (type =
'viewed' or type='purchased'). I have a query that figures out people
who
bought/viewed this also bought/viewed that.
It worked fine, taking about 0.1 seconds to complete, until a few
hours ago when it started taking hours to complete. Vacuum/analyze didn't
help. Turned out there was one session_id that had 400k rows in the
system. Deleting that made the query performant again.
Is there anything I can do to make the query work better in cases
like that? Missing index, or better query?
This is on 9.3.5.
The below is reproduced at the following URL if it's not formatted
correctly in the email.
https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt
explain select
e1.product_id,
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503' and
e1.product_id != e2.product_id
group by e1.product_id, e2.product_id, e2.site_id;
QUERY PLAN
GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
- Sort (cost=828395.67..840117.89 rows=465 width=19)
Sort Key: e1.product_id, e2.product_id, e2.site_id
- Nested Loop (cost=11.85..20371.14 rows=465 width=19)
- Bitmap Heap Scan on events e1 (cost=11.29..1404.31
rows=369 width=49)
Recheck Cond: (product_id = '82503'::citext)
- Bitmap Index Scan on
events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
Index Cond: (product_id = '82503'::citext)
- Index Scan using
events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28
rows=12 width=51)
Index Cond: ((session_id = e1.session_id) AND (type
= e1.type))
Filter: (e1.product_id product_id)
(11 rows)
recommender_production= \d events
Table public.events
Column| Type | Modifiers
-+--+-
id | bigint | not null default
nextval('events_id_seq'::regclass)
user_id | citext |
session_id | citext | not null
product_id | citext | not null
site_id | citext | not null
type| text | not null
happened_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null
Indexes:
events_pkey PRIMARY KEY, btree (id)
events_product_id_site_id_idx btree (product_id, site_id)
events_session_id_type_product_id_idx btree (session_id, type,
product_id)
Check constraints:
events_session_id_check CHECK (length(session_id::text) 255)
events_type_check CHECK (type = ANY (ARRAY['purchased'::text,
'viewed'::text]))
events_user_id_check CHECK (length(user_id::text) 255)
After removing the session with 400k events, I was able to do an
explain analyze, here is one of them:
http://explain.depesz.com/s/PFNk
Re: [PERFORM] Query performance
Oops, didn't run vacuum analyze after deleting the events. Here is another
'explain analyze': http://explain.depesz.com/s/AviN
On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk j...@tanga.com wrote:
On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk j...@tanga.com wrote:
I have an events table that records page views and purchases (type =
'viewed' or type='purchased'). I have a query that figures out people who
bought/viewed this also bought/viewed that.
It worked fine, taking about 0.1 seconds to complete, until a few hours
ago when it started taking hours to complete. Vacuum/analyze didn't help.
Turned out there was one session_id that had 400k rows in the system.
Deleting that made the query performant again.
Is there anything I can do to make the query work better in cases like
that? Missing index, or better query?
This is on 9.3.5.
The below is reproduced at the following URL if it's not formatted
correctly in the email.
https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt
explain select
e1.product_id,
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503' and
e1.product_id != e2.product_id
group by e1.product_id, e2.product_id, e2.site_id;
QUERY PLAN
GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
- Sort (cost=828395.67..840117.89 rows=465 width=19)
Sort Key: e1.product_id, e2.product_id, e2.site_id
- Nested Loop (cost=11.85..20371.14 rows=465 width=19)
- Bitmap Heap Scan on events e1 (cost=11.29..1404.31
rows=369 width=49)
Recheck Cond: (product_id = '82503'::citext)
- Bitmap Index Scan on events_product_id_site_id_idx
(cost=0.00..11.20 rows=369 width=0)
Index Cond: (product_id = '82503'::citext)
- Index Scan using events_session_id_type_product_id_idx on
events e2 (cost=0.56..51.28 rows=12 width=51)
Index Cond: ((session_id = e1.session_id) AND (type =
e1.type))
Filter: (e1.product_id product_id)
(11 rows)
recommender_production= \d events
Table public.events
Column| Type | Modifiers
-+--+-
id | bigint | not null default
nextval('events_id_seq'::regclass)
user_id | citext |
session_id | citext | not null
product_id | citext | not null
site_id | citext | not null
type| text | not null
happened_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null
Indexes:
events_pkey PRIMARY KEY, btree (id)
events_product_id_site_id_idx btree (product_id, site_id)
events_session_id_type_product_id_idx btree (session_id, type,
product_id)
Check constraints:
events_session_id_check CHECK (length(session_id::text) 255)
events_type_check CHECK (type = ANY (ARRAY['purchased'::text,
'viewed'::text]))
events_user_id_check CHECK (length(user_id::text) 255)
After removing the session with 400k events, I was able to do an explain
analyze, here is one of them:
http://explain.depesz.com/s/PFNk
Re: [PERFORM] Query performance
On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk j...@tanga.com wrote:
I have an events table that records page views and purchases (type =
'viewed' or type='purchased'). I have a query that figures out people who
bought/viewed this also bought/viewed that.
It worked fine, taking about 0.1 seconds to complete, until a few hours
ago when it started taking hours to complete. Vacuum/analyze didn't help.
Turned out there was one session_id that had 400k rows in the system.
Deleting that made the query performant again.
Is there anything I can do to make the query work better in cases like
that? Missing index, or better query?
This is on 9.3.5.
The below is reproduced at the following URL if it's not formatted
correctly in the email.
https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt
explain select
e1.product_id,
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503' and
e1.product_id != e2.product_id
group by e1.product_id, e2.product_id, e2.site_id;
QUERY PLAN
GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
- Sort (cost=828395.67..840117.89 rows=465 width=19)
Sort Key: e1.product_id, e2.product_id, e2.site_id
- Nested Loop (cost=11.85..20371.14 rows=465 width=19)
- Bitmap Heap Scan on events e1 (cost=11.29..1404.31
rows=369 width=49)
Recheck Cond: (product_id = '82503'::citext)
- Bitmap Index Scan on events_product_id_site_id_idx
(cost=0.00..11.20 rows=369 width=0)
Index Cond: (product_id = '82503'::citext)
- Index Scan using events_session_id_type_product_id_idx on
events e2 (cost=0.56..51.28 rows=12 width=51)
Index Cond: ((session_id = e1.session_id) AND (type =
e1.type))
Filter: (e1.product_id product_id)
(11 rows)
recommender_production= \d events
Table public.events
Column| Type | Modifiers
-+--+-
id | bigint | not null default
nextval('events_id_seq'::regclass)
user_id | citext |
session_id | citext | not null
product_id | citext | not null
site_id | citext | not null
type| text | not null
happened_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null
Indexes:
events_pkey PRIMARY KEY, btree (id)
events_product_id_site_id_idx btree (product_id, site_id)
events_session_id_type_product_id_idx btree (session_id, type,
product_id)
Check constraints:
events_session_id_check CHECK (length(session_id::text) 255)
events_type_check CHECK (type = ANY (ARRAY['purchased'::text,
'viewed'::text]))
events_user_id_check CHECK (length(user_id::text) 255)
After removing the session with 400k events, I was able to do an explain
analyze, here is one of them:
http://explain.depesz.com/s/PFNk
[PERFORM] Query performance
I have an events table that records page views and purchases (type =
'viewed' or type='purchased'). I have a query that figures out people who
bought/viewed this also bought/viewed that.
It worked fine, taking about 0.1 seconds to complete, until a few hours ago
when it started taking hours to complete. Vacuum/analyze didn't help.
Turned out there was one session_id that had 400k rows in the system.
Deleting that made the query performant again.
Is there anything I can do to make the query work better in cases like
that? Missing index, or better query?
This is on 9.3.5.
The below is reproduced at the following URL if it's not formatted
correctly in the email.
https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt
explain select
e1.product_id,
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503' and
e1.product_id != e2.product_id
group by e1.product_id, e2.product_id, e2.site_id;
QUERY PLAN
GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
- Sort (cost=828395.67..840117.89 rows=465 width=19)
Sort Key: e1.product_id, e2.product_id, e2.site_id
- Nested Loop (cost=11.85..20371.14 rows=465 width=19)
- Bitmap Heap Scan on events e1 (cost=11.29..1404.31
rows=369 width=49)
Recheck Cond: (product_id = '82503'::citext)
- Bitmap Index Scan on
events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
Index Cond: (product_id = '82503'::citext)
- Index Scan using
events_session_id_type_product_id_idx on events e2 (cost=0.56..51.28
rows=12 width=51)
Index Cond: ((session_id = e1.session_id) AND
(type = e1.type))
Filter: (e1.product_id product_id)
(11 rows)
recommender_production= \d events
Table public.events
Column| Type | Modifiers
-+--+-
id | bigint | not null default
nextval('events_id_seq'::regclass)
user_id | citext |
session_id | citext | not null
product_id | citext | not null
site_id | citext | not null
type| text | not null
happened_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null
Indexes:
events_pkey PRIMARY KEY, btree (id)
events_product_id_site_id_idx btree (product_id, site_id)
events_session_id_type_product_id_idx btree (session_id, type, product_id)
Check constraints:
events_session_id_check CHECK (length(session_id::text) 255)
events_type_check CHECK (type = ANY (ARRAY['purchased'::text,
'viewed'::text]))
events_user_id_check CHECK (length(user_id::text) 255)
Re: [PERFORM] Query performance
On Sat, Jan 24, 2015 at 10:12 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hi
this plan looks well
Regards
Pavel
Here's one that's not quite as well: http://explain.depesz.com/s/SgT
Joe
2015-01-25 6:45 GMT+01:00 Joe Van Dyk j...@tanga.com:
Oops, didn't run vacuum analyze after deleting the events. Here is
another 'explain analyze': http://explain.depesz.com/s/AviN
On Sat, Jan 24, 2015 at 9:43 PM, Joe Van Dyk j...@tanga.com wrote:
On Sat, Jan 24, 2015 at 9:41 PM, Joe Van Dyk j...@tanga.com wrote:
I have an events table that records page views and purchases (type =
'viewed' or type='purchased'). I have a query that figures out people who
bought/viewed this also bought/viewed that.
It worked fine, taking about 0.1 seconds to complete, until a few hours
ago when it started taking hours to complete. Vacuum/analyze didn't help.
Turned out there was one session_id that had 400k rows in the system.
Deleting that made the query performant again.
Is there anything I can do to make the query work better in cases like
that? Missing index, or better query?
This is on 9.3.5.
The below is reproduced at the following URL if it's not formatted
correctly in the email.
https://gist.githubusercontent.com/joevandyk/cb8f4afdb6c1b178c606/raw/9940bbe033ebd56d38caa46e33c1ddfd9df36eda/gistfile1.txt
explain select
e1.product_id,
e2.site_id,
e2.product_id,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
from events e1
join events e2 on e1.session_id = e2.session_id and e1.type = e2.type
where
e1.product_id = '82503' and
e1.product_id != e2.product_id
group by e1.product_id, e2.product_id, e2.site_id;
QUERY PLAN
GroupAggregate (cost=828395.67..945838.90 rows=22110 width=19)
- Sort (cost=828395.67..840117.89 rows=465 width=19)
Sort Key: e1.product_id, e2.product_id, e2.site_id
- Nested Loop (cost=11.85..20371.14 rows=465 width=19)
- Bitmap Heap Scan on events e1 (cost=11.29..1404.31
rows=369 width=49)
Recheck Cond: (product_id = '82503'::citext)
- Bitmap Index Scan on
events_product_id_site_id_idx (cost=0.00..11.20 rows=369 width=0)
Index Cond: (product_id = '82503'::citext)
- Index Scan using events_session_id_type_product_id_idx
on events e2 (cost=0.56..51.28 rows=12 width=51)
Index Cond: ((session_id = e1.session_id) AND (type =
e1.type))
Filter: (e1.product_id product_id)
(11 rows)
recommender_production= \d events
Table public.events
Column| Type | Modifiers
-+--+-
id | bigint | not null default
nextval('events_id_seq'::regclass)
user_id | citext |
session_id | citext | not null
product_id | citext | not null
site_id | citext | not null
type| text | not null
happened_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null
Indexes:
events_pkey PRIMARY KEY, btree (id)
events_product_id_site_id_idx btree (product_id, site_id)
events_session_id_type_product_id_idx btree (session_id, type,
product_id)
Check constraints:
events_session_id_check CHECK (length(session_id::text) 255)
events_type_check CHECK (type = ANY (ARRAY['purchased'::text,
'viewed'::text]))
events_user_id_check CHECK (length(user_id::text) 255)
After removing the session with 400k events, I was able to do an explain
analyze, here is one of them:
http://explain.depesz.com/s/PFNk
[PERFORM] Adding an additional join causes very different/slow query plan
The actual query selects columns from each of those tables. If I remove the join on order_shipping_addresses, it's very fast. Likewise, if I remove the join on skus, base_skus, or products, it's also very fast. I'm pretty sure I have all the necessary indexes. The below is also at https://gist.github.com/joevandyk/88624f7c23790200cccd/raw/gistfile1.txt Postgres appears to use the number of joins to determine which plan to use? If I go over that by one, then it seems to switch to a very different/slow plan. Is there a way I can speed this up? -- This is really slow explain analyze select pl.uuid as packing_list_id from orders o join order_shipping_addresses osa on osa.order_id = o.id join line_items li on li.order_id = o.id join skus on skus.id = li.sku_id join base_skus bs using (base_sku_id) join products p on p.id = li.product_id left join packed_line_items plis on plis.line_item_id = li.id left join packing_list_items pli using (packed_line_item_id) left join packing_lists pl on pl.id = pli.packing_list_id where pl.uuid = '58995488567'; Hash Join (cost=529945.66..1169006.25 rows=1 width=8) (actual time=16994.025..18442.838 rows=1 loops=1) Hash Cond: (pli.packing_list_id = pl.id) - Hash Join (cost=529937.20..1156754.36 rows=3264913 width=8) (actual time=6394.260..18186.960 rows=3373977 loops=1) Hash Cond: (plis.packed_line_item_id = pli.packed_line_item_id) - Hash Join (cost=389265.00..911373.32 rows=3264913 width=16) (actual time=5260.162..13971.003 rows=3373977 loops=1) Hash Cond: (li.sku_id = skus.id) - Hash Join (cost=379645.45..836455.51 rows=3264913 width=20) (actual time=5130.797..12370.225 rows=3373977 loops=1) Hash Cond: (li.order_id = osa.order_id) - Hash Join (cost=7256.32..353371.98 rows=3265060 width=24) (actual time=29.692..3674.827 rows=3373977 loops=1) Hash Cond: (li.product_id = p.id) - Merge Join (cost=16.25..284912.04 rows=3265060 width=28) (actual time=0.093..2659.779 rows=3373977 loops=1) Merge Cond: (li.id = plis.line_item_id) - Index Only Scan using line_items_id_product_id_order_id_sku_id_idx on line_items li (cost=0.43..116593.45 rows=3240868 width=16) (actual time=0.073..531.457 rows=3606900 loops=1) Heap Fetches: 14 - Index Scan using packed_line_items_line_item_id_idx on packed_line_items plis (cost=0.43..119180.75 rows=3373974 width=20) (actual time=0.014..1052.544 rows=3373977 loops=1) - Hash (cost=6683.92..6683.92 rows=44492 width=4) (actual time=29.561..29.561 rows=44492 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 1565kB - Seq Scan on products p (cost=0.00..6683.92 rows=44492 width=4) (actual time=0.006..23.023 rows=44492 loops=1) - Hash (cost=325301.79..325301.79 rows=2870027 width=8) (actual time=5097.168..5097.168 rows=2870028 loops=1) Buckets: 65536 Batches: 8 Memory Usage: 14039kB - Hash Join (cost=111732.51..325301.79 rows=2870027 width=8) (actual time=828.796..4582.395 rows=2870028 loops=1) Hash Cond: (o.id = osa.order_id) - Seq Scan on orders o (cost=0.00..126120.27 rows=2870027 width=4) (actual time=0.009..636.423 rows=2870028 loops=1) - Hash (cost=64643.56..64643.56 rows=2870156 width=4) (actual time=827.832..827.832 rows=2870028 loops=1) Buckets: 65536 Batches: 8 Memory Usage: 12636kB - Seq Scan on order_shipping_addresses osa (cost=0.00..64643.56 rows=2870156 width=4) (actual time=0.008..419.783 rows=2870028 loops=1) - Hash (cost=8324.48..8324.48 rows=103606 width=4) (actual time=129.271..129.271 rows=103606 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 3643kB - Hash Join (cost=3389.30..8324.48 rows=103606 width=4) (actual time=28.641..113.012 rows=103606 loops=1) Hash Cond: (skus.base_sku_id = bs.base_sku_id) - Seq Scan on skus (cost=0.00..2863.06 rows=103606 width=20) (actual time=0.014..13.836 rows=103606 loops=1) - Hash (cost=2098.02..2098.02 rows=103302 width=16) (actual time=28.549..28.549 rows=103302 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 4843kB - Seq Scan on base_skus bs (cost=0.00..2098.02 rows=103302 width=16) (actual time=0.013..13.572 rows=103302 loops=1) - Hash (cost=78727.09..78727.09 rows=3374009 width=24) (actual
Re: [PERFORM] Adding an additional join causes very different/slow query plan
Hm, setting set join_collapse_limit = 9 seemed to fix the problem. Is that my best/only option? On Mon, Dec 16, 2013 at 1:52 PM, Joe Van Dyk j...@tanga.com wrote: The actual query selects columns from each of those tables. If I remove the join on order_shipping_addresses, it's very fast. Likewise, if I remove the join on skus, base_skus, or products, it's also very fast. I'm pretty sure I have all the necessary indexes. The below is also at https://gist.github.com/joevandyk/88624f7c23790200cccd/raw/gistfile1.txt Postgres appears to use the number of joins to determine which plan to use? If I go over that by one, then it seems to switch to a very different/slow plan. Is there a way I can speed this up? -- This is really slow explain analyze select pl.uuid as packing_list_id from orders o join order_shipping_addresses osa on osa.order_id = o.id join line_items li on li.order_id = o.id join skus on skus.id = li.sku_id join base_skus bs using (base_sku_id) join products p on p.id = li.product_id left join packed_line_items plis on plis.line_item_id = li.id left join packing_list_items pli using (packed_line_item_id) left join packing_lists pl on pl.id = pli.packing_list_id where pl.uuid = '58995488567'; Hash Join (cost=529945.66..1169006.25 rows=1 width=8) (actual time=16994.025..18442.838 rows=1 loops=1) Hash Cond: (pli.packing_list_id = pl.id) - Hash Join (cost=529937.20..1156754.36 rows=3264913 width=8) (actual time=6394.260..18186.960 rows=3373977 loops=1) Hash Cond: (plis.packed_line_item_id = pli.packed_line_item_id) - Hash Join (cost=389265.00..911373.32 rows=3264913 width=16) (actual time=5260.162..13971.003 rows=3373977 loops=1) Hash Cond: (li.sku_id = skus.id) - Hash Join (cost=379645.45..836455.51 rows=3264913 width=20) (actual time=5130.797..12370.225 rows=3373977 loops=1) Hash Cond: (li.order_id = osa.order_id) - Hash Join (cost=7256.32..353371.98 rows=3265060 width=24) (actual time=29.692..3674.827 rows=3373977 loops=1) Hash Cond: (li.product_id = p.id) - Merge Join (cost=16.25..284912.04 rows=3265060 width=28) (actual time=0.093..2659.779 rows=3373977 loops=1) Merge Cond: (li.id = plis.line_item_id) - Index Only Scan using line_items_id_product_id_order_id_sku_id_idx on line_items li (cost=0.43..116593.45 rows=3240868 width=16) (actual time=0.073..531.457 rows=3606900 loops=1) Heap Fetches: 14 - Index Scan using packed_line_items_line_item_id_idx on packed_line_items plis (cost=0.43..119180.75 rows=3373974 width=20) (actual time=0.014..1052.544 rows=3373977 loops=1) - Hash (cost=6683.92..6683.92 rows=44492 width=4) (actual time=29.561..29.561 rows=44492 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 1565kB - Seq Scan on products p (cost=0.00..6683.92 rows=44492 width=4) (actual time=0.006..23.023 rows=44492 loops=1) - Hash (cost=325301.79..325301.79 rows=2870027 width=8) (actual time=5097.168..5097.168 rows=2870028 loops=1) Buckets: 65536 Batches: 8 Memory Usage: 14039kB - Hash Join (cost=111732.51..325301.79 rows=2870027 width=8) (actual time=828.796..4582.395 rows=2870028 loops=1) Hash Cond: (o.id = osa.order_id) - Seq Scan on orders o (cost=0.00..126120.27 rows=2870027 width=4) (actual time=0.009..636.423 rows=2870028 loops=1) - Hash (cost=64643.56..64643.56 rows=2870156 width=4) (actual time=827.832..827.832 rows=2870028 loops=1) Buckets: 65536 Batches: 8 Memory Usage: 12636kB - Seq Scan on order_shipping_addresses osa (cost=0.00..64643.56 rows=2870156 width=4) (actual time=0.008..419.783 rows=2870028 loops=1) - Hash (cost=8324.48..8324.48 rows=103606 width=4) (actual time=129.271..129.271 rows=103606 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 3643kB - Hash Join (cost=3389.30..8324.48 rows=103606 width=4) (actual time=28.641..113.012 rows=103606 loops=1) Hash Cond: (skus.base_sku_id = bs.base_sku_id) - Seq Scan on skus (cost=0.00..2863.06 rows=103606 width=20) (actual time=0.014..13.836 rows=103606 loops=1) - Hash (cost=2098.02..2098.02 rows=103302 width=16) (actual time=28.549..28.549 rows=103302 loops=1
Re: [PERFORM] Adding an additional join causes very different/slow query plan
On Mon, Dec 16, 2013 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Joe Van Dyk j...@tanga.com writes: Hm, setting set join_collapse_limit = 9 seemed to fix the problem. Is that my best/only option? Yup, that's what I was just about to suggest. You might want to use 10 or 12 in case some of your queries are a bit more complex than this one --- but don't go overboard, or you may find yourself with unreasonable planning time. Is there a way to measure the planning time? It's not reported in 'explain analyze' or 'explain analyze verbose', right? Joe
[PERFORM] slow joins?
On 9.2.4, running two identical queries except for the value of a column in the WHERE clause. Postgres is picking very different query plans, the first is much slower than the second. Any ideas on how I can speed this up? I have btree indexes for all the columns used in the query. explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 221; Aggregate (cost=193356.31..193356.32 rows=1 width=0) (actual time=2425.225..2425.225 rows=1 loops=1) - Hash Join (cost=78864.43..193160.41 rows=78360 width=0) (actual time=726.612..2424.206 rows=8413 loops=1) Hash Cond: (pi.line_item_id = li.id) - Seq Scan on purchased_items pi (cost=0.00..60912.39 rows=3724639 width=4) (actual time=0.008..616.812 rows=3724639 loops=1) - Hash (cost=77937.19..77937.19 rows=56499 width=4) (actual time=726.231..726.231 rows=8178 loops=1) Buckets: 4096 Batches: 4 Memory Usage: 73kB - Hash Join (cost=1684.33..77937.19 rows=56499 width=4) (actual time=1.270..723.222 rows=8178 loops=1) Hash Cond: (li.product_id = products.id) - Seq Scan on line_items li (cost=0.00..65617.18 rows=2685518 width=8) (actual time=0.081..392.926 rows=2685499 loops=1) - Hash (cost=1676.60..1676.60 rows=618 width=4) (actual time=0.835..0.835 rows=618 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 22kB - Bitmap Heap Scan on products (cost=13.07..1676.60 rows=618 width=4) (actual time=0.185..0.752 rows=618 loops=1) Recheck Cond: (drop_shipper_id = 221) - Bitmap Index Scan on index_products_on_drop_shipper_id (cost=0.00..12.92 rows=618 width=0) (actual time=0.125..0.125 rows=618 loops=1) Index Cond: (drop_shipper_id = 221) Total runtime: 2425.302 ms explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 2; Aggregate (cost=29260.40..29260.41 rows=1 width=0) (actual time=0.906..0.906 rows=1 loops=1) - Nested Loop (cost=0.00..29254.38 rows=2409 width=0) (actual time=0.029..0.877 rows=172 loops=1) - Nested Loop (cost=0.00..16011.70 rows=1737 width=4) (actual time=0.021..0.383 rows=167 loops=1) - Index Scan using index_products_on_drop_shipper_id on products (cost=0.00..80.41 rows=19 width=4) (actual time=0.010..0.074 rows=70 loops=1) Index Cond: (drop_shipper_id = 2) - Index Scan using index_line_items_on_product_id on line_items li (cost=0.00..835.70 rows=279 width=8) (actual time=0.002..0.004 rows=2 loops=70) Index Cond: (product_id = products.id) - Index Only Scan using purchased_items_line_item_id_idx on purchased_items pi (cost=0.00..7.60 rows=2 width=4) (actual time=0.002..0.003 rows=1 loops=167) Index Cond: (line_item_id = li.id) Heap Fetches: 5 Total runtime: 0.955 ms (11 rows)
Re: [PERFORM] slow joins?
( https://gist.github.com/joevandyk/df0df703f3fda6d14ae1/raw/c15cae813913b7f8c35b24b467a0c732c0100d79/gistfile1.txtshows a non-wrapped version of the queries and plan) On Fri, Apr 5, 2013 at 6:38 PM, Joe Van Dyk j...@tanga.com wrote: On 9.2.4, running two identical queries except for the value of a column in the WHERE clause. Postgres is picking very different query plans, the first is much slower than the second. Any ideas on how I can speed this up? I have btree indexes for all the columns used in the query. explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 221; Aggregate (cost=193356.31..193356.32 rows=1 width=0) (actual time=2425.225..2425.225 rows=1 loops=1) - Hash Join (cost=78864.43..193160.41 rows=78360 width=0) (actual time=726.612..2424.206 rows=8413 loops=1) Hash Cond: (pi.line_item_id = li.id) - Seq Scan on purchased_items pi (cost=0.00..60912.39 rows=3724639 width=4) (actual time=0.008..616.812 rows=3724639 loops=1) - Hash (cost=77937.19..77937.19 rows=56499 width=4) (actual time=726.231..726.231 rows=8178 loops=1) Buckets: 4096 Batches: 4 Memory Usage: 73kB - Hash Join (cost=1684.33..77937.19 rows=56499 width=4) (actual time=1.270..723.222 rows=8178 loops=1) Hash Cond: (li.product_id = products.id) - Seq Scan on line_items li (cost=0.00..65617.18 rows=2685518 width=8) (actual time=0.081..392.926 rows=2685499 loops=1) - Hash (cost=1676.60..1676.60 rows=618 width=4) (actual time=0.835..0.835 rows=618 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 22kB - Bitmap Heap Scan on products (cost=13.07..1676.60 rows=618 width=4) (actual time=0.185..0.752 rows=618 loops=1) Recheck Cond: (drop_shipper_id = 221) - Bitmap Index Scan on index_products_on_drop_shipper_id (cost=0.00..12.92 rows=618 width=0) (actual time=0.125..0.125 rows=618 loops=1) Index Cond: (drop_shipper_id = 221) Total runtime: 2425.302 ms explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 2; Aggregate (cost=29260.40..29260.41 rows=1 width=0) (actual time=0.906..0.906 rows=1 loops=1) - Nested Loop (cost=0.00..29254.38 rows=2409 width=0) (actual time=0.029..0.877 rows=172 loops=1) - Nested Loop (cost=0.00..16011.70 rows=1737 width=4) (actual time=0.021..0.383 rows=167 loops=1) - Index Scan using index_products_on_drop_shipper_id on products (cost=0.00..80.41 rows=19 width=4) (actual time=0.010..0.074 rows=70 loops=1) Index Cond: (drop_shipper_id = 2) - Index Scan using index_line_items_on_product_id on line_items li (cost=0.00..835.70 rows=279 width=8) (actual time=0.002..0.004 rows=2 loops=70) Index Cond: (product_id = products.id) - Index Only Scan using purchased_items_line_item_id_idx on purchased_items pi (cost=0.00..7.60 rows=2 width=4) (actual time=0.002..0.003 rows=1 loops=167) Index Cond: (line_item_id = li.id) Heap Fetches: 5 Total runtime: 0.955 ms (11 rows)
Re: [PERFORM] slow joins?
If I disable sequential scans, hash joins, and merge joins, the query plans become the same and performance on the first slow one is much improved. Is there something else I can do to avoid this problem? below also at https://gist.github.com/joevandyk/34e31b3ad5cccb730a50/raw/8081a4298ba50ac93a86df97c1d0aae482ee7d2d/gistfile1.txt Aggregate (cost=869360.53..869360.54 rows=1 width=0) (actual time=103.102..103.102 rows=1 loops=1) - Nested Loop (cost=0.00..869164.63 rows=78360 width=0) (actual time=0.253..101.708 rows=8413 loops=1) - Nested Loop (cost=0.00..438422.95 rows=56499 width=4) (actual time=0.157..51.766 rows=8178 loops=1) - Index Scan using index_products_on_drop_shipper_id on products (cost=0.00..2312.56 rows=618 width=4) (actual time=0.087..6.318 rows=618 loops=1) Index Cond: (drop_shipper_id = 221) - Index Scan using index_line_items_on_product_id on line_items li (cost=0.00..702.89 rows=279 width=8) (actual time=0.010..0.069 rows=13 loops=618) Index Cond: (product_id = products.id) - Index Only Scan using purchased_items_line_item_id_idx on purchased_items pi (cost=0.00..7.60 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=8178) Index Cond: (line_item_id = li.id) Heap Fetches: 144 Total runtime: 103.442 ms (11 rows) On Fri, Apr 5, 2013 at 6:38 PM, Joe Van Dyk j...@tanga.com wrote: On 9.2.4, running two identical queries except for the value of a column in the WHERE clause. Postgres is picking very different query plans, the first is much slower than the second. Any ideas on how I can speed this up? I have btree indexes for all the columns used in the query. explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 221; Aggregate (cost=193356.31..193356.32 rows=1 width=0) (actual time=2425.225..2425.225 rows=1 loops=1) - Hash Join (cost=78864.43..193160.41 rows=78360 width=0) (actual time=726.612..2424.206 rows=8413 loops=1) Hash Cond: (pi.line_item_id = li.id) - Seq Scan on purchased_items pi (cost=0.00..60912.39 rows=3724639 width=4) (actual time=0.008..616.812 rows=3724639 loops=1) - Hash (cost=77937.19..77937.19 rows=56499 width=4) (actual time=726.231..726.231 rows=8178 loops=1) Buckets: 4096 Batches: 4 Memory Usage: 73kB - Hash Join (cost=1684.33..77937.19 rows=56499 width=4) (actual time=1.270..723.222 rows=8178 loops=1) Hash Cond: (li.product_id = products.id) - Seq Scan on line_items li (cost=0.00..65617.18 rows=2685518 width=8) (actual time=0.081..392.926 rows=2685499 loops=1) - Hash (cost=1676.60..1676.60 rows=618 width=4) (actual time=0.835..0.835 rows=618 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 22kB - Bitmap Heap Scan on products (cost=13.07..1676.60 rows=618 width=4) (actual time=0.185..0.752 rows=618 loops=1) Recheck Cond: (drop_shipper_id = 221) - Bitmap Index Scan on index_products_on_drop_shipper_id (cost=0.00..12.92 rows=618 width=0) (actual time=0.125..0.125 rows=618 loops=1) Index Cond: (drop_shipper_id = 221) Total runtime: 2425.302 ms explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 2; Aggregate (cost=29260.40..29260.41 rows=1 width=0) (actual time=0.906..0.906 rows=1 loops=1) - Nested Loop (cost=0.00..29254.38 rows=2409 width=0) (actual time=0.029..0.877 rows=172 loops=1) - Nested Loop (cost=0.00..16011.70 rows=1737 width=4) (actual time=0.021..0.383 rows=167 loops=1) - Index Scan using index_products_on_drop_shipper_id on products (cost=0.00..80.41 rows=19 width=4) (actual time=0.010..0.074 rows=70 loops=1) Index Cond: (drop_shipper_id = 2) - Index Scan using index_line_items_on_product_id on line_items li (cost=0.00..835.70 rows=279 width=8) (actual time=0.002..0.004 rows=2 loops=70) Index Cond: (product_id = products.id) - Index Only Scan using purchased_items_line_item_id_idx on purchased_items pi (cost=0.00..7.60 rows=2 width=4) (actual time=0.002..0.003 rows=1 loops=167) Index Cond: (line_item_id = li.id) Heap Fetches: 5 Total runtime: 0.955 ms (11 rows)
Re: [PERFORM] slow joins?
On Fri, Apr 5, 2013 at 6:54 PM, Greg Williamson gwilliamso...@yahoo.comwrote: Joe -- From: Joe Van Dyk j...@tanga.com To: pgsql-performance@postgresql.org Sent: Friday, April 5, 2013 6:42 PM Subject: Re: [PERFORM] slow joins? ( https://gist.github.com/joevandyk/df0df703f3fda6d14ae1/raw/c15cae813913b7f8c35b24b467a0c732c0100d79/gistfile1.txtshows a non-wrapped version of the queries and plan) On Fri, Apr 5, 2013 at 6:38 PM, Joe Van Dyk j...@tanga.com wrote: On 9.2.4, running two identical queries except for the value of a column in the WHERE clause. Postgres is picking very different query plans, the first is much slower than the second. Any ideas on how I can speed this up? I have btree indexes for all the columns used in the query. explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 221; Aggregate (cost=193356.31..193356.32 rows=1 width=0) (actual time=2425.225..2425.225 rows=1 loops=1) - Hash Join (cost=78864.43..193160.41 rows=78360 width=0) (actual time=726.612..2424.206 rows=8413 loops=1) Hash Cond: (pi.line_item_id = li.id) - Seq Scan on purchased_items pi (cost=0.00..60912.39 rows=3724639 width=4) (actual time=0.008..616.812 rows=3724639 loops=1) - Hash (cost=77937.19..77937.19 rows=56499 width=4) (actual time=726.231..726.231 rows=8178 loops=1) Buckets: 4096 Batches: 4 Memory Usage: 73kB - Hash Join (cost=1684.33..77937.19 rows=56499 width=4) (actual time=1.270..723.222 rows=8178 loops=1) Hash Cond: (li.product_id = products.id) - Seq Scan on line_items li (cost=0.00..65617.18 rows=2685518 width=8) (actual time=0.081..392.926 rows=2685499 loops=1) - Hash (cost=1676.60..1676.60 rows=618 width=4) (actual time=0.835..0.835 rows=618 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 22kB - Bitmap Heap Scan on products (cost=13.07..1676.60 rows=618 width=4) (actual time=0.185..0.752 rows=618 loops=1) Recheck Cond: (drop_shipper_id = 221) - Bitmap Index Scan on index_products_on_drop_shipper_id (cost=0.00..12.92 rows=618 width=0) (actual time=0.125..0.125 rows=618 loops=1) Index Cond: (drop_shipper_id = 221) Total runtime: 2425.302 ms explain analyze SELECT COUNT(*) FROM purchased_items pi inner join line_items li on li.id = pi.line_item_id inner join products on products.id = li.product_id WHERE products.drop_shipper_id = 2; Aggregate (cost=29260.40..29260.41 rows=1 width=0) (actual time=0.906..0.906 rows=1 loops=1) - Nested Loop (cost=0.00..29254.38 rows=2409 width=0) (actual time=0.029..0.877 rows=172 loops=1) - Nested Loop (cost=0.00..16011.70 rows=1737 width=4) (actual time=0.021..0.383 rows=167 loops=1) - Index Scan using index_products_on_drop_shipper_id on products (cost=0.00..80.41 rows=19 width=4) (actual time=0.010..0.074 rows=70 loops=1) Index Cond: (drop_shipper_id = 2) - Index Scan using index_line_items_on_product_id on line_items li (cost=0.00..835.70 rows=279 width=8) (actual time=0.002..0.004 rows=2 loops=70) Index Cond: (product_id = products.id) - Index Only Scan using purchased_items_line_item_id_idx on purchased_items pi (cost=0.00..7.60 rows=2 width=4) (actual time=0.002..0.003 rows=1 loops=167) Index Cond: (line_item_id = li.id) Heap Fetches: 5 Total runtime: 0.955 ms (11 rows) Does drop_shipper+id have a much larger number of rows which is making the scanner want to avoid an indexed scan or otherwise prefer a sequential scan on products and on line_items ? Assuming you mean products.drop_shipper_id? There are more rows matched for the first one vs the second one. 70 products rows match drop_shipper_id=2, 618 match drop_shipper_id=221. What are the stats settings for these tables ? Whatever the defaults are. HTH, Greg WIlliamson -- 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] Hints (was Poor performance using CTE)
On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: Rather than telling the planner what to do or not to do, I'd much rather have hints that give the planner more information about the tables and quals involved in the query. A typical source of bad plans is when the planner gets its cost estimates wrong. So rather than telling the planner to use a nested loop join for a INNER JOIN b ON a.id = b.id, the user could tell the planner that there are only 10 rows that match the a.id = b.id qual. That gives the planner the information it needs to choose the right plan on its own. That kind of hints would be much less implementation specific and much more likely to still be useful, or at least not outright counter-productive, in a future version with a smarter planner. You could also attach that kind of hints to tables and columns, which would be more portable and nicer than decorating all queries. I like this idea, but also think that if we have a syntax to allow hints, it would be nice to have a simple way to ignore all hints (yes, I suppose I'm suggesting yet another GUC). That way after sprinkling your SQL with hints, you could easily periodically (e.g. after a Postgres upgrade) test what would happen if the hints were removed. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- 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] Hints (was Poor performance using CTE)
On 11/21/2012 09:28 AM, Craig James wrote: On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway m...@joeconway.com mailto:m...@joeconway.com wrote: On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: Rather than telling the planner what to do or not to do, I'd much rather have hints that give the planner more information about the tables and quals involved in the query. A typical source of bad plans is when the planner gets its cost estimates wrong. So rather than telling the planner to use a nested loop join for a INNER JOIN b ON a.id http://a.id = b.id http://b.id, the user could tell the planner that there are only 10 rows that match the a.id http://a.id = b.id http://b.id qual. That gives the planner the information it needs to choose the right plan on its own. That kind of hints would be much less implementation specific and much more likely to still be useful, or at least not outright counter-productive, in a future version with a smarter planner. You could also attach that kind of hints to tables and columns, which would be more portable and nicer than decorating all queries. I like this idea, but also think that if we have a syntax to allow hints, it would be nice to have a simple way to ignore all hints (yes, I suppose I'm suggesting yet another GUC). That way after sprinkling your SQL with hints, you could easily periodically (e.g. after a Postgres upgrade) test what would happen if the hints were removed. Or a three-way choice: Allow, ignore, or generate an error. That would allow developers to identify where hints are being used. +1 Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- 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] pg_dumpall affecting performance
I was always under the impression that pg_dump and pg_dumpall cause all data to be read in to the buffers and then out, (of course squeezing out whatever may be active). That is the big advantage to using PITR backups and using a tar or cpio method of backing up active containers and shipping off to another system, disk or api to tape system. -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Mark Mikulec Sent: Tuesday, February 15, 2011 12:41 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] pg_dumpall affecting performance Hello, I was under the impression that pg_dumpall didn't affect database performance when dumping while the db is live. However I have evidence to the contrary now - queries that are run during the pg_dumpall time take 10 to a 100 times longer to execute than normal while pg_dumpall is running. The strange thing is that this started after my database grew by about 25% after a large influx of data due to user load. I'm wonder if there is a tipping point or a config setting I need to change now that the db is larger that is causing all this to happen. Thanks, Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Index Bloat - how to tell?
I have used this in the past ... run this against the database that you want to
inspect.
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint,
relpages::bigint, otta,*/
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS
tbloat,
CASE WHEN relpages otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS
wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric
END,1) AS ibloat,
CASE WHEN ipages iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma
END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples,
COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta --
very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma
END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma
END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac0 AND s2.schemaname = s.schemaname AND s2.tablename
= s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23
END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname =
rs.schemaname AND nn.nspname 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC
-Original Message-
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of John W Strange
Sent: Tuesday, December 14, 2010 8:48 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Index Bloat - how to tell?
How can you tell when your indexes are starting to get bloated and when you
need to rebuild them. I haven't seen a quick way to tell and not sure if it's
being tracked.
___
| John W. Strange | Investment Bank | Global Commodities Technology
| J.P. Morgan | 700 Louisiana, 11th Floor | T: 713-236-4122 | C: 281-744-6476 |
F: 713 236-
| john.w.stra...@jpmchase.com | jpmorgan.com
This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase Co., its subsidiaries
and affiliates.
This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.
Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
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] Auto ANALYZE criteria
Thanks for fixing the docs, but if that's the case, I shouldn't be seeing the behavior that I'm seeing. Should I flesh out this test case a little better and file a bug? Thanks, Joe On Tue, Sep 21, 2010 at 4:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Joe Miller joe.d.mil...@gmail.com writes: I was looking at the autovacuum documentation: http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html#AUTOVACUUM For analyze, a similar condition is used: the threshold, defined as: analyze threshold = analyze base threshold + analyze scale factor * number of tuples is compared to the total number of tuples inserted or updated since the last ANALYZE. I guess that should be updated to read insert, updated or deleted. Mph. We caught the other places where the docs explain what the analyze threshold is, but missed that one. Fixed, thanks for pointing it out. 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] Slow count(*) again...
The biggest single problem with select count(*) is that it is seriously overused. People use that idiom to establish existence, which usually leads to a performance disaster in the application using it, unless the table has no more than few hundred records. SQL language, of which PostgreSQL offers an excellent implementation, offers [NOT] EXISTS clause since its inception in the Jurassic era. The problem is with the sequential scan, not with counting. I'd even go as far as to suggest that 99% instances of the select count(*) idiom are probably bad use of the SQL language. I agree, I have seen many very bad examples of using count(*). I will go so far as to question the use of count(*) in my examples here. It there a better way to come up with a page list than using count(*)? What is the best method to make a page of results and a list of links to other pages of results? Am I barking up the wrong tree here? One way I have dealt with this on very large tables is to cache the count(*) at the application level (using memcached, terracotta, or something along those lines) and then increment that cache whenever you add a row to the relevant table. On application restart that cache is re-initialized with a regular old count(*). This approach works really well and all large systems in my experience need caching in front of the DB eventually. If you have a simpler system with say a single application/web server you can simply store the value in a variable, the specifics would depend on the language and framework you are using. Another more all-DB approach is to create a statistics tables into which you place aggregated statistics rows (num deleted, num inserted, totals, etc) at an appropriate time interval in your code. So you have rows containing aggregated statistics information for the past and some tiny portion of the new data happening right now that hasn't yet been aggregated. Queries then look like a summation of the aggregated values in the statistics table plus a count(*) over just the newest portion of the data table and are generally very fast. Overall I have found that once things get big the layers of your app stack start to blend together and have to be combined in clever ways to keep speed up. Postgres is a beast but when you run into things it can't do well just find a way to cache it or make it work together with some other persistence tech to handle those cases.
Re: [PERFORM] Auto ANALYZE criteria
I was looking at the autovacuum documentation: http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html#AUTOVACUUM For analyze, a similar condition is used: the threshold, defined as: analyze threshold = analyze base threshold + analyze scale factor * number of tuples is compared to the total number of tuples inserted or updated since the last ANALYZE. I guess that should be updated to read insert, updated or deleted. On Mon, Sep 20, 2010 at 10:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Joe Miller joe.d.mil...@gmail.com writes: The autovacuum daemon currently uses the number of inserted and updated tuples to determine if it should run VACUUM ANALYZE on a table. Why doesn’t it consider deleted tuples as well? I think you misread the code. Now there *is* a problem, pre-9.0, if your update pattern is such that most or all updates are HOT updates. To quote from the 9.0 alpha release notes: Revise pgstat's tracking of tuple changes to improve the reliability of decisions about when to auto-analyze. The previous code depended on n_live_tuples + n_dead_tuples - last_anl_tuples, where all three of these numbers could be bad estimates from ANALYZE itself. Even worse, in the presence of a steady flow of HOT updates and matching HOT-tuple reclamations, auto-analyze might never trigger at all, even if all three numbers are exactly right, because n_dead_tuples could hold steady. It's not clear to me if that matches your problem, though. 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] Auto ANALYZE criteria
On Mon, Sep 20, 2010 at 6:28 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Joe Miller joe.d.mil...@gmail.com wrote: I can set up a cron job to run the ANALYZE manually, but it seems like the autovacuum daemon should be smart enough to figure this out on its own. Deletes can have as big an impact on the stats as inserts and updates. But until the deleted rows are vacuumed from the indexes, an index scan must read all the index entries for the deleted tuples, and visit the heap to determine that they are not visible. Does a manual run of ANALYZE without a VACUUM change the stats much for you, or are you running VACUUM ANALYZE? -Kevin The autovacuum is running correctly, so the deleted rows are being removed. All I'm doing is an ANALYZE, not VACUUM ANALYZE. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Auto ANALYZE criteria
The autovacuum daemon currently uses the number of inserted and
updated tuples to determine if it should run VACUUM ANALYZE on a
table. Why doesn’t it consider deleted tuples as well?
For example, I have a table which gets initially loaded with several
million records. A batch process grabs the records 100 at a time, does
some processing and deletes them from the table in the order of the
primary key. Eventually, performance degrades because an autoanalyze
is never run. The planner decides that it should do a sequential scan
instead of an index scan because the stats don't reflect reality. See
example below.
I can set up a cron job to run the ANALYZE manually, but it seems like
the autovacuum daemon should be smart enough to figure this out on its
own. Deletes can have as big an impact on the stats as inserts and
updates.
Joe Miller
---
testdb=# \d test
Table public.test
Column | Type | Modifiers
+-+---
id | integer | not null
data | bytea |
Indexes:
test_pkey PRIMARY KEY, btree (id)
testdb=# insert into public.test select s.a, gen_random_bytes(256)
from generate_series(1,1000) as s(a);
INSERT 0 1000
testdb=# SELECT *
FROM pg_stat_all_tables
WHERE schemaname='public' AND relname='test';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd |
n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze
| last_autoanalyze
-++-+--+--+--+---+---+---+---+---+++-+-+--+--
5608158 | public | test|1 |0 |0 |
0 | 1000 | 0 | 0 | 0 |
0 | 0 | | | |
2010-09-20 10:46:37.283775-04
(1 row)
testdb=# explain analyze delete from public.test where id = 100;
QUERY PLAN
Index Scan using test_pkey on test (cost=0.00..71.63 rows=1000
width=6) (actual time=13.251..22.916 rows=100 loops=1)
Index Cond: (id = 100)
Total runtime: 23.271 ms
(3 rows)
{ delete records ad nauseum }
testdb=# explain analyze delete from public.test where id = 7978800;
QUERY PLAN
---
Seq Scan on test (cost=0.00..410106.17 rows=2538412 width=6) (actual
time=48771.772..49681.562 rows=100 loops=1)
Filter: (id = 7978800)
Total runtime: 49682.006 ms
(3 rows)
testdb=# SELECT *
FROM pg_stat_all_tables
WHERE schemaname='public' AND relname='test';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan |
idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd |
n_live_tup | n_dead_tup | last_vacuum |last_autovacuum
| last_analyze | last_autoanalyze
-++-+--+--+--+---+---+---+---+---+++-+---+--+---
5608158 | public | test|1 |0 |54345 |
5433206 | 1000 | 0 | 5433200 | 0 |
5459506 | 725300 | | 2010-09-20 14:45:54.757611-04 |
| 2010-09-20 10:46:37.283775-04
--
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] Queries with conditions using bitand operator
On 07/13/2010 04:48 AM, Elias Ghanem wrote: Hi, I have table ARTICLE containing a String a field STATUS that represents a number in binary format (for ex: 10011101). My application issues queries with where conditions that uses BITAND operator on this field (for ex: select * from article where status 4 = 4). Thus i'm facing performance problemes with these select queries: the queries are too slow. Since i'm using the BITAND operator in my conditions, creating an index on the status filed is useless and since the second operator variable (status 4 = 4; status 8 = 8; status 16 = 16...) a functional index is also usless (because a functional index require the use of a function that accept only table column as input parameter: constants are not accepted). So is there a way to enhance the performance of these queries? You haven't given a lot of info to help us help you, but would something along these lines be useful to you? drop table if exists testbit; create table testbit( id serial primary key, article text, status int ); insert into testbit (article, status) select 'article ' || generate_series::text, generate_series % 256 from generate_series(1,100); create index idx1 on testbit(article) where status 1 = 1; create index idx2 on testbit(article) where status 2 = 2; create index idx4 on testbit(article) where status 4 = 4; create index idx8 on testbit(article) where status 8 = 8; create index idx16 on testbit(article) where status 16 = 16; create index idx32 on testbit(article) where status 512 = 512; update testbit set status = status + 512 where id in (42, 4242, 424242); explain analyze select * from testbit where status 512 = 512; QUERY PLAN -- Index Scan using idx32 on testbit (cost=0.00..4712.62 rows=5000 width=22) (actual time=0.080..0.085 rows=3 loops=1) Total runtime: 0.170 ms HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, Support signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Highly Efficient Custom Sorting
On 07/06/2010 12:42 PM, Eliot Gable wrote:
Thanks for suggesting array_unnest(). I think that will actually prove
more useful to me than the other example I'm using for extracting my
data from an array. I was actually planning on computing the order on
the first call and storing it in a linked list which gets returned one
item at a time until all rows have been returned. Also, I found a code
example using Google that showed someone storing data across function
calls using that pointer. I used their example to produce this:
snip
if(SRF_IS_FIRSTCALL()) {
funcctx = SRF_FIRSTCALL_INIT();
/* This is where we stick or sorted data for returning later */
funcctx-user_fctx =
MemoryContextAlloc(funcctx-multi_call_memory_ctx, sizeof(sort_data));
oldcontext = MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
data = (sort_data*) funcctx-user_fctx;
/snip
I have a structure set up that is typedef'd to sort_data which stores
pointers to various things that I need to survive across the calls.
Since this seems to be what you are suggesting, I assume this is the
correct approach.
This approach works, but you could also use the SFRM_Materialize mode
and calculate the entire result set in one go. That tends to be simpler.
See, for example crosstab_hash() in contrib/tablefunc for an example.
FWIW, there are also some good examples of array handling in PL/R, e.g.
pg_array_get_r() in pg_conversion.c
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, Support
signature.asc
Description: OpenPGP digital signature
Re: [PERFORM] Need to increase performance of a query
On 06/10/2010 12:56 PM, Anne Rosset wrote: Craig James wrote: create index item_rank_null_idx on item_rank(pf_id) where item_rank.pf_id is null; Craig Hi Craig, I tried again after adding your suggested index but I didn't see any improvements: (seems that the index is not used) Filter: ((rank IS NOT NULL) AND (pf_id IS NULL) AND ((project_id)::text = 'proj2783'::text)) Total runtime: 11.988 ms (6 rows) Time: 13.654 ms try: create index item_rank_null_idx on item_rank(pf_id) where rank IS NOT NULL AND pf_id IS NULL; Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Need to increase performance of a query
On 06/10/2010 01:10 PM, Joe Conway wrote: try: create index item_rank_null_idx on item_rank(pf_id) where rank IS NOT NULL AND pf_id IS NULL; oops -- that probably should be: create index item_rank_null_idx on item_rank(project_id) where rank IS NOT NULL AND pf_id IS NULL; Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Need to increase performance of a query
On 06/10/2010 01:21 PM, Anne Rosset wrote: I tried that and it didn't make any difference. Same query plan. A little experimentation suggests this might work: create index item_rank_project on item_rank(project_id, rank) where pf_id IS NULL; Joe signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Autovacuum Tuning advice
Sorry, additional info: OS is Red Hat Enterprise Linux ES release 4 (Nahant Update 5) DISK - IBM DS4700 Array - 31 drives and 1 hot spare - RAID10 - 32MB stripe Sysctl.conf kernel.shmmax=6442450944 kernel.shmall=1887436 kernel.msgmni=1024 kernel.msgmnb=65536 kernel.msgmax=65536 kernel.sem=250 256000 32 1024 Problem Child table: This table is partitioned so that after the data has rolled past 30 days, I can just drop the table. Table public.log_events_y2010m02 Column | Type | Modifiers ---++--- callseq | character varying(32) | not null eventid | character varying(40) | not null msgseq| character varying(32) | not null eventdate | timestamp(0) without time zone | not null hollyid | character varying(20) | ownerid | character varying(60) | spownerid | character varying(60) | applicationid | character varying(60) | clid | character varying(40) | dnis | character varying(40) | param | character varying(2000)| docid | character varying(40) | Indexes: log_events_y2010m02_pk PRIMARY KEY, btree (callseq, msgseq) loev_eventid_idx_y2010m02 btree (eventid) loev_ownerid_cidx_y2010m02 btree (ownerid, spownerid) Check constraints: log_events_y2010m02_eventdate_check CHECK (eventdate = '2010-02-01'::date AND eventdate '2010-03-01'::date) Inherits: log_events Parent Table: Table public.log_events Column | Type | Modifiers ---++--- callseq | character varying(32) | not null eventid | character varying(40) | not null msgseq| character varying(32) | not null eventdate | timestamp(0) without time zone | not null hollyid | character varying(20) | ownerid | character varying(60) | spownerid | character varying(60) | applicationid | character varying(60) | clid | character varying(40) | dnis | character varying(40) | param | character varying(2000)| docid | character varying(40) | Triggers: insert_log_events_trigger BEFORE INSERT ON log_events FOR EACH ROW EXECUTE PROCEDURE insert_log_events() schemaname | tablename| size_pretty | total_size_pretty ++-+--- public | log_events_y2010m02| 356 GB | 610 GB -Original Message- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Monday, March 01, 2010 12:58 AM To: Plugge, Joe R. Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Autovacuum Tuning advice On Sun, Feb 28, 2010 at 8:09 PM, Plugge, Joe R. jrplu...@west.com wrote: I have a very busy system that takes about 9 million inserts per day and each record gets updated at least once after the insert (all for the one same table), there are other tables that get hit but not as severely. As suspected I am having a problem with table bloat. Any advice on how to be more aggressive with autovacuum? I am using 8.4.1. My machine has 4 Intel Xeon 3000 MHz Processors with 8 GB of Ram. What kind of drive system do you have? That's far more important than CPU and RAM. Let's look at a two pronged attack. 1: What can you maybe do to reduce the number of updates for each row. if you do something like: update row set field1='xyz' where id=4; update row set field2='www' where id=4; And you can combine those updates, that's a big savings. Can you benefit from HOT updates by removing some indexes? Updating indexed fields can cost a fair bit more than updating indexed ones IF you have a 100% fill factor and therefore free room in each page for a few extra rows. 2: Vacuum tuning. Currently I am using only defaults for autovac. This one: #autovacuum_vacuum_cost_delay = 20ms is very high for a busy system with a powerful io subsystem. I run my production servers with 1ms to 4ms so they can keep up. Lastly there are some settings you can make per table for autovac you can look into (i.e. set cost_delay to 0 for this table), or you can turn off autovac for this one table and then run a regular vac with no cost_delay on it every minute or two. -- 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] Autovacuum Tuning advice
Sorry, this is a “black box” application, I am bound by what they give me as far as table layout, but I fully understand the rationale. I believe this application spent its beginnings with Oracle, which explains the blanket use of VARCHAR. From: Grzegorz Jaśkiewicz [mailto:gryz...@gmail.com] Sent: Monday, March 01, 2010 6:51 AM To: Plugge, Joe R. Cc: Scott Marlowe; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Autovacuum Tuning advice storing all fields as varchar surely doesn't make: - indicies small, - the thing fly, - tables small. ...
[PERFORM] Autovacuum Tuning advice
I have a very busy system that takes about 9 million inserts per day and each record gets updated at least once after the insert (all for the one same table), there are other tables that get hit but not as severely. As suspected I am having a problem with table bloat. Any advice on how to be more aggressive with autovacuum? I am using 8.4.1. My machine has 4 Intel Xeon 3000 MHz Processors with 8 GB of Ram. Currently I am using only defaults for autovac. shared_buffers = 768MB # min 128kB work_mem = 1MB # min 64kB maintenance_work_mem = 384MB #-- # AUTOVACUUM PARAMETERS #-- #autovacuum = on #log_autovacuum_min_duration = -1 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #autovacuum_vacuum_threshold = 50 #autovacuum_analyze_threshold = 50 #autovacuum_vacuum_scale_factor = 0.2 #autovacuum_analyze_scale_factor = 0.1 #autovacuum_freeze_max_age = 2 #autovacuum_vacuum_cost_delay = 20ms #autovacuum_vacuum_cost_limit = -1 -- 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] Performance with sorting and LIMIT on partitioned table
On Mon, Oct 19, 2009 at 6:58 AM, Joe Uhl joe...@gmail.com wrote: I have a similar, recent thread titled Partitioned Tables and ORDER BY with a decent break down. I think I am hitting the same issue Michal is. Essentially doing a SELECT against the parent with appropriate constraint columns in the WHERE clause is very fast (uses index scans against correct child table only) but the moment you add an ORDER BY it seems to be merging the parent (an empty table) and the child, sorting the results, and sequential scanning. So it does still scan only the appropriate child table in the end but indexes are useless. Unfortunately the only workaround I can come up with is to query the partitioned child tables directly. In my case the partitions are rather large so the timing difference is 522ms versus 149865ms. These questions are all solvable depending on what you define 'solution' as. I would at this point be thinking in terms of wrapping the query in a function using dynamic sql in plpgsql...using some ad hoc method of determining which children to hit and awkwardly looping them and enforcing limit, ordering, etc at that level. Yes, it sucks, but it only has to be done for classes of queries constraint exclusion can't handle and you will only handle a couple of cases most likely. For this reason, when I set up my partitioning strategies, I always try to divide the data such that you rarely if ever, have to fire queries that have to touch multiple partitions simultaneously. merlin This definitely sounds like a workable approach. I am doing something a little similar on the insert/update side to trick hibernate into writing data correctly into partitioned tables when it only knows about the parent. For anyone else hitting this issue and using hibernate my solution on the select side ended up being session-specific hibernate interceptors that rewrite the from clause after hibernate prepares the statement. This seems to be working alright especially since in our case the code, while not aware of DB partitioning, has the context necessary to select the right partition under the hood. Thankfully we haven't yet had queries that need to hit multiple partitions so this works okay without too much logic for now. I suppose if I needed to go multi-partition on single queries and wanted to continue down the hibernate interceptor path I could get more creative with the from clause rewriting and start using UNIONs, or switch to a Postgres-level solution like you are describing.
Re: [PERFORM] Partitioned Tables and ORDER BY
This seems like a pretty major weakness in PostgreSQL partitioning. I have essentially settled on not being able to do queries against the parent table when I want to order the results. Going to have to use a Hibernate interceptor or something similar to rewrite the statements so they hit specific partitions, will be working on this in the coming week. This weakness is a bummer though as it makes partitions a lot less useful. Having to hit specific child tables by name isn't much different than just creating separate tables and not using partitions at all. Michal Szymanski wrote: I've described our problem here http://groups.google.pl/group/pgsql.performance/browse_thread/thread/54a7419381bd1565?hl=pl# Michal Szymanski http://blog.szymanskich.net http://techblog.freeconet.pl/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Partitioned Tables and ORDER BY
We have been using partitioning for some time with great success. Up until now our usage has not included ordering and now that we are trying to use an ORDER BY against an indexed column a rather significant shortcoming seems to be kicking in. Parent table (have cut all but 4 columns to make it easier to post about) CREATE TABLE people ( person_id character varying(36) NOT NULL, list_id character varying(36) NOT NULL, first_name character varying(255), last_name character varying(255), CONSTRAINT people_pkey (person_id, list_id) ); A partition looks like this: CREATE TABLE people_list1 ( -- inherited columns omitted CONSTRAINT people_list1_list_id_check CHECK (list_id::text = 'the_unique_list_id'::text) ) INHERITS (people); Both the parent and the children have indexes on all 4 columns mentioned above. The parent table is completely empty. If I run this query, directly against the partition, performance is excellent: select * from people_list1 order by first_name asc limit 50; The explain analyze output: Limit (cost=0.00..4.97 rows=50 width=34315) (actual time=49.616..522.464 rows=50 loops=1) - Index Scan using idx_people_first_name_list1 on people_list1 (cost=0.00..849746.98 rows=8544854 width=34315) (actual time=49.614..522.424 rows=50 loops=1) Total runtime: 522.773 ms If I run this query, against the parent, performance is terrible: select * from people where list_id = 'the_unique_list_id' order by first_name asc limit 50; The explain analyze output: Limit (cost=726844.88..726845.01 rows=50 width=37739) (actual time=149864.869..149864.884 rows=50 loops=1) - Sort (cost=726844.88..748207.02 rows=8544855 width=37739) (actual time=149864.868..149864.876 rows=50 loops=1) Sort Key: public.people.first_name Sort Method: top-N heapsort Memory: 50kB - Result (cost=0.00..442990.94 rows=8544855 width=37739) (actual time=0.081..125837.332 rows=8545138 loops=1) - Append (cost=0.00..442990.94 rows=8544855 width=37739) (actual time=0.079..03.743 rows=8545138 loops=1) - Index Scan using people_pkey on people (cost=0.00..4.27 rows=1 width=37739) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: ((list_id)::text = 'the_unique_list_id'::text) - Seq Scan on people_list1 people (cost=0.00..442986.67 rows=8544854 width=34315) (actual time=0.068..109781.308 rows=8545138 loops=1) Filter: ((list_id)::text = 'the_unique_list_id'::text) Total runtime: 149865.411 ms Just to show that partitions are setup correctly, this query also has excellent performance: select * from people where list_id = 'the_unique_list_id' and first_name = 'JOE'; Here is the explain analyze for that: Result (cost=0.00..963.76 rows=482 width=37739) (actual time=6.031..25.394 rows=2319 loops=1) - Append (cost=0.00..963.76 rows=482 width=37739) (actual time=6.029..21.340 rows=2319 loops=1) - Index Scan using idx_people_first_name on people (cost=0.00..4.27 rows=1 width=37739) (actual time=0.010..0.010 rows=0 loops=1) Index Cond: ((first_name)::text = 'JOE'::text) Filter: ((list_id)::text = 'the_unique_list_id'::text) - Bitmap Heap Scan on people_list1 people (cost=8.47..959.49 rows=481 width=34315) (actual time=6.018..20.968 rows=2319 loops=1) Recheck Cond: ((first_name)::text = 'JOE'::text) Filter: ((list_id)::text = 'the_unique_list_id'::text) - Bitmap Index Scan on idx_people_first_name_list1 (cost=0.00..8.35 rows=481 width=0) (actual time=5.566..5.566 rows=2319 loops=1) Index Cond: ((first_name)::text = 'JOE'::text) Total runtime: 25.991 ms This is Postgres 8.3.7 on the 2.6.28 kernel with constraint_exclusion on. Our partitions are in the 8 - 15 million row range. I realize one option is to hit the partition directly instead of hitting the parent table with the check constraint in the WHERE clause, but up until now we have been able to avoid needing partition-awareness in our code. Perhaps we have hit upon something that will require breaking that cleanliness but wanted to see if there were any workarounds. -- 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] Best suiting OS
S Arvind wrote: Hi everyone, What is the best Linux flavor for server which runs postgres alone. The postgres must handle greater number of database around 200+. Performance on speed is the vital factor. Is it FreeBSD, CentOS, Fedora, Redhat xxx?? -Arvind S We use Arch Linux and love it. It does not have versions - you just keep updating your install and never have to do a major version upgrade. It is a bare bones distribution with excellent package management and repositories, virtually no distribution cruft, and a fantastic community/wiki/forum. As a warning no one offers support for Arch that I know of and the packages are generally very current with the latest which is both a good and bad thing. For a production environment you have to be very careful about when you do upgrades and preferably can test upgrades on QA machines before running on production. You also want to make sure and exclude postgresql from updates so that it doesn't do something like pull down 8.4 over an 8.3.x installation without you being backed up and ready to restore. PostgreSQL is currently at 8.4.1 in their repositories. With that disclaimer out of the way it is my favorite Linux distribution and I am running it on a couple dozens servers at the moment ranging from puny app servers to 8 core, 32GB+ RAM, 30-40 disk database servers. If you are comfortable with Linux it is worth checking out (on your personal machine or QA environment first). I've run dozens of distributions and this works well for us (a startup with nontrivial Linux experience). I imagine at a larger company it definitely would not be an option. Joe Uhl -- 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] High CPU Utilization
On Mar 20, 2009, at 4:58 PM, Scott Marlowe wrote: On Fri, Mar 20, 2009 at 2:49 PM, Joe Uhl joe...@gmail.com wrote: On Mar 20, 2009, at 4:29 PM, Scott Marlowe wrote: What does the cs entry on vmstat say at this time? If you're cs is skyrocketing then you're getting a context switch storm, which is usually a sign that there are just too many things going on at once / you've got an old kernel things like that. cs column (plus cpu columns) of vmtstat 1 30 reads as follows: csus sy id wa 11172 95 4 1 0 12498 94 5 1 0 14121 91 7 1 1 11310 90 7 1 1 12918 92 6 1 1 10613 93 6 1 1 9382 94 4 1 1 14023 89 8 2 1 10138 92 6 1 1 11932 94 4 1 1 15948 93 5 2 1 12919 92 5 3 1 10879 93 4 2 1 14014 94 5 1 1 9083 92 6 2 0 11178 94 4 2 0 10717 94 5 1 0 9279 97 2 1 0 12673 94 5 1 0 8058 82 17 1 1 8150 94 5 1 1 11334 93 6 0 0 13884 91 8 1 0 10159 92 7 0 0 9382 96 4 0 0 11450 95 4 1 0 11947 96 3 1 0 8616 95 4 1 0 10717 95 3 1 0 We are running on 2.6.28.7-2 kernel. I am unfamiliar with vmstat output but reading the man page (and that cs = context switches per second) makes my numbers seem very high. No, those aren't really all that high. If you were hitting cs contention, I'd expect it to be in the 25k to 100k range. 10k average under load is pretty reasonable. Our sum JDBC pools currently top out at 400 connections (and we are doing work on all 400 right now). I may try dropping those pools down even smaller. Are there any general rules of thumb for figuring out how many connections you should service at maximum? I know of the memory constraints, but thinking more along the lines of connections per CPU core. Well, maximum efficiency is usually somewhere in the range of 1 to 2 times the number of cores you have, so trying to get the pool down to a dozen or two connections would be the direction to generally head. May not be reasonable or doable though. Turns out we may have an opportunity to purchase a new database server with this increased load. Seems that the best route, based on feedback to this thread, is to go whitebox, get quad opterons, and get a very good disk controller. Can anyone recommend a whitebox vendor? Is there a current controller anyone on this list has experience with that they could recommend? This will be a bigger purchase so will be doing research and benchmarking but any general pointers to a vendor/controller greatly appreciated. -- 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] High CPU Utilization
On Mar 17, 2009, at 12:19 AM, Greg Smith wrote: On Tue, 17 Mar 2009, Gregory Stark wrote: Hm, well the tests I ran for posix_fadvise were actually on a Perc5 -- though who knows if it was the same under the hood -- and I saw better performance than this. I saw about 4MB/s for a single drive and up to about 35MB/s for 15 drives. However this was using linux md raid-0, not hardware raid. Right, it's the hardware RAID on the Perc5 I think people mainly complain about. If you use it in JBOD mode and let the higher performance CPU in your main system drive the RAID functions it's not so bad. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD I have not yet had a chance to try software raid on the standby server (still planning to) but wanted to follow up to see if there was any good way to figure out what the postgresql processes are spending their CPU time on. We are under peak load right now, and I have Zabbix plotting CPU utilization and CPU wait (from vmstat output) along with all sorts of other vitals on charts. CPU utilization is a sustained 90% - 95% and CPU Wait is hanging below 10%. Since being pointed at vmstat by this list I have been watching CPU Wait and it does get high at times (hence still wanting to try Perc5 in JBOD) but then there are sustained periods, right now included, where our CPUs are just getting crushed while wait and IO (only doing about 1.5 MB/sec right now) are very low. This high CPU utilization only occurs when under peak load and when our JDBC pools are fully loaded. We are moving more things into our cache and constantly tuning indexes/tables but just want to see if there is some underlying cause that is killing us. Any recommendations for figuring out what our database is spending its CPU time on? -- 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] High CPU Utilization
On Mar 20, 2009, at 4:29 PM, Scott Marlowe wrote: On Fri, Mar 20, 2009 at 2:26 PM, Joe Uhl joe...@gmail.com wrote: On Mar 17, 2009, at 12:19 AM, Greg Smith wrote: On Tue, 17 Mar 2009, Gregory Stark wrote: Hm, well the tests I ran for posix_fadvise were actually on a Perc5 -- though who knows if it was the same under the hood -- and I saw better performance than this. I saw about 4MB/s for a single drive and up to about 35MB/s for 15 drives. However this was using linux md raid-0, not hardware raid. Right, it's the hardware RAID on the Perc5 I think people mainly complain about. If you use it in JBOD mode and let the higher performance CPU in your main system drive the RAID functions it's not so bad. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD I have not yet had a chance to try software raid on the standby server (still planning to) but wanted to follow up to see if there was any good way to figure out what the postgresql processes are spending their CPU time on. We are under peak load right now, and I have Zabbix plotting CPU utilization and CPU wait (from vmstat output) along with all sorts of other vitals on charts. CPU utilization is a sustained 90% - 95% and CPU Wait is hanging below 10%. Since being pointed at vmstat by this list I have been watching CPU Wait and it does get high at times (hence still wanting to try Perc5 in JBOD) but then there are sustained periods, right now included, where our CPUs are just getting crushed while wait and IO (only doing about 1.5 MB/sec right now) are very low. This high CPU utilization only occurs when under peak load and when our JDBC pools are fully loaded. We are moving more things into our cache and constantly tuning indexes/tables but just want to see if there is some underlying cause that is killing us. Any recommendations for figuring out what our database is spending its CPU time on? What does the cs entry on vmstat say at this time? If you're cs is skyrocketing then you're getting a context switch storm, which is usually a sign that there are just too many things going on at once / you've got an old kernel things like that. cs column (plus cpu columns) of vmtstat 1 30 reads as follows: csus sy id wa 11172 95 4 1 0 12498 94 5 1 0 14121 91 7 1 1 11310 90 7 1 1 12918 92 6 1 1 10613 93 6 1 1 9382 94 4 1 1 14023 89 8 2 1 10138 92 6 1 1 11932 94 4 1 1 15948 93 5 2 1 12919 92 5 3 1 10879 93 4 2 1 14014 94 5 1 1 9083 92 6 2 0 11178 94 4 2 0 10717 94 5 1 0 9279 97 2 1 0 12673 94 5 1 0 8058 82 17 1 1 8150 94 5 1 1 11334 93 6 0 0 13884 91 8 1 0 10159 92 7 0 0 9382 96 4 0 0 11450 95 4 1 0 11947 96 3 1 0 8616 95 4 1 0 10717 95 3 1 0 We are running on 2.6.28.7-2 kernel. I am unfamiliar with vmstat output but reading the man page (and that cs = context switches per second) makes my numbers seem very high. Our sum JDBC pools currently top out at 400 connections (and we are doing work on all 400 right now). I may try dropping those pools down even smaller. Are there any general rules of thumb for figuring out how many connections you should service at maximum? I know of the memory constraints, but thinking more along the lines of connections per CPU core. -- 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] High CPU Utilization
On Mar 20, 2009, at 4:58 PM, Scott Marlowe wrote: On Fri, Mar 20, 2009 at 2:49 PM, Joe Uhl joe...@gmail.com wrote: On Mar 20, 2009, at 4:29 PM, Scott Marlowe wrote: What does the cs entry on vmstat say at this time? If you're cs is skyrocketing then you're getting a context switch storm, which is usually a sign that there are just too many things going on at once / you've got an old kernel things like that. cs column (plus cpu columns) of vmtstat 1 30 reads as follows: csus sy id wa 11172 95 4 1 0 12498 94 5 1 0 14121 91 7 1 1 11310 90 7 1 1 12918 92 6 1 1 10613 93 6 1 1 9382 94 4 1 1 14023 89 8 2 1 10138 92 6 1 1 11932 94 4 1 1 15948 93 5 2 1 12919 92 5 3 1 10879 93 4 2 1 14014 94 5 1 1 9083 92 6 2 0 11178 94 4 2 0 10717 94 5 1 0 9279 97 2 1 0 12673 94 5 1 0 8058 82 17 1 1 8150 94 5 1 1 11334 93 6 0 0 13884 91 8 1 0 10159 92 7 0 0 9382 96 4 0 0 11450 95 4 1 0 11947 96 3 1 0 8616 95 4 1 0 10717 95 3 1 0 We are running on 2.6.28.7-2 kernel. I am unfamiliar with vmstat output but reading the man page (and that cs = context switches per second) makes my numbers seem very high. No, those aren't really all that high. If you were hitting cs contention, I'd expect it to be in the 25k to 100k range. 10k average under load is pretty reasonable. Our sum JDBC pools currently top out at 400 connections (and we are doing work on all 400 right now). I may try dropping those pools down even smaller. Are there any general rules of thumb for figuring out how many connections you should service at maximum? I know of the memory constraints, but thinking more along the lines of connections per CPU core. Well, maximum efficiency is usually somewhere in the range of 1 to 2 times the number of cores you have, so trying to get the pool down to a dozen or two connections would be the direction to generally head. May not be reasonable or doable though. Thanks for the info. Figure I can tune our pools down and monitor throughput/CPU/IO and look for a sweet spot with our existing hardware. Just wanted to see if tuning connections down could potentially help. I feel as though we are going to have to replicate this DB before too long. We've got an almost identical server doing nothing but PITR with 8 CPU cores mostly idle that could be better spent. Our pgfouine reports, though only logging queries that take over 1 second, show 90% reads. I have heard much about Slony, but has anyone used the newer version of Mammoth Replicator (or looks to be called PostgreSQL + Replication now) on 8.3? From the documentation, it appears to be easier to set up and less invasive but I struggle to find usage information/stories online. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] High CPU Utilization
Our production database is seeing very heavy CPU utilization - anyone have any ideas/input considering the following? CPU utilization gradually increases during the day until it approaches 90%-100% at our peak time. When this happens our transactions/sec drops and our site becomes very slow. When in this state, I can see hundreds of queries in pg_stat_activity that are not waiting on locks but sit there for minutes. When the database is not in this state, those same queries can complete in fractions of a second - faster that my script that watches pg_stat_activity can keep track of them. This server has dual quad core xeon 5310s, 32 GB RAM, and a few different disk arrays (all managed in hardware by either the Perc5/i or Perc5/e adapter). The Postgres data is on a 14 disk 7.2k SATA raid 10. This server runs nothing but Postgres. The PostgreSQL database (according to pg_database_size) is 55GB and we are running PostgreSQL 8.3.5 and the 2.6.28.7-2 kernel under Arch Linux. Right now (not under peak load) this server is running at 68% CPU utilization and its SATA raid 10 is doing about 2MB/s writes and 11MB/ s reads. When I run dd I can hit 200+MB/s writes and 230+ MB/s reads, so we are barely using the available IO. Further when I run dd the CPU utilization of that process only approaches 20%-30% of one core. Additionally, when I view top -c I generally see a dozen or so idle postgres processes (they appear and drop away quickly though) consuming very large chunks of CPU (as much as 60% of a core). At any given time we have hundreds of idle postgres processes due to the JDBC connection pooling but most of them are 0% as I would expect them to be. I also see selects and inserts consuming very large percentages of CPU but I view that as less strange since they are doing work. Any ideas as to what is causing our CPUs to struggle? Is the fact that our RAM covers a significant portion of the database causing our CPUs to do a bunch of thrashing as they work with memory while our disk controllers sit idle? According to top we barely use any swap. We currently have max_connections set to 1000 (roughly the sum of the JDBC pools on our application servers). Would decreasing this value help? We can decrease the JDBC pools or switch to pgbouncer for pooling if this is the case. Really just looking for any input/ideas. Our workload is primarily OLTP in nature - essentially a social network. By transactions/sec at the start I am using the xact_commit value in pg_stat_database. Please let me know if this value is not appropriate for getting a tps guess. Right now with the 60% CPU utilization and low IO use xact_commit is increasing at a rate of 1070 a second. I have an identical PITR slave I can pause the PITR sync on to run any test against. I will happily provide any additional information that would be helpful. Any assistance is greatly appreciated. Joe Uhl -- 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] High CPU Utilization
Here is vmstat 1 30. We are under peak load right now so I can gather information from the real deal :) Had an almost complete lockup a moment ago, number of non-idle postgres connections was 637. Going to drop our JDBC pool sizes a bit and bounce everything. procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 12 35 95056 11102380 56856 1495494834 669 54112 23 3 54 19 12 39 95056 11092484 56876 1496320400 6740 1204 10066 13277 91 5 0 4 8 42 95056 11081712 56888 1497224400 8620 1168 10659 17020 78 6 0 15 10 30 95052 11069768 56904 1498262800 8944 976 9809 15109 81 6 1 12 4 27 95048 11059576 56916 1499129600 8852 440 7652 13294 63 4 2 32 5 42 95048 11052524 56932 1499649600 4700 384 6383 11249 64 4 4 28 5 33 95048 11047492 56956 1500142800 3852 572 6029 14010 36 4 5 56 7 35 95048 11041184 56960 1500548000 3964 136 5042 10802 40 3 1 56 1 33 95048 11037988 56968 1500924000 3892 168 3384 6479 26 1 3 69 3 28 95048 11029332 56980 1501574400 6724 152 4964 12844 11 2 8 79 0 34 95048 11025880 56988 1502016800 3852 160 3616 8614 11 1 6 82 3 25 95048 10996356 57044 1504479600 7892 456 3126 7115 4 3 8 85 1 26 95048 10991692 57052 1505010000 5188 176 2566 5976 3 2 12 83 0 29 95048 10985408 57060 1505496800 420080 2586 6582 4 1 12 83 1 29 95048 10980828 57064 1505899200 456064 2966 7557 7 2 6 85 2 28 95048 10977192 57072 1506317600 386072 2695 6742 11 1 7 81 2 29 95048 10969120 57088 1506780800 508484 3296 8067 14 1 0 84 0 25 95048 10962096 57104 1507298400 4440 500 2721 6263 12 1 6 80 0 23 95044 10955320 57108 1507926000 5712 232 2678 5990 6 1 6 87 2 25 95044 10948644 57120 1508452400 5120 184 3499 8143 20 3 9 69 3 21 95044 10939744 57128 1509064400 5756 264 4724 10272 32 3 5 60 1 19 95040 10933196 57144 15095024 120 4440 180 2585 5244 13 2 15 70 0 21 95040 10927596 57148 1509868400 3248 136 2973 7292 8 1 9 81 1 20 95040 10920708 57164 1510424400 5192 360 1865 4547 3 1 9 87 1 24 95040 10914552 57172 1510585600 230816 1948 4450 6 1 1 93 0 24 95036 10909148 57176 1511024000 3824 152 1330 2632 3 1 6 90 1 21 95036 10900628 57192 1511633200 5680 180 1898 3986 4 1 11 84 0 19 95036 10888356 57200 1512173600 5952 120 2252 3991 12 1 8 79 2 22 95036 10874336 57204 1512825200 6320 112 2831 6755 5 2 8 85 3 26 95036 10857592 57220 1513402000 5124 216 3067 5296 32 6 3 59 Alan, my apologies if you get this twice. Didn't reply back to the list on first try. On Mar 16, 2009, at 3:52 PM, Alan Hodgson wrote: On Monday 16 March 2009, Joe Uhl joe...@gmail.com wrote: Right now (not under peak load) this server is running at 68% CPU utilization and its SATA raid 10 is doing about 2MB/s writes and 11MB/ s reads. When I run dd I can hit 200+MB/s writes and 230+ MB/s reads, so we are barely using the available IO. Further when I run dd the CPU utilization of that process only approaches 20%-30% of one core. What does vmstat say when it's slow? The output of vmstat 1 30 would be informative. note: dd is sequential I/O. Normal database usage is random I/O. -- Even a sixth-grader can figure out that you can’t borrow money to pay off your debt -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] High CPU Utilization
I dropped the pool sizes and brought things back up. Things are stable, site is fast, CPU utilization is still high. Probably just a matter of time before issue comes back (we get slammed as kids get out of school in the US). Now when I run vmtstat 1 30 it looks very different (below). Waiting is minimal, user is very high. Under nontrivial load, according to xact_commit in pg_stat_database we are doing 1800+ tps. Appreciate the input and explanation on vmstat. I am going to throw some of these numbers into zabbix so I can keep a better eye on them. This server is a couple years old so the purchase of a new controller and/or disks is not out of the question. On final note, have made several changes to postgresql.conf. Some of those here: max_connections = 1000 shared_buffers = 7680MB work_mem = 30MB synchronous_commit = off checkpoint_segments = 50 effective_cache_size = 2MB procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 9 8 73036 500164 82200 2349774834 669 54111 23 3 54 19 20 4 73036 497252 82200 2350083600 2500 680 11145 15168 91 4 2 2 21 1 73036 491416 82204 2350383200 1916 920 10303 14032 94 4 1 1 23 5 73036 489580 82212 2350586000 1348 3296 11682 15970 94 5 1 0 31 1 73036 481408 82220 2350775200 984 8988 10123 11289 97 3 0 0 25 4 73036 483248 82232 2350942000 1268 1312 10705 14063 96 4 0 0 23 4 73036 480096 82232 2351238000 2372 472 9805 13996 94 5 1 1 24 4 73036 476732 82236 2351519600 2012 720 10365 14307 96 3 1 0 22 1 73036 474468 82236 2351658400 944 3108 9838 12831 95 4 1 0 14 1 73036 455756 82284 2353454800 908 3284 9096 11333 94 4 1 0 10 2 73036 455224 82292 2353630400 1760 416 12454 17736 89 6 3 2 17 0 73036 460620 82292 235300 1292 968 12030 18333 90 7 2 1 13 4 73036 459764 82292 2353972400 332 288 9722 14197 92 5 2 1 17 5 73036 457516 82292 2354217600 1872 17752 10458 15465 91 5 2 1 19 4 73036 450804 82300 2354564000 2980 640 10602 15621 90 6 2 2 24 0 73036 447660 82312 2354764400 1736 10724 12401 15413 93 6 1 0 20 6 73036 444380 82320 2355069200 2064 476 9008 10985 94 4 1 0 22 2 73036 442880 82328 2355364000 2496 3156 10739 15211 93 5 1 1 11 1 73036 441448 82328 2355563200 1452 3552 10812 15337 93 5 2 1 6 2 73036 439812 82348 2355742000 1052 1128 8603 10514 91 3 3 2 6 3 73036 433456 82348 2356086000 2484 656 7636 13033 68 4 14 14 6 3 73036 433084 82348 2356262800 1400 408 6046 11778 70 3 18 9 5 0 73036 430776 82356 2356426400 1108 1300 7549 13754 73 4 16 7 5 2 73036 430124 82360 2356558000 1016 2216 7844 14507 72 4 18 6 4 2 73036 429652 82380 2356748000 1168 2468 7694 15466 58 4 24 14 6 2 73036 427304 82384 2356966800 1132 752 5993 13606 49 5 36 10 7 1 73036 423020 82384 2357193200 1244 824 8085 18072 56 3 30 10 procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 4 0 73036 420816 82392 2357382400 1292 820 5370 10958 46 2 41 10 9 1 73020 418048 82392 23576900 520 1632 2592 5931 11629 60 3 29 8 4 2 73004 415164 82424 23578620 560 1812 4116 7503 14674 71 3 15 12 On Mar 16, 2009, at 4:19 PM, Dave Youatt wrote: Last column wa is % cpu time spent waiting (for IO to complete). 80s and 90s is pretty high, probably too high. Might also want to measure the IO/s performance of your RAID controller. From the descriptions, it will be much more important that long sequential reads/writes for characterizing your workload. There are also some disappointing HW RAID controllers out there. Generally, Aretec and Promise are good, Adaptec good, depending on model, and the ones that Dell ship w/their servers haven't had good reviews/reports. On 03/16/2009 01:10 PM, Joe Uhl wrote: Here is vmstat 1 30. We are under peak load right now so I can gather information from the real deal :) Had an almost complete lockup a moment ago, number of non-idle postgres connections was 637. Going to drop our JDBC pool sizes a bit and bounce everything. procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 12 35 95056 11102380 56856 1495494834 669 54112 23 3 54 19 12 39 95056 11092484 56876 1496320400 6740 1204 10066 13277 91 5 0 4 8 42 95056 11081712 56888
Re: [PERFORM] crosstab speed
Jeremiah Elliott wrote: ok, I have an application that I am trying to speed up. Its a reporting application that makes heavy use of the crosstab function. snip here is an example query The crostab function is taking between 5 and 15 seconds to return. Please run the two embedded queries independently, i.e. select ARRAY[site::text,product_line_description::text,report_sls::text,fy_period::text] as COL_HEADER, fy_year, sum(invoice_value) from order_data_tbl where fy_year is not null group by site::text,product_line_description::text,report_sls::text,fy_period::text, fy_year order by site::text,product_line_description::text,report_sls::text,fy_period::text; -- and -- select fy_year from order_data_tbl where fy_year is not null group by fy_year order by fy_year; How long does each take? crosstab cannot run any faster than the sum of these two queries run on their own. If the second one doesn't change often, can you pre-calculate it, perhaps once a day? Joe -- 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] hardware and For PostgreSQL
Magnus Hagander wrote: Ron St-Pierre wrote: Joe Uhl wrote: I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. We have a similar piece of equipment from Dell (the PowerEdge), and when we had a problem with it we received excellent service from them. When our raid controller went down (machine 1 year old), Dell helped to diagnose the problem and installed a new one at our hosting facility, all within 24 hours. 24 hours?! I have a new one for my HP boxes onsite in 4 hours, including a tech if needed... But I assume Dell also has service-agreement deals you can get to get the level of service you'd want. (But you won't get it for a non-brand-name server, most likely) Bottom line - don't underestimate the service you get from the vendor when something breaks. Because eventually, something *will* break. //Magnus Yeah the response time depends on the service level purchased. I generally go with 24 hour because everything is redundant so a day of downtime isn't going to bring services down (though it could make them slow depending on what fails) but you can purchase 4 hr and in some cases even 2 hr. I had a gold level support contract on a server that failed awhile back and within 3 net hours they diagnosed and fixed the problem by getting onsite and replacing the motherboard and a cpu. I haven't had any of our 24hr support level devices fail yet so don't have anything to compare there. If you do go with Dell and want the higher support contracts i'll restate that a small business account is the way to go. Typically the prices are better to the point that a support level upgrade appears free when compared to the best shopping cart combo I can come up with. Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] hardware and For PostgreSQL
I realize there are people who discourage looking at Dell, but i've been very happy with a larger ball of equipment we ordered recently from them. Our database servers consist of a PowerEdge 2950 connected to a PowerVault MD1000 with a 1 meter SAS cable. The 2950 tops out at dual quad core cpus, 32 gb ram, and 6 x 3.5 drives. It has a Perc 5/i as the controller of the in-box disks but then also has room for 2 Perc 5/e controllers that can allow connecting up to 2 chains of disk arrays to the thing. In our environment we started the boxes off at 8gb ram with 6 15k SAS disks in the server and then connected an MD1000 with 15 SATA disks to one of the Perc 5/e controllers. Gives tons of flexibility for growth and for tablespace usage depending on budget and what you can spend on your disks. We have everything on the SATA disks right now but plan to start moving the most brutalized indexes to the SAS disks very soon. If you do use Dell, get connected with a small business account manager for better prices and more attention. Joe Ketema Harris wrote: I am trying to build a very Robust DB server that will support 1000+ concurrent users (all ready have seen max of 237 no pooling being used). i have read so many articles now that I am just saturated. I have a general idea but would like feedback from others. I understand query tuning and table design play a large role in performance, but taking that factor away and focusing on just hardware, what is the best hardware to get for Pg to work at the highest level (meaning speed at returning results)? How does pg utilize multiple processors? The more the better? Are queries spread across multiple processors? Is Pg 64 bit? If so what processors are recommended? I read this : http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/node12.html POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process. Because of this, all multi-cpu operating systems can spread multiple database connections among the available CPUs. However, if only a single database connection is active, it can only use one CPU. POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs. Its pretty old (2003) but is it still accurate? if this statement is accurate how would it affect connection pooling software like pg_pool? RAM? The more the merrier right? Understanding shmmax and the pg config file parameters for shared mem has to be adjusted to use it. Disks? standard Raid rules right? 1 for safety 5 for best mix of performance and safety? Any preference of SCSI over SATA? What about using a High speed (fibre channel) mass storage device? Who has built the biggest baddest Pg server out there and what do you use? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN vs Internal Disks
Scott Marlowe wrote: On 9/6/07, Harsh Azad [EMAIL PROTECTED] wrote: Hi, How about the Dell Perc 5/i card, 512MB battery backed cache or IBM ServeRAID-8k Adapter? All Dell Percs have so far been based on either adaptec or LSI controllers, and have ranged from really bad to fairly decent performers. There were some recent posts on this list where someone was benchmarking one, I believe. searching the list archives might prove useful. I am not at all familiar with IBM's ServeRAID controllers. Do either of these come with or have the option for battery back module for the cache? I hope I am sending relevant information here, I am not too well versed with RAID controllers. Yep. Def look for a chance to evaluate whichever ones you're considering. The Areca's are in the same price range as the IBM controller you're considering, maybe a few hundred dollars more. See if you can get one for review while looking at these other controllers. I'd recommend against Dell unless you're at a company that orders computers by the hundred lot. My experience with Dell has been that unless you are a big customer you're just another number (a small one at that) on a spreadsheet. If you do go with Dell get connected with an account manager instead of ordering online. You work with the same people every time you have an order and in my experience they can noticeably beat the best prices I can find. This is definitely the way to go if you don't want to get lost in the volume. The group I have worked with for the past ~2 years is very responsive, remembers me and my company across the 3 - 6 month gaps between purchases, and the server/storage person in the group is reasonably knowledgeable and helpful. This is for small lots of machines, our first order was just 2 boxes and i've only placed 4 orders total in the past 2 years. Just my personal experience, i'd be happy to pass along the account manager's information if anyone is interested. ---(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 Joe Uhl [EMAIL PROTECTED] ---(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
[PERFORM] Dell Hardware Recommendations
We have a 30 GB database (according to pg_database_size) running nicely on a single Dell PowerEdge 2850 right now. This represents data specific to 1 US state. We are in the process of planning a deployment that will service all 50 US states. If 30 GB is an accurate number per state that means the database size is about to explode to 1.5 TB. About 1 TB of this amount would be OLAP data that is heavy-read but only updated or inserted in batch. It is also largely isolated to a single table partitioned on state. This portion of the data will grow very slowly after the initial loading. The remaining 500 GB has frequent individual writes performed against it. 500 GB is a high estimate and it will probably start out closer to 100 GB and grow steadily up to and past 500 GB. I am trying to figure out an appropriate hardware configuration for such a database. Currently I am considering the following: PowerEdge 1950 paired with a PowerVault MD1000 2 x Quad Core Xeon E5310 16 GB 667MHz RAM (4 x 4GB leaving room to expand if we need to) PERC 5/E Raid Adapter 2 x 146 GB SAS in Raid 1 for OS + logs. A bunch of disks in the MD1000 configured in Raid 10 for Postgres data. The MD1000 holds 15 disks, so 14 disks + a hot spare is the max. With 12 250GB SATA drives to cover the 1.5TB we would be able add another 250GB of usable space for future growth before needing to get a bigger set of disks. 500GB drives would leave alot more room and could allow us to run the MD1000 in split mode and use its remaining disks for other purposes in the mean time. I would greatly appreciate any feedback with respect to drive count vs. drive size and SATA vs. SCSI/SAS. The price difference makes SATA awfully appealing. We plan to involve outside help in getting this database tuned and configured, but want to get some hardware ballparks in order to get quotes and potentially request a trial unit. Any thoughts or recommendations? We are running openSUSE 10.2 with kernel 2.6.18.2-34. Regards, Joe Uhl [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Dell Hardware Recommendations
Thanks for the input. Thus far we have used Dell but I would certainly be willing to explore other options. I found a Reference Guide for the MD1000 from April, 2006 that includes info on the PERC 5/E at: http://www.dell.com/downloads/global/products/pvaul/en/pvaul_md1000_solutions_guide.pdf To answer the questions below: How many users do you expect to hit the db at the same time? There are 2 types of users. For roughly every 5000 active accounts, 10 or fewer or those will have additional privileges. Only those more privileged users interact substantially with the OLAP portion of the database. For 1 state 10 concurrent connections was about the max, so if that holds for 50 states we are looking at 500 concurrent users as a top end, with a very small fraction of those users interacting with the OLAP portion. How big of a dataset will each one be grabbing at the same time? For the OLTP data it is mostly single object reads and writes and generally touches only a few tables at a time. Will your Perc RAID controller have a battery backed cache on board? If so (and it better!) how big of a cache can it hold? According to the above link, it has a 256 MB cache that is battery backed. Can you split this out onto two different machines, one for the OLAP load and the other for what I'm assuming is OLTP? Can you physically partition this out by state if need be? Right now this system isn't in production so we can explore any option. We are looking into splitting the OLAP and OLTP portions right now and I imagine physically splitting the partitions on the big OLAP table is an option as well. Really appreciate all of the advice. Before we pull the trigger on hardware we probably will get some external advice from someone but I knew this list would provide some excellent ideas and feedback to get us started. Joe Uhl [EMAIL PROTECTED] On Thu, 9 Aug 2007 16:02:49 -0500, Scott Marlowe [EMAIL PROTECTED] said: On 8/9/07, Joe Uhl [EMAIL PROTECTED] wrote: We have a 30 GB database (according to pg_database_size) running nicely on a single Dell PowerEdge 2850 right now. This represents data specific to 1 US state. We are in the process of planning a deployment that will service all 50 US states. If 30 GB is an accurate number per state that means the database size is about to explode to 1.5 TB. About 1 TB of this amount would be OLAP data that is heavy-read but only updated or inserted in batch. It is also largely isolated to a single table partitioned on state. This portion of the data will grow very slowly after the initial loading. The remaining 500 GB has frequent individual writes performed against it. 500 GB is a high estimate and it will probably start out closer to 100 GB and grow steadily up to and past 500 GB. I am trying to figure out an appropriate hardware configuration for such a database. Currently I am considering the following: PowerEdge 1950 paired with a PowerVault MD1000 2 x Quad Core Xeon E5310 16 GB 667MHz RAM (4 x 4GB leaving room to expand if we need to) PERC 5/E Raid Adapter 2 x 146 GB SAS in Raid 1 for OS + logs. A bunch of disks in the MD1000 configured in Raid 10 for Postgres data. The MD1000 holds 15 disks, so 14 disks + a hot spare is the max. With 12 250GB SATA drives to cover the 1.5TB we would be able add another 250GB of usable space for future growth before needing to get a bigger set of disks. 500GB drives would leave alot more room and could allow us to run the MD1000 in split mode and use its remaining disks for other purposes in the mean time. I would greatly appreciate any feedback with respect to drive count vs. drive size and SATA vs. SCSI/SAS. The price difference makes SATA awfully appealing. We plan to involve outside help in getting this database tuned and configured, but want to get some hardware ballparks in order to get quotes and potentially request a trial unit. Any thoughts or recommendations? We are running openSUSE 10.2 with kernel 2.6.18.2-34. Some questions: How many users do you expect to hit the db at the same time? How big of a dataset will each one be grabbing at the same time? Will your Perc RAID controller have a battery backed cache on board? If so (and it better!) how big of a cache can it hold? Can you split this out onto two different machines, one for the OLAP load and the other for what I'm assuming is OLTP? Can you physically partition this out by state if need be? A few comments: I'd go with the bigger drives. Just as many, so you have spare storage as you need it. you never know when you'll need to migrate your whole data set from one pg db to another for testing etc... extra space comes in REAL handy when things aren't quite going right. With 10krpm 500 and 750 Gig drives you can use smaller partitions on the bigger drives to short stroke them and often outrun supposedly faster drives. The difference between SAS
[PERFORM] Getting Slow
About six months ago, our normally fast postgres server started having performance issues. Queries that should have been instant were taking up to 20 seconds to complete (like selects on the primary key of a table). Running the same query 4 times in a row would yield dramatically different results... 1.001 seconds, 5 seconds, 22 seconds, 0.01 seconds, to complete. At the time we upgraded the hardware and the performance problems went away. But I did not feel like we had solved the underlying problem. Now, six months later, the same thing is happening... and I'm kind of glad because now, I'd like to find out what the real issue is. I'm just starting to diagnose it so I don't know a lot yet, but what I do know, I'll share with you here in the hopes of starting off on the right track. I've already described the main symptom. Here are some other random observations: - The server log shows frequent archived transaction log file entries. Usually once every 10 minutes or so, but sometimes 2 or 3 per minute. - The server box seems otherwise to be responsive. CPU sits at about 90% idle. - When queries are especially slow, the server shows a big spike in read/write activity. - This morning I did a VACUUM ANALYZE. It seemed to help for 30 minutes or so, but then it was back to being slowish. I'd hate to schedule these because it feels more like a band-aid. For a long time we've been doing just fine with autovacuum, so why start scheduling vacuums now? Here's info about our configuration. Any advise/pointers would be much appreciated. Thanks! Computer: Mac Pro Dual Core Intel Operating System: Mac OS 10.4.7 Client Memory: 4GB RAM Data Drives: 3 drives in a software RAID (internal) Log/Backup Drive: 1 (the startup disk, internal) Postgres Version: 8.1.4 Data Size: 5.1 GB # of Tables: 60 Size of Tables: Most are under 100,000 records. A few are in the millions. Largest is 7058497. Average Number of Simultaneous Client Connections: 250 max_connections = 500 shared_buffers = 1 work_mem = 2048 max_stack_depth = 6000 effective_cache_size = 3 fsync = on wal_sync_method = fsync archive_command = 'cp -i %p /Users/postgres/officelink/wal_archive/%f /dev/null' max_fsm_pages = 15 stats_start_collector = on stats_row_level = on log_min_duration_statement = 2000 log_line_prefix = '%t %h ' superuser_reserved_connections = 3 autovacuum = on autovacuum_naptime = 60 autovacuum_vacuum_threshold = 150 autovacuum_vacuum_scale_factor = 0.0001 autovacuum_analyze_scale_factor = 0.0001 sudo pico /etc/rc sysctl -w kern.sysv.shmmax=4294967296 sysctl -w kern.sysv.shmall=1048576 sudo pico /etc/sysctl.conf kern.maxproc=2048 kern.maxprocperuid=800 kern.maxfiles=4 kern.maxfilesperproc=3 Processes: 470 total, 2 running, 4 stuck, 464 sleeping... 587 threads 13:34:50 Load Avg: 0.45, 0.34, 0.33 CPU usage: 5.1% user, 5.1% sys, 89.7% idle SharedLibs: num = 157, resident = 26.9M code, 3.29M data, 5.44M LinkEdit MemRegions: num = 15307, resident = 555M + 25.5M private, 282M shared PhysMem: 938M wired, 934M active, 2.13G inactive, 3.96G used, 43.1M free VM: 116G + 90.1M 1213436(0) pageins, 263418(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 29804 postgres 0.0% 0:03.24 1 927 1.27M 245M 175M 276M 29720 postgres 0.0% 0:01.89 1 927 1.25M 245M 125M 276M 29714 postgres 0.0% 0:03.70 11027 1.30M 245M 215M 276M 29711 postgres 0.0% 0:01.38 11027 1.21M 245M 107M 276M 29707 postgres 0.0% 0:01.27 1 927 1.16M 245M 78.2M 276M 29578 postgres 0.0% 0:01.33 1 927 1.16M 245M 67.8M 276M 29556 postgres 0.0% 0:00.39 1 927 1.09M 245M 91.8M 276M 29494 postgres 0.0% 0:00.19 1 927 1.05M 245M 26.5M 276M 29464 postgres 0.0% 0:01.98 1 927 1.16M 245M 88.8M 276M 29425 postgres 0.0% 0:01.61 1 927 1.17M 245M 112M 276M 29406 postgres 0.0% 0:01.42 1 927 1.15M 245M 118M 276M 29405 postgres 0.0% 0:00.13 1 926 924K 245M 17.9M 276M 29401 postgres 0.0% 0:00.98 11027 1.13M 245M 84.4M 276M 29400 postgres 0.0% 0:00.90 11027 1.14M 245M 78.4M 276M 29394 postgres 0.0% 0:01.56 11027 1.17M 245M 111M 276M
Re: [PERFORM] Simple query, 10 million records...MySQL ten times faster
Is there a reason you are not using postgis. The R tree indexes are designed for exactly this type of query and should be able to do it very quickly. Hope that helps, Joe I have this table: CREATE TABLE test_zip_assoc ( id serial NOT NULL, f_id integer DEFAULT 0 NOT NULL, lat_radians numeric(6,5) DEFAULT 0.0 NOT NULL, long_radians numeric(6,5) DEFAULT 0.0 NOT NULL ); CREATE INDEX lat_radians ON test_zip_assoc USING btree (lat_radians); CREATE INDEX long_radians ON test_zip_assoc USING btree (long_radians); It's basically a table that associates some foreign_key (for an event, for instance) with a particular location using longitude and latitude. I'm basically doing a simple proximity search. I have populated the database with *10 million* records. I then test performance by picking 50 zip codes at random and finding the records within 50 miles with a query like this: SELECT id FROM test_zip_assoc WHERE lat_radians 0.69014816041 AND lat_radians 0.71538026567 AND long_radians -1.35446228028 AND long_radians -1.32923017502 On my development server (dual proc/dual core Opteron 2.8 Ghz with 4GB ram) this query averages 1.5 seconds each time it runs after a brief warmup period. In PostGreSQL it averages about 15 seconds. Both of those times are too slow. I need the query to run in under a second with as many as a billion records. I don't know if this is possible but I'm really hoping someone can help me restructure my indexes (multicolumn?, multiple indexes with a 'where' clause?) so that I can get this running as fast as possible. If I need to consider some non-database data structure in RAM I will do that too. Any help or tips would be greatly appreciated. I'm willing to go to greath lengths to test this if someone can make a good suggestion that sounds like it has a reasonable chance of improving the speed of this search. There's an extensive thread on my efforts already here: http://phpbuilder.com/board/showthread.php?t=10331619page=10 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Determining server load from client
(forgot to send to list) Dan Harris wrote: architecture of the server hardware. It would be very nice if I could check the load of the server at certain intervals to throttle the number of concurrent queries and mitigate load problems when other processes might be already inducing a significant load. I have seen some other nice back-end things exposed through PG functions ( e.g. database size on disk ) and wondered if there was anything applicable to this. Even if it can't return the load average proper, is there anything else in the pg_* tables that might give me a clue how busy the server is for a period of time? I have installed munin (http://munin.projects.linpro.no/) on a few systems. This lets you look at graphs of system resources/load etc. I have also added python scripts which do sample queries to let me know if performance/index size is changing dramatically. I have attached an example script. Hope that helps, Joe #! /usr/bin/python import psycopg import sys def fixName(name): return name[:19] if len(sys.argv) 1 and sys.argv[1] == config: print graph_title Postgresql Index Sizes graph_vlabel Mb con = psycopg.connect(host=xxx user=xxx dbname=xxx password=xxx) cur = con.cursor() cur.execute(select relname, relpages from pg_class where relowner 10 and relkind='i' and relpages 256 order by reltuples desc;) results = cur.fetchall() for name, pages in results: print %s.label %s % (fixName(name), name) else: con = psycopg.connect(host=xxx user=xxx dbname=xxx password=xxx) cur = con.cursor() cur.execute(select relname, relpages from pg_class where relowner 10 and relkind='i' and relpages 256 order by reltuples desc;) results = cur.fetchall() for name, pages in results: print %s.value %.2f % (name[:19], pages*8.0/1024.0) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Opinions on Raid
Really appreciate all of the valuable input. The current server has the Perc4ei controller. The impression I am taking from the responses is that we may be okay with software raid, especially if raid 1 and 10 are what we intend to use. I think we can collect enough information from the archives of this list to help make decisions for the new machine(s), was just very interested in hearing feedback on software vs. hardware raid. We will likely be using the 2.6.18 kernel. Thanks for everyone's input, Joe -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 27, 2007 12:56 PM To: Joe Uhl Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Opinions on Raid On Tue, 2007-02-27 at 07:12, Joe Uhl wrote: We have been running Postgres on a 2U server with 2 disks configured in raid 1 for the os and logs and 4 disks configured in raid 10 for the data. I have since been told raid 5 would have been a better option given our usage of Dell equipment and the way they handle raid 10. Some controllers do no layer RAID effectively. Generally speaking, the cheaper the controller, the worse it's gonna perform. Also, some controllers are optimized more for RAID 5 than RAID 1 or 0. Which controller does your Dell have, btw? I have just a few general questions about raid with respect to Postgres: [1] What is the performance penalty of software raid over hardware raid? Is it truly significant? We will be working with 100s of GB to 1-2 TB of data eventually. For a mostly read system, the performance is generally pretty good. Older linux kernels ran layered RAID pretty slowly. I.e. RAID 1+0 was no faster than RAID 1. The best performance software RAID I found in older linux kernels (2.2, 2.4) was plain old RAID-1. RAID-5 was good at reading, but slow at writing. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Opinions on Raid
We have been running Postgres on a 2U server with 2 disks configured in raid 1 for the os and logs and 4 disks configured in raid 10 for the data. I have since been told raid 5 would have been a better option given our usage of Dell equipment and the way they handle raid 10. I have just a few general questions about raid with respect to Postgres: [1] What is the performance penalty of software raid over hardware raid? Is it truly significant? We will be working with 100s of GB to 1-2 TB of data eventually. [2] How do people on this list monitor their hardware raid? Thus far we have used Dell and the only way to easily monitor disk status is to use their openmanage application. Do other controllers offer easier means of monitoring individual disks in a raid configuration? It seems one advantage software raid has is the ease of monitoring. I truly appreciate any assistance or input. As an additional question, does anyone have any strong recommendations for vendors that offer both consulting/training and support? We are currently speaking with Command Prompt, EnterpriseDB, and Greenplum but I am certainly open to hearing any other recommendations. Thanks, Joe ---(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: [PERFORM] Horribly slow query/ sequential scan
Yes it does: SET EXPLAIN ON; It writes the file to sqexplain.out -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, January 09, 2007 9:13 AM To: Gregory S. Williamson Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Horribly slow query/ sequential scan Gregory S. Williamson [EMAIL PROTECTED] writes: HAving burdened others with my foolishness too often, I hesitate to ask, but could someone either point me to a reference or explain what the difference might be ... I can see it with the eyes but I am having trouble understanding what Informix might have been doing to my (bad ?) SQL to fix the query. Me too. Does informix have anything EXPLAIN-like to show what it's doing? regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance penalty for remote access of postgresql
Stephen Frost wrote: * Guoping Zhang ([EMAIL PROTECTED]) wrote: Obviously, if there is no better solution, the TCP round trip penalty will stop us doing so as we do have performance requirement. Actually, can't you stick multiple inserts into a given 'statement'? ie: insert into abc (123); insert into abc (234); I'm not 100% sure if that solves the round-trip issue, but it might.. Also, it looks like we might have multi-value insert support in 8.2 (I truely hope so anyway), so you could do something like this: insert into abc (123),(234); Yeah, see my post from last night on PATCHES. Something like insert into abc (123); insert into abc (234); ... actually seems to work pretty well as long as you don't drive the machine into swapping. If you're doing a very large number of INSERTs, break it up into bite-sized chunks and you should be fine. Joe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Big differences in plans between 8.0 and 8.1
Gabriele Turchi wrote: Il giorno sab, 15/07/2006 alle 13.04 -0700, Joe Conway ha scritto: Why not just periodically (once an hour?) run ANALYZE registrazioni; during the day. This will only update the statistics, and should be very low impact. This is my solution too... but: is enough? Or else: there is a better way to do this? If the performance in the better case is 50 times faster than the worse case, during an hour (50/100 record inserted in registrazioni) how much the performance can fall before the new ANALYZE is run? Otherwise, running ANALYZE more frequently can badly affect the overall performance? One thing I noticed is that in both plans there is a seq scan on registrazioni. Given that performance degrades so quickly as records are inserted into registrazioni, I'm wondering if you're missing an index. What indexes do you have on registrazioni? Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Big differences in plans between 8.0 and 8.1
Gabriele Turchi wrote: Running an ANALYZE really change the plan, now it is fast as before (8.0). On the production system a VACUUM FULL ANALYZE is run every morning after a clean-up, when the registrazioni table is empty. During the day this table fills up (about 500 record any day), and apparently the performances are free-falling very quickly. This behaviour has not changed between the old and the new installation. Can you suggest an easy way to collect and keep up-to-date these statistics in a very low-impact way? Why not just periodically (once an hour?) run ANALYZE registrazioni; during the day. This will only update the statistics, and should be very low impact. HTH, Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Index Being Ignored?
great! Thanks Markus and Tom! On Jun 30, 2006, at 10:29 AM, Markus Schaber wrote: Hi, Joe, Joe Lester wrote: Aggregate (cost=22695.28..22695.28 rows=1 width=0) (actual time=2205.688..2205.724 rows=1 loops=1) - Seq Scan on purchase_order_items (cost=0.00..21978.08 rows=286882 width=0) (actual time=0.535..2184.405 rows=7458 loops=1) Filter: (expected_quantity 0) The query planner estimates that your filter will hit 286882 rows, while in reality it hits only 7458 rows. That's why the query planer chooses a sequential scan. It seems that the statistics for the column expected_quantity are off. My suggestions: - make shure that the statistics are current by analyzing the table appropriately (e. G. by using the autovacuum daemon from contrib). - increase the statistics target for this column. - if you run this query very often, an conditional index might make sense: CREATE INDEX purchase_order_having_quantity_idx ON purchase_order_items (expected_quantity) WHERE expected_quantity 0; HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Dynamically loaded C function performance
Jim C. Nasby wrote: On Fri, May 05, 2006 at 03:47:53PM -0700, Adam Palmblad wrote: Hi, We've got a C function that we use here and we find that for every connection, the first run of the function is much slower than any subsequent runs. ( 50ms compared to 8ms) Besides using connection pooling, are there any options to improve performance? In my experience, connection startup takes a heck of a lot longer than 50ms, so why are you worrying about 50ms for the first run of a function? BTW, sorry, but I don't know a way to speed this up, either. I think Tom nailed the solution already in a nearby reply -- see preload_libraries on this page: http://www.postgresql.org/docs/8.1/interactive/runtime-config-resource.html Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Comparative performance
Hi Jim, Jim C. Nasby wrote: Also, just because no one else has mentioned it, remember that it's very easy to get MySQL into a mode where you have no data integrity. If that's the case it's going to be faster than PostgreSQL (though I'm not sure how much that affects the performance of SELECTs). Yes indeed. When I added the REFERENCES to the schema and reran the conversion scripts, aside from having to reorder the table creation and loading (they used to be in alphabetical order), I also found a few referential integrity errors in the MySQL data. Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Comparative performance
PFC wrote: - if you use a version before 8, type mismatch will prevent use of the indexes. I'm using 8.0.3, but the type mismatch between relationship.rel_type and entry_type.type_id was unintended. The current databases use SMALLINT for both. The PostgreSQL schema was derived from an export script stored in Subversion, apparently before the column datatypes were changed. CREATE INDEX'es ON entry_type( class_id ) relationship( topic_id1, rel_type, topic_id2 )which becomes your new PRIMARY KEY relationship( topic_id2, rel_type, topic_id1 ) Creating the second relationship index was sufficient to modify the query plan to cut down runtime to zero: Sort (cost=75.94..75.95 rows=2 width=381) (actual time=0.000..0.000 rows=0 loops=1) Sort Key: r.rel_type, t.list_name - Nested Loop (cost=16.00..75.93 rows=2 width=381) (actual time=0.000..0.000 rows=0 loops=1) Join Filter: (((outer.topic_id1 = inner.topic_id) AND (outer.topic_id2 = 1252)) OR ((outer.topic_id2 = inner.topic_id) AND (outer.topic_id1 = 1252))) - Nested Loop (cost=16.00..35.11 rows=1 width=169) (actual time=0.000..0.000 rows=0 loops=1) Join Filter: (inner.rel_type = outer.type_id) - Seq Scan on entry_type e (cost=0.00..18.75 rows=4 width=4) (actual time=0.000..0.000 rows=15 loops=1) Filter: (class_id = 2) - Materialize (cost=16.00..16.04 rows=4 width=167) (actual time=0.000..0.000 rows=0 loops=15) - Seq Scan on relationship r (cost=0.00..16.00 rows=4 width=167) (actual time=0.000..0.000 rows=0 loops=1) Filter: ((topic_id2 = 1252) OR (topic_id1 = 1252)) - Seq Scan on topic t (cost=0.00..30.94 rows=494 width=216) (never executed) Total runtime: 0.000 ms (13 rows) The overall execution time for the Economists page for PostgreSQL is within 4% of the MySQL time, so for the time being I'll leave the query in its current form. Thanks for your help. Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Comparative performance
Jim C. Nasby wrote: Make sure these indexes exist if you'll be updating or inserting into entry: CREATE INDEX topic__subject_id ON topic(subject_id); CREATE INDEX topic__actor_id ON topic(actor_id); Actually, topic's primary key is topic_id. Also, the fact that subject and actor both point to topic along with subject_type and actor_type make me suspect that your design is de-normalized. Of course there's no way to know without more info. Yes, the design is denormalized. The reason is that a book or article is usually by a single author (an actor topic) and it will be listed under one main topic (a subject topic). There's a topic_entry table where additional actors and subjects can be added. It's somewhat ironic because I used to teach and/or preach normalization and the goodness of a 3NF+ design (also about having the database do aggregation and sorting as you mentioned in your other email). FWIW, I usually use timestamptz for both created and updated fields. IIRC 'created' ended up as a DATE because MySQL 4 has a restriction about a single TIMESTAMP column per table taking the default value of current_timestamp. Joe ---(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: [PERFORM] Comparative performance
PFC wrote: Even though this query isn't that optimized, it's still only 16 milliseconds. Why does it take this long for PHP to get the results ? Can you try pg_query'ing this exact same query, FROM PHP, and timing it with getmicrotime() ? That query took about 27 msec in actual PHP execution time. It turns out the real culprit is the following query, which interestingly enough retrieves zero rows in the case of the Economists page that I've been using for testing, yet it uses up about 1370 msec in actual runtime: SELECT topic_id1, topic_id2, topic_name, categ_id, list_name, t.title, url, page_type, rel_type, inverse_id, r.description AS rel_descrip, r.created, r.updated FROM relationship r, topic t, entry_type e WHERE ((topic_id1 = topic_id AND topic_id2 = 1252) OR (topic_id2 = topic_id and topic_id1 = 1252)) AND rel_type = type_id AND e.class_id = 2 ORDER BY rel_type, list_name; The EXPLAIN ANALYZE output, after I ran VACUUM ANALYZE on the three tables, is: Sort (cost=4035.55..4035.56 rows=1 width=131) (actual time=2110.000..2110.000 rows=0 loops=1) Sort Key: r.rel_type, t.list_name - Nested Loop (cost=36.06..4035.54 rows=1 width=131) (actual time=2110.000..2110.000 rows=0 loops=1) Join Filter: (((inner.topic_id1 = outer.topic_id) AND (inner.topic_id2 = 1252)) OR ((inner.topic_id2 = outer.topic_id) AND (inner.topic_id1 = 1252))) - Seq Scan on topic t (cost=0.00..38.34 rows=1234 width=90) (actual time=0.000..15.000 rows=1234 loops=1) - Materialize (cost=36.06..37.13 rows=107 width=45) (actual time=0.000..0.509 rows=466 loops=1234) - Merge Join (cost=30.31..35.96 rows=107 width=45) (actual time=0.000..0.000 rows=466 loops=1) Merge Cond: (outer.type_id = inner.rel_type) - Index Scan using entry_type_pkey on entry_type e (cost =0.00..3.94 rows=16 width=4) (actual time=0.000..0.000 rows=15 loops=1) Filter: (class_id = 2) - Sort (cost=30.31..31.48 rows=466 width=43) (actual time=0.000..0.000 rows=466 loops=1) Sort Key: r.rel_type - Seq Scan on relationship r (cost=0.00..9.66 rows=466 width=43) (actual time=0.000..0.000 rows=466 loops=1) Total runtime: 2110.000 ms (14 rows) The tables are as follows: CREATE TABLE entry_type ( type_id SMALLINT NOT NULL PRIMARY KEY, title VARCHAR(32) NOT NULL, rel_title VARCHAR(32), class_id SMALLINT NOT NULL DEFAULT 1, inverse_id SMALLINT, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP) WITHOUT OIDS; CREATE TABLE topic ( topic_id serial PRIMARY KEY, topic_name VARCHAR(48) NOT NULL UNIQUE, categ_id SMALLINT NOT NULL, parent_entity INTEGER, parent_concept INTEGER, crossref_id INTEGER, list_name VARCHAR(80) NOT NULL, title VARCHAR(80), description VARCHAR(255), url VARCHAR(64), page_type SMALLINT NOT NULL, dark_ind BOOLEAN NOT NULL DEFAULT FALSE, ad_code INTEGER, created DATE NOT NULL DEFAULT CURRENT_DATE, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP) WITHOUT OIDS; CREATE TABLE relationship ( topic_id1 INTEGER NOT NULL REFERENCES topic, topic_id2 INTEGER NOT NULL REFERENCES topic, rel_type INTEGER NOT NULL, description VARCHAR(255), created DATE NOT NULL DEFAULT CURRENT_DATE, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (topic_id1, topic_id2, rel_type)) WITHOUT OIDS; I'm thinking that perhaps I need to set up another index with topic_id2 first and topic_id1 second. In addition, an index on entry_type.class_id may improve things. Another possibility would be to rewrite the query as a UNION. Of course, this doesn't explain how MySQL manages to execute the query in about 9 msec. The only minor differences in the schema are: entry_type.title and rel_title are char(32) in MySQL, entry_type.class_id is a tinyint, and topic.categ_id, page_type and dark_ind are also tinyints. MySQL also doesn't have the REFERENCES. A couple of interesting side notes from my testing. First is that pg_connect() took about 39 msec but mysql_connect() took only 4 msec, however, pg_pconnect() took 0.14 msec while mysql_pconnect() took 0.99 msec (all tests were repeated five times and the quoted results are averages). Second, is that PostgreSQL's performance appears to be much more consistent in certain queries. For example, the query that retrieves the list of subtopics (the names and description of economists), took 17 msec in PG, with a low of 15 (three times) and a high of 21, whereas MySQL took 60 msec on average but had a low of 22 and a high of 102 msec. Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Comparative performance
Magnus Hagander wrote: That actually depends a lot on *how* you use it. I've seen pg-on-windows deployments that come within a few percent of the linux performance. I've also seen those that are absolutely horrible compared. One sure way to kill the performance is to do a lot of small connections. Using persistent connection is even more important on Windows than it is on Unix. It could easily explain a difference like this. I just tried using pg_pconnect() and I didn't notice any significant improvement. What bothers me most is that with Postgres I tend to see jerky behavior on almost every page: the upper 1/2 or 2/3 of the page is displayed first and you can see a blank bottom (or you can see a half-filled completion bar). With MySQL each page is generally displayed in one swoop. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Comparative performance
PFC wrote: From my experience, the postgres libraries in PHP are a piece of crap, and add a lot of overhead even from small queries. For instance, a simple query like SELECT * FROM table WHERE primary_key_id=1234 can take the following time, on my laptop, with data in the filesystem cache of course : EXPLAIN ANALYZE0.1 ms python + psycopg 20.1 ms (damn fast) php + mysql0.3 ms php + postgres1-2 ms (damn slow) As a Trac user I was considering moving to Python, so it's good to know that, but the rewrite is a longer term project. So, if your pages are designed in The PHP Way (ie. a large number of small queries), I might suggest using a language with a decent postgres interface (python, among others), or rewriting your bunches of small queries as Stored Procedures or Joins, which will provide large speedups. Doing 50 queries on a page is always a bad idea, but it's tolerable in php-mysql, not in php-postgres. The pages do use a number of queries to collect all the data for display but nowhere near 50. I'd say it's probably less than a dozen. As an aside, one of my tasks (before the conversion) was to analyze the queries and see where they could be tweaked for performance, but with MySQL that was never a top priority. The schema is fairly simple having two main tables: topic and entry (sort of like account and transaction in an accounting scenario). There are two additional tables that perhaps could be merged into the entry table (and that would reduce the number of queries) but I do not want to make major changes to the schema (and the app) for the PostgreSQL conversion. Joe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Comparative performance
Gavin Sherry wrote: Please post the table definitions, queries and explain analyze results so we can tell you why the performance is poor. I did try to post that last night but apparently my reply didn't make it to the list. Here it is again: Matthew Nuzum wrote: This is the right list. Post detail and I'm sure you'll get some suggestions. Thanks, Matthew (and Chris and Gavin). The main table used in the query is defined as follows: CREATE TABLE entry ( entry_id serial PRIMARY KEY, title VARCHAR(128) NOT NULL, subtitle VARCHAR(128), subject_type SMALLINT, subject_id INTEGER REFERENCES topic, actor_type SMALLINT, actor_id INTEGER REFERENCES topic, actor VARCHAR(64), actor_role VARCHAR(64), rel_entry_id INTEGER, rel_entry VARCHAR(64), description VARCHAR(255), quote text, url VARCHAR(255), entry_date CHAR(10), created DATE NOT NULL DEFAULT CURRENT_DATE, updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP) WITHOUT OIDS; CREATE INDEX entry_actor_id ON entry (actor_id); CREATE INDEX entry_subject_id ON entry (subject_id); It has 3422 rows at this time. The query for one of the pages is the following: SELECT entry_id, subject_type AS type, subject_type, subject_id, actor_type, actor_id, actor, actor_role, rel_entry_id, rel_entry, title, subtitle, description, url, quote AS main_quote, NULL AS rel_quote, substring(entry_date from 8) AS dom, substring(entry_date from 1) AS date_ymd, substring(entry_date from 1 for 7) AS date_ym, substring(entry_date from 1 for 4) AS date_y, created, updated FROM entry WHERE subject_id = 1079 UNION SELECT entry_id, actor_type AS type, subject_type, subject_id, actor_type, actor_id, actor, actor_role, rel_entry_id, rel_entry, title, subtitle, description, url, quote AS main_quote, NULL AS rel_quote, substring(entry_date from 8) AS dom, substring(entry_date from 1) AS date_ymd, substring(entry_date from 1 for 7) AS date_ym, substring(entry_date from 1 for 4) AS date_y, created, updated FROM entry WHERE actor_id = 1079 ORDER BY type, title, subtitle; The output of EXPLAIN ANALYZE is: Sort (cost=158.98..159.14 rows=62 width=568) (actual time=16.000..16.000 rows=59 loops=1) Sort Key: type, title, subtitle - Unique (cost=153.57..157.14 rows=62 width=568) (actual time=16.000..16.000 rows=59 loops=1) - Sort (cost=153.57..153.73 rows=62 width=568) (actual time=16.000..16.000 rows=59 loops=1) Sort Key: entry_id, type, subject_type, subject_id, actor_type, actor_id, actor, actor_role, rel_entry_id, rel_entry, title, subtitle, description, url, main_quote, rel_quote, dom, date_ymd, date_ym, date_y, created, updated - Append (cost=0.00..151.73 rows=62 width=568) (actual time=0.000..16.000 rows=59 loops=1) - Subquery Scan *SELECT* 1 (cost=0.00..17.21 rows=4 width=568) (actual time=0.000..0.000 rows=3 loops=1) - Index Scan using entry_subject_id on entry (cost=0.00..17.17 rows=4 width=568) (actual time=0.000..0.000 rows=3 loops=1) Index Cond: (subject_id = 1079) - Subquery Scan *SELECT* 2 (cost=0.00..134.52 rows=58 width=568) (actual time=0.000..16.000 rows=56 loops=1) - Seq Scan on entry (cost=0.00..133.94 rows=58 width=568) (actual time=0.000..16.000 rows=56 loops=1) Filter: (actor_id = 1079) Total runtime: 16.000 ms (13 rows) What I don't quite understand is why it's doing a sequential scan on actor_id instead of using the entry_actor_id index. Note that actor_id has 928 non-null values (27%), whereas subject_id has 3089 non-null values (90%). Note that the entry_date column was originally a MySQL date but it had partial dates, i.e., some days and months are set to zero. Eventually I hope to define a PostgreSQL datatype for it and to simplify the substring retrievals. However, I don't think the extra computational time should affect the overall runtime significantly. Gavin, I'm using PostgreSQL 8.0.3, Apache 1.3.28, PHP 4.3.4, MySQL 4.0.16 and I'm comparing both databases on XP (on a Pentium 4, 1.6 GHz, 256 MB RAM). Thanks for any feedback. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Comparative performance
Andreas Pflug wrote: Hm, if you only have 4 tables, why do you need 12 queries? To reduce queries, join them in the query; no need to merge them physically. If you have only two main tables, I'd bet you only need 1-2 queries for the whole page. There are more than four tables and the queries are not functionally overlapping. As an example, allow me to refer to the page www.freedomcircle.com/topic.php/Economists. The top row of navigation buttons (Life, Liberty, etc.) is created from a query of the 'topic' table. It could've been hard-coded as a PHP array, but with less flexibility. The alphabetical links are from a SELECT DISTINCT substring from topic. It could've been generated by a PHP for loop (originally implemented that way) but again with less flexibility. The listing of economists is another SELECT from topic. The subheadings (Articles, Books) come from a SELECT of an entry_type table --which currently has 70 rows-- and is read into a PHP array since we don't know what headings will be used in a given page. The detail of the entries comes from that query that I posted earlier, but there are three additional queries that are used for specialized entry types (relationships between topics --e.g., Prof. Williams teaches at George Mason, events, and multi-author or multi-subject articles and books). And there's yet another table for the specific book information. Once the data is retrieved it's sorted internally with PHP, at the heading level, before display. Maybe there is some way to merge all the queries (some already fairly complex) that fetch the data for the entries box but I believe it would be a monstrosity with over 100 lines of SQL. Thanks, Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Comparative performance
PFC wrote: Even though this query isn't that optimized, it's still only 16 milliseconds. Why does it take this long for PHP to get the results ? Can you try pg_query'ing this exact same query, FROM PHP, and timing it with getmicrotime() ? Thanks, that's what I was looking for. It's microtime(), BTW. It'll take me some time to instrument it, but that way I can pinpoint what is really slow. You can even do an EXPLAIN ANALYZE from pg_query and display the results in your webpage, to check how long the query takes on the server. You can also try it on a Linux box. My current host only supports MySQL. I contacted hub.org to see if they could assist in this transition but I haven't heard back. This smells like a TCP communication problem. I'm puzzled by that remark. How much does TCP get into the picture in a local Windows client/server environment? Joe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Comparative performance
I'm converting a relatively small database (2 MB) from MySQL to PostgreSQL. It is used to generate web pages using PHP. Although the actual website runs under Linux, the development is done under XP. I've completed most of the data conversion and rewrite of the PHP scripts, so now I'm comparing relative performance. It appears that PostgreSQL is two to three times slower than MySQL. For example, some pages that have some 30,000 characters (when saved as HTML) take 1 to 1 1/2 seconds with MySQL but 3 to 4 seconds with PostgreSQL. I had read that the former was generally faster than the latter, particularly for simple web applications but I was hoping that Postgres' performance would not be that noticeably slower. I'm trying to determine if the difference can be attributed to anything that I've done or missed. I've run VACUUM ANALYZE on the two main tables and I'm looking at the results of EXPLAIN on the query that drives the retrieval of probably 80% of the data for the pages in question. Before I post the EXPLAIN and the table schema I'd appreciate confirmation that this list is the appropriate forum. I'm a relative newcomer to PostgreSQL (but not to relational databases), so I'm not sure if this belongs in the novice or general lists. Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [sfpug] DATA directory on network attached storage
Aditya wrote: We have not, AFAICT, had any problems with the traffic over NFS as far as reliability -- I'm sure there is a performance penalty, but the reliability and scalability gains more than offset that. My experience agrees with yours. However we did find one gotcha -- see the thread starting here for details: http://archives.postgresql.org/pgsql-hackers/2004-12/msg00479.php In a nutshell, be careful when using an nfs mounted data directory combined with an init script that creates a new data dir when it doesn't find one. FWIW, if I were to do this anew, I would probably opt for iSCSI over GigE with a NetApp. Any particular reason? Our NetApp technical rep advised nfs over iSCSI, IIRC because of performance. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [sfpug] DATA directory on network attached storage
Aditya wrote: On Mon, Apr 11, 2005 at 10:59:51AM -0700, Joe Conway wrote: Any particular reason? Our NetApp technical rep advised nfs over iSCSI, IIRC because of performance. I would mount the Netapp volume(s) as a block level device on my server using iSCSI (vs. a file-based device like NFS) so that filesystem parameters could be more finely tuned and one could really make use of jumbo frames over GigE. Actually, we're using jumbo frames over GigE with nfs too. I'm not sure I understand why NFS would perform better than iSCSI -- in any case, some large Oracle dbs at my current job are moving to iSCSI on Netapp and in that environment both Oracle and Netapp advise iSCSI (probably because Oracle uses the block-level device directly), so I suspend the difference in performance is minimal. We also have Oracle DBs via nfs mounted Netapp, again per the local guru's advice. It might be one of those things that is still being debated even within Netapp's ranks (or maybe our info is dated - worth a check). Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
Alex Turner wrote: I'm not advocating that people switch to Oracle at all, It's still much more expensive than Postgresql, and for most small and medium applications Postgresql is much easier to manage and maintain. I would just like to make sure people get their facts straight. I worked for a company that selected MS SQL Server because it was 'cheaper' than Oracle, when infact with the correct Oracle pricing, Oracle was cheaper, and had superior features. I would have prefered that they use Postgresql, which for the project in question would have been more appropriate and cost much less in hardware and software requirements, but they had to have 'Industry Standard'. Oracle ended up costing $10k with licenses at $149 ea for 25 users, and the support contract wasn't that much of a bear - I can't remember exactly how much, I think it was around $1800/yr. My facts were straight, and they come from firsthand experience. The point is, it is easy to get trapped into thinking to yourself, great, I can get a dual CPU oracle server for ~$10K, that's not too bad But then later you figure out you really need table partitioning or RAC, and suddenly you have to jump directly to multiple 6 figures. The entry level Oracle pricing is mainly a marketing gimmick -- it is intended to get you hooked. Also note that the per named user license scheme is subject to per CPU minimums that guarantee you'll never spend less than half the per CPU price. Oracle's licensing is so complex that there are businesses out there that subsist solely on helping companies figure it out to save money, and they take a cut of the savings. Oracle's own account reps had a hard time answering this question -- does a hyperthreaded Intel CPU count as 1 or 2 CPUs from a licensing standpoint? We were eventually told 1, but that the decision was subject to change in the future. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
Alex Turner wrote: I appreciate your information, but it's not valid. Most people don't need RAC or table partitioning. From a small company perspective, maybe, but not in the least invalid for larger companies. Many of the features in Oracle EE are just not available in Postgresql at all, and many aren't available in any version of SQL Server (table partitioning, bitmap indexes and others). I never claimed otherwise. I said the low end product gets you hooked. Once you're hooked, you'll start to wish for all the wiz-bang features -- after all, that's why you picked Oracle in the first place. Just because Oracle reps are a little clueless sometimes doesn't mean that the product pricing sucks. The minimum user requirement for standard one is 5 users. 5*149=$745, much less than half the price of a dual or single CPU config. And what happens once you need a quad server? I'm sorry that you had a bad experience with Oracle, but Oracle is a fine product, that is available for not alot of $$ if you are willing to use a bit of elbow grease to learn how it works and don't need enterprise features, which many other database product simply don't have, or work very poorly. I never said I had a bad experience with Oracle. I pointed out the gotchas. We have several large Oracle boxes running, several MSSQL, and several Postgres -- they all have their strengths and weaknesses. Nuff said -- this thread is way off topic now... Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] PostgreSQL vs. Oracle vs. Microsoft
Greg Sabino Mullane wrote: Don't forget your support contract cost, as well as licenses for each of your servers: development, testing, QA, etc. Is it really as cheap as 5K? I've heard that for any fairly modern system, it's much more, but that may be wrong. Sort of -- see: http://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=15105 It is available on single server systems supporting up to a maximum of 2 CPUs Also note that most industrial strength features (like table partitioning, RAC, OLAP, Enterprise Manager plugins, etc, etc) are high priced options (mostly $10K to $20K per CPU) and they can only be used with the Enterprise edition (which is $40K/CPU *not* $2.5K/CPU). http://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=10103 And you are correct, they expect to be paid for each dev, test, and QA machine too. The $5K edition is just there to get you hooked ;-) By the time you add up what you really want/need, figure you'll spend a couple of orders of magnatude higher, and then 20% per year for ongoing maintenance/upgrades/support. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Trying to create multi db query in one large querie
Spiegelberg, Greg wrote: My experience with dblink() is that each dblink() is executed serially Correct. If you really want to do multiple queries simultaneously, you would need to write a function very similar to dblink_record, but using asynchonous libpq calls to both remote hosts. See: http://www.postgresql.org/docs/current/static/libpq-async.html HTH, Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Alternatives to Dell?
Josh Berkus wrote: Thing is, some companies are required to use 1st-tier or at least 2nd-tier vendors for hardware; they won't home-build. For those people, what vendors do others on this list recommend? What have been your good/bad experiences? I've had very good experiences with IBM hardware, and found their sales and support to be responsive. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Iain wrote: Joe's example wasn't excluding partions, as he didn't use a predicated UNION ALL view to select from. His queries use an indexed column that allow the various partitions to be probed at low cost, and he was satisfied wth that. Right. My point in my previous post was that you could still do all that that if you wanted to, by building the predicated view with UNION ALL of each of the child tables. Right. It doesn't look that much different: create or replace view foo_vw as select * from foo_2004_01 where f2 = '2004-jan-01' and f2 = '2004-jan-31' union all select * from foo_2004_02 where f2 = '2004-feb-01' and f2 = '2004-feb-29' union all select * from foo_2004_03 where f2 = '2004-mar-01' and f2 = '2004-mar-31' ; -- needed just for illustration since these are toy tables set enable_seqscan to false; explain analyze select * from foo_vw where f2 = '2004-feb-15'; QUERY PLAN -- Subquery Scan foo_vw (cost=0.00..14.54 rows=3 width=16) (actual time=0.022..0.027 rows=1 loops=1) - Append (cost=0.00..14.51 rows=3 width=16) (actual time=0.019..0.022 rows=1 loops=1) - Subquery Scan *SELECT* 1 (cost=0.00..4.84 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1) - Index Scan using foo_2004_01_idx2 on foo_2004_01 (cost=0.00..4.83 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: ((f2 = '2004-01-01'::date) AND (f2 = '2004-01-31'::date) AND (f2 = '2004-02-15'::date)) - Subquery Scan *SELECT* 2 (cost=0.00..4.84 rows=1 width=16) (actual time=0.013..0.015 rows=1 loops=1) - Index Scan using foo_2004_02_idx2 on foo_2004_02 (cost=0.00..4.83 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: ((f2 = '2004-02-01'::date) AND (f2 = '2004-02-29'::date) AND (f2 = '2004-02-15'::date)) - Subquery Scan *SELECT* 3 (cost=0.00..4.84 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1) - Index Scan using foo_2004_03_idx2 on foo_2004_03 (cost=0.00..4.83 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1) Index Cond: ((f2 = '2004-03-01'::date) AND (f2 = '2004-03-31'::date) AND (f2 = '2004-02-15'::date)) Total runtime: 0.188 ms (12 rows) regression=# explain analyze select * from foo where f2 = '2004-feb-15'; QUERY PLAN -- Result (cost=1.00..10073.70 rows=20 width=16) (actual time=0.059..0.091 rows=1 loops=1) - Append (cost=1.00..10073.70 rows=20 width=16) (actual time=0.055..0.086 rows=1 loops=1) - Seq Scan on foo (cost=1.00..10022.50 rows=5 width=16) (actual time=0.001..0.001 rows=0 loops=1) Filter: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_01_idx2 on foo_2004_01 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.045..0.045 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_02_idx2 on foo_2004_02 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.009 rows=1 loops=1) Index Cond: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_03_idx2 on foo_2004_03 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.029..0.029 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) Total runtime: 0.191 ms (11 rows) The main difference being that the view needs to be recreated every time a table is added or dropped, whereas with the inherited tables method that isn't needed. Joe ---(end of broadcast)--- TIP 3: 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: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
[EMAIL PROTECTED] wrote: Joe Conway [EMAIL PROTECTED] wrote on 15.09.2004, 06:30:24: We're not completely done with our data conversion (from a commercial RDBMSi), but so far the results have been excellent. Similar to what others have said in this thread, the conversion involved restructuring the data to better suit Postgres, and the application (data analysis/mining vs. the source system which is operational). As a result we've compressed a 1TB database down to ~0.4TB, and seen at least one typical query reduced from ~9 minutes down to ~40 seconds. Sounds interesting. The performance gain comes from partition elimination of the inherited tables under the root? I take it the compression comes from use of arrays, avoiding the need for additional rows and key overhead? Sorry, in trying to be concise I was not very clear. I'm using the term compression very generally here. I'll try to give a bit more background, The original data source is a database schema designed for use by an operational application that my company sells to provide enhanced management of equipment that we also sell. The application needs to be very flexible in exactly what data it stores in order to be useful across a wide variety of equipment models and versions. In order to do that there is a very large central transaction table that stores name-value pairs in varchar columns. The name-value pairs come from parsed output of the equipment, and as such there is a fair amount of redundancy and unneeded data that ends up getting stored. At each installation in the field this table can get very large ( billion rows). Additionally the application prematerializes a variety of summaries for use by the operators using the GUI. We collect the data exported from each of the systems in the field and accumulate it in a single central database for data mining and analysis. This is the database that is actually being converted. By compression I really mean that unneeded and redundant data is being stripped out, and data known to be of a certain datatype is stored in that type instead of varchar (e.g. values known to be int are stored as int). Also the summaries are not being converted (although we do some post processing to create new materialized summaries). My points in telling this were: - the use of inherited tables to partition this huge number of rows and yet allow simple query access to it seems to work well, at least in early validation tests - had we simply taken the original database and slammed it into Postgres with no further thought, we would not have seen the big improvements, and thus the project might have been seen as a failure (even though it saves substantial $) Hope that's a bit more clear. I'm hoping to write up a more detailed case study once we've cut the Postgres system into production and the dust settles a bit. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Josh Berkus wrote: - the use of inherited tables to partition this huge number of rows and yet allow simple query access to it seems to work well, at least in early validation tests - had we simply taken the original database and slammed it into Postgres with no further thought, we would not have seen the big improvements, and thus the project might have been seen as a failure (even though it saves substantial $) Any further thoughts on developing this into true table partitioning? Just that I'd love to see it happen ;-) Maybe someday I'll be able to find the time to work on it myself, but for the moment I'm satisfied with the workarounds we've made. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Simon Riggs wrote: Joe, Your application is very interesting. I've just read your OSCON paper. I'd like to talk more about that. Very similar to Kalido. ...but back to partitioning momentarily: Does the performance gain come from partition elimination of the inherited tables under the root? I think the major part of the peformance gain comes from the fact that the source database has different needs in terms of partitioning criteria because of it's different purpose. The data is basically partitioned by customer installation instead of by date. Our converted scheme partitions by date, which is in line with the analytical queries run at the corporate office. Again, this is an argument in favor of not simply porting what you're handed. We might get similar query performance with a single large table and multiple partial indexes (e.g. one per month), but there would be one tradeoff and one disadvantage to that: 1) The indexes would need to be generated periodically -- this is a tradeoff since we currently need to create inherited tables at the same periodicity 2) It would be much more difficult to roll off a month's worth of data when needed. The general idea is that each month we create a new monthly table, then archive and drop the oldest monthly table. If all the data were in one big table we would have to delete many millions of rows from a (possibly) multibillion row table, and then vacuum that table -- no thanks ;-) Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Iain wrote: That's exactly what we're doing, but using inherited tables instead of a union view. With inheritance, there is no need to rebuild the view each time a table is added or removed. Basically, in our application, tables are partitioned by either month or week, depending on the type of data involved, and queries are normally date qualified. That sounds interesting. I have to admit that I havn't touched iheritance in pg at all yet so I find it hard to imagine how this would work. If you have a chance, would you mind elaborating on it just a little? OK, see below: = create table foo(f1 int, f2 date, f3 float8); create table foo_2004_01() inherits (foo); create table foo_2004_02() inherits (foo); create table foo_2004_03() inherits (foo); create index foo_2004_01_idx1 on foo_2004_01(f2); create index foo_2004_02_idx1 on foo_2004_02(f2); create index foo_2004_03_idx1 on foo_2004_03(f2); insert into foo_2004_02 values(1,'2004-feb-15',3.14); -- needed just for illustration since these are toy tables set enable_seqscan to false; explain analyze select * from foo where f2 = '2004-feb-15'; QUERY PLAN Result (cost=1.00..10061.32 rows=16 width=16) (actual time=0.224..0.310 rows=1 loops=1) - Append (cost=1.00..10061.32 rows=16 width=16) (actual time=0.214..0.294 rows=1 loops=1) - Seq Scan on foo (cost=1.00..10022.50 rows=5 width=16) (actual time=0.004..0.004 rows=0 loops=1) Filter: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_01_idx1 on foo_2004_01 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.101..0.101 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_02_idx1 on foo_2004_02 foo (cost=0.00..4.68 rows=1 width=16) (actual time=0.095..0.101 rows=1 loops=1) Index Cond: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_03_idx1 on foo_2004_03 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.066..0.066 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) Total runtime: 0.582 ms (11 rows) create table foo_2004_04() inherits (foo); create index foo_2004_04_idx1 on foo_2004_04(f2); explain analyze select * from foo where f2 = '2004-feb-15'; QUERY PLAN Result (cost=1.00..10078.38 rows=21 width=16) (actual time=0.052..0.176 rows=1 loops=1) - Append (cost=1.00..10078.38 rows=21 width=16) (actual time=0.041..0.159 rows=1 loops=1) - Seq Scan on foo (cost=1.00..10022.50 rows=5 width=16) (actual time=0.004..0.004 rows=0 loops=1) Filter: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_01_idx1 on foo_2004_01 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.012..0.012 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_02_idx1 on foo_2004_02 foo (cost=0.00..4.68 rows=1 width=16) (actual time=0.016..0.022 rows=1 loops=1) Index Cond: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_03_idx1 on foo_2004_03 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) - Index Scan using foo_2004_04_idx1 on foo_2004_04 foo (cost=0.00..17.07 rows=5 width=16) (actual time=0.095..0.095 rows=0 loops=1) Index Cond: (f2 = '2004-02-15'::date) Total runtime: 0.443 ms (13 rows) For loading data, we COPY into foo, and have a trigger that redirects the rows to the appropriate partition. Notice that the partitions which do not contain any data of interest are still probed for data, but since they have none it is very quick. In a real life example I got the following results just this afternoon: - aggregate row count = 471,849,665 - total number inherited tables = 216 (many are future dated and therefore contain no data) - select one month's worth of data for one piece of equipment by serial number (49,257 rows) = 526.015 ms Not too bad -- quick enough for my needs. BTW, this is using NFS mounted storage (NetApp NAS). Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Christopher Browne wrote: In the last exciting episode, [EMAIL PROTECTED] (Joe Conway) wrote: That's exactly what we're doing, but using inherited tables instead of a union view. With inheritance, there is no need to rebuild the view each time a table is added or removed. Basically, in our application, tables are partitioned by either month or week, depending on the type of data involved, and queries are normally date qualified. Where does the constraint come in that'll allow most of the data to be excluded? Not sure I follow this. Or is this just that the entries are all part of bigtable so that the self join is only 2-way? We don't have a need for self-joins in our application. We do use a crosstab function to materialize some transposed views of the data, however. That allows us to avoid self-joins in the cases where we might otherwise need them. Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --
Chris Browne wrote: Might we set up the view as: create view combination_of_logs as select * from table_1 where txn_date between 'this' and 'that' union all select * from table_2 where txn_date between 'this2' and 'that2' union all select * from table_3 where txn_date between 'this3' and 'that3' union all select * from table_4 where txn_date between 'this4' and 'that4' union all ... ad infinitum union all select * from table_n where txn_date 'start_of_partition_n'; and expect that to help, as long as the query that hooks up to this has date constraints? We'd have to regenerate the view with new fixed constants each time we set up the tables, but that sounds like it could work... That's exactly what we're doing, but using inherited tables instead of a union view. With inheritance, there is no need to rebuild the view each time a table is added or removed. Basically, in our application, tables are partitioned by either month or week, depending on the type of data involved, and queries are normally date qualified. We're not completely done with our data conversion (from a commercial RDBMSi), but so far the results have been excellent. Similar to what others have said in this thread, the conversion involved restructuring the data to better suit Postgres, and the application (data analysis/mining vs. the source system which is operational). As a result we've compressed a 1TB database down to ~0.4TB, and seen at least one typical query reduced from ~9 minutes down to ~40 seconds. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Beowulf Cluster Postgresql?
Hi all, I was wondering if part or all of Postgres would be able to take advantage of a beowulf cluster to increase performance? If not then why not, and if so then how would/could it benefit from being on a cluster? Thanks for the enlightenment in advance. -Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] NAS, SAN or any alternate solution ?
[EMAIL PROTECTED] wrote: Would NAS or SAN be good solutions ? (I've read that NAS uses NFS which could slow down the transfer rate ??) Has anyone ever tried one of these with postgresql ? Not (yet) with Postgres, but my company has run ~100GB Oracle database on NAS (NetApp) for the past couple of years. We've found it to outperform local attached storage, and it has been extremely reliable and flexible. Our DBAs wouldn't give it up without a fight. Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] postgresql and openmosix migration
Bill wrote: Ok, so maybe someone on this group will have a better idea. We have a database of financial information, and this has literally millions of entries. I have installed indicies, but for the rather computationally demanding processes we like to use, like a select query to find the commodity with the highest monthly or annual returns, the computer generally runs unacceptably slow. So, other than clustring, how could I achieve a speed increase in these complex queries? Is this better in mysql or postgresql? If the bottleneck is really computational, not I/O, you might try PL/R in conjunction with the rpvm R package. rpvm allows R to make use of pvm to split its load among a cluster. See: R: http://www.r-project.org/ PL/R: http://www.joeconway.com/plr/ rpvm: http://cran.r-project.org/src/contrib/Descriptions/rpvm.html http://cran.r-project.org/doc/packages/rpvm.pdf I haven't had a chance to play with this myself yet, but I hope to relatively soon. HTH, Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Looking for ideas on how to speed up warehouse loading
Sean Shanny wrote: explain analyze SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER JOIN d_referral t1 ON t2.url = t1.referral_raw_url ORDER BY t1.id; What I would like to know is if there are better ways to do the join? I need to get all the rows back from the referral_temp table as they are used for assigning FK's for the fact table later in processing. When I iterate over the values that I get back those with t1.id = null I assign a new FK and push both into the d_referral table as new entries as well as a text file for later use. The matching records are written to a text file for later use. Would something like this work any better (without disabling index scans): SELECT t1.id, t2.url FROM referral_temp t2, d_referral t1 WHERE t1.referral_raw_url = t2.url; process rows with a match SELECT t1.id, t2.url FROM referral_temp t2 WHERE NOT EXISTS (select 1 FROM d_referral t1 WHERE t1.referral_raw_url = t2.url); process rows without a match ? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Wierd context-switching issue on Xeon
Joe Conway wrote: In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. Here's results for 7.4 on a dual Athlon server running fedora core: CPU states: cpuusernice systemirq softirq iowaitidle total 86.0%0.0% 52.4% 0.0% 0.0%0.0% 61.2% cpu00 37.6%0.0% 29.7% 0.0% 0.0%0.0% 32.6% cpu01 48.5%0.0% 22.7% 0.0% 0.0%0.0% 28.7% procs memory swap io system cpu r b swpd free buff cache si sobibo incs 1 0 120448 25764 48300 109457600 0 124 170 187 1 0 120448 25780 48300 109457600 0 0 15289 2 0 120448 25744 48300 109458000 060 141 78290 2 0 120448 25752 48300 109458000 0 0 131 140326 2 0 120448 25756 48300 109457600 040 122 140100 2 0 120448 25764 48300 109458400 060 133 136595 2 0 120448 24284 48300 109458400 0 200 138 135151 The jump in cs corresponds to starting the query in the second session. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Wierd context-switching issue on Xeon
scott.marlowe wrote: On Mon, 19 Apr 2004, Bruce Momjian wrote: I have BSD on a SuperMicro dual Xeon, so if folks want another hardware/OS combination to test, I can give out logins to my machine. I can probably do some nighttime testing on a dual 2800MHz non-MP Xeon machine as well. It's a Dell 2600 series machine and very fast. It has the moderately fast 533MHz FSB so may not have as many problems as the MP type CPUs seem to be having. I've got a quad 2.8Ghz MP Xeon (IBM x445) that I could test on. Does anyone have a test set that can reliably reproduce the problem? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Wierd context-switching issue on Xeon
Tom Lane wrote: Here is a test case. To set up, run the test_setup.sql script once; then launch two copies of the test_run.sql script. (For those of you with more than two CPUs, see whether you need one per CPU to make trouble, or whether two test_runs are enough.) Check that you get a nestloops-with-index-scans plan shown by the EXPLAIN in test_run. Check. In isolation, test_run.sql should do essentially no syscalls at all once it's past the initial ramp-up. On a machine that's functioning per expectations, multiple copies of test_run show a relatively low rate of semop() calls --- a few per second, at most --- and maybe a delaying select() here and there. What I actually see on Josh's client's machine is a context swap storm: vmstat 1 shows CS rates around 170K/sec. strace'ing the backends shows a corresponding rate of semop() syscalls, with a few delaying select()s sprinkled in. top(1) shows system CPU percent of 25-30 and idle CPU percent of 16-20. Your test case works perfectly. I ran 4 concurrent psql sessions, on a quad Xeon (IBM x445, 2.8GHz, 4GB RAM), hyperthreaded. Heres what 'top' looks like: 177 processes: 173 sleeping, 3 running, 1 zombie, 0 stopped CPU states: cpuusernice systemirq softirq iowaitidle total 35.9%0.0%7.2% 0.0% 0.0%0.0% 56.8% cpu00 19.6%0.0%4.9% 0.0% 0.0%0.0% 75.4% cpu01 44.1%0.0%7.8% 0.0% 0.0%0.0% 48.0% cpu020.0%0.0%0.0% 0.0% 0.0%0.0% 100.0% cpu03 32.3%0.0% 13.7% 0.0% 0.0%0.0% 53.9% cpu04 21.5%0.0% 10.7% 0.0% 0.0%0.0% 67.6% cpu05 42.1%0.0%9.8% 0.0% 0.0%0.0% 48.0% cpu06 100.0%0.0%0.0% 0.0% 0.0%0.0%0.0% cpu07 27.4%0.0% 10.7% 0.0% 0.0%0.0% 61.7% Mem: 4123700k av, 3933896k used, 189804k free, 0k shrd, 221948k buff 2492124k actv, 760612k in_d, 41416k in_c Swap: 2040244k av, 5632k used, 2034612k free 3113272k cached Note that cpu06 is not a postgres process. The output of vmstat looks like this: # vmstat 1 procs memory swap io system cpu r b swpd free buff cache si so bi bo in cs us sy id wa 4 0 5632 184264 221948 3113308 0 000 00 0 0 0 0 3 0 5632 184264 221948 3113308 0 000 112 211894 36 9 55 0 5 0 5632 184264 221948 3113308 0 000 125 222071 39 8 53 0 4 0 5632 184264 221948 3113308 0 000 110 215097 39 10 52 0 1 0 5632 184588 221948 3113308 0 00 96 139 187561 35 10 55 0 3 0 5632 184588 221948 3113308 0 000 114 241731 38 10 52 0 3 0 5632 184920 221948 3113308 0 000 132 257168 40 9 51 0 1 0 5632 184912 221948 3113308 0 000 114 251802 38 9 54 0 Note the test case assumes you've got shared_buffers set to at least 1000; with smaller values, you may get some I/O syscalls, which will probably skew the results. shared_buffers 16384 (1 row) I found that killing three of the four concurrent queries dropped context switches to about 70,000 to 100,000. Two or more sessions brings it up to 200K+. Joe ---(end of broadcast)--- TIP 3: 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: [PERFORM] Toooo many context switches (maybe SLES8?)
Dirk Lutzebäck wrote: Joe, do you know where I should look in the 7.4.2 code to find this out? I think I was wrong. I just looked in CVS and found the commit I was thinking about: http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/storage/lmgr/s_lock.c.diff?r1=1.22r2=1.23 http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/include/storage/s_lock.h.diff?r1=1.123r2=1.124 = Revision 1.23 / (download) - [select for diffs] , Sat Dec 27 20:58:58 2003 UTC (3 months, 2 weeks ago) by tgl Changes since 1.22: +5 -1 lines Diff to previous 1.22 Improve spinlock code for recent x86 processors: insert a PAUSE instruction in the s_lock() wait loop, and use test before test-and-set in TAS() macro to avoid unnecessary bus traffic. Patch from Manfred Spraul, reworked a bit by Tom. = I thought this had been committed to the 7.4 stable branch as well, but it appears not. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SETOF performance
Jeff wrote: I think it was on this list - someone posted a message about SETOF being slower. Tom replied saying it was because it needed to create an on-disk tuplestore. I was just looking for some clarification - a SETOF function will always write the reslting tuples to disk (Not buffering in say a sort_mem sized buffer)? I think at least part of what you're seeing is normal function call overhead. As far as tuplestores writing to disk, here's what the source says: In src/backend/utils/sort/tuplestore.c 8--- * maxKBytes: how much data to store in memory (any data beyond this * amount is paged to disk). When in doubt, use work_mem. */ Tuplestorestate * tuplestore_begin_heap(bool randomAccess, bool interXact, int maxKBytes) 8--- In src/backend/executor/execQual.c:ExecMakeTableFunctionResult(): 8--- tupstore = tuplestore_begin_heap(true, false, work_mem); 8--- So up to work_mem (sort_mem in 7.4 and earlier) should be stored in memory. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
