[PERFORM] View vs Stored Proc Performance
Is it faster to use a Stored Proc that returns a Type or has Out Parameters then a View? Views are easier to maintain I feel. I remember testing this around 8.0 days and the view seemed slower with a lot of data.
[PERFORM] odd iostat graph
Hey folks, Earlier in the week I wrote a Munin plugin that takes the "await" and "average queue length" fields from "iostat -x" and graphs them. This seems rather odd to me : http://picasaweb.google.ca/alan.mckay/Work#5380253477470243954 That is Qlen. And await looks similar http://picasaweb.google.ca/alan.mckay/Work#5380254090296723426 This is on an IBM 3650 with the 2 main "internal" drives set up in a mirrored config, and sdb are the 6 other drives set up in a RAID5 with a global hot spare. (4 drives in array + 1 to make it RAID5 + global hot spare) We aren't seeing any performance problems on this per-se. But that just seems like a really odd graph to me. Can anyone explain it? In particular, how regular it is? cheers, -Alan -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] View vs Stored Proc Performance
On Fri, Sep 11, 2009 at 11:46 AM, Jason Tesser wrote: > Is it faster to use a Stored Proc that returns a Type or has Out Parameters > then a View? Views are easier to maintain I feel. I remember testing this > around 8.0 days and the view seemed slower with a lot of data. for the most part, a view can be faster and would rarely be slower. Views are like C macros for you query...they are expanded first and then planned. Functions (except for very simple ones) are black boxes to the planner and can materially hurt query performance in common cases. The only case where a function would win is when dealing with conner case planner issues (by forcing a nestloop for example). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] odd iostat graph
On 9/11/09 9:58 AM, "Alan McKay" wrote: > Hey folks, > > Earlier in the week I wrote a Munin plugin that takes the "await" and > "average queue length" fields from "iostat -x" and graphs them. > > This seems rather odd to me : > > http://picasaweb.google.ca/alan.mckay/Work#5380253477470243954 > > That is Qlen. And await looks similar > > http://picasaweb.google.ca/alan.mckay/Work#5380254090296723426 > > This is on an IBM 3650 with the 2 main "internal" drives set up in a > mirrored config, and sdb are the 6 other drives set up in a RAID5 with > a global hot spare. (4 drives in array + 1 to make it RAID5 + global > hot spare) > > We aren't seeing any performance problems on this per-se. But that > just seems like a really odd graph to me. Can anyone explain it? In > particular, how regular it is? My guess is this is checkpoint related. Find out when your checkpoints are happening. The drops are most likely due to the sync() on all outstanding writes at the end of each checkpoint. The rise is probably small writes not yet on disk in the OS bufffer cache. If this is due to checkpoints, I would expect a burst of write volume to disk at the same time of the drop. You can change your logging settings to output the time of each checkpoint and some stats about them. > > cheers, > -Alan > > -- > ³Don't eat anything you've ever seen advertised on TV² > - Michael Pollan, author of "In Defense of Food" > > -- > 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] odd iostat graph
> My guess is this is checkpoint related. I'll assume "checkpoint" is a PG term that I'm not yet familiar with - will query my DBA :-) If this OS buffer cache, wouldn't that be cached an awfully long time? i.e. we're in big trouble if we get a bad crash? -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] odd iostat graph
Alan McKay wrote: >> My guess is this is checkpoint related. > > I'll assume "checkpoint" is a PG term that I'm not yet familiar with - > will query my DBA :-) A checkpoint flushes all dirty PostgreSQL buffers to the OS and then tells the OS to write them to disk. The exact details of how that's done and the timings involved vary with PostgreSQL version and configuration. > If this OS buffer cache, wouldn't that be cached an awfully long > time? i.e. we're in big trouble if we get a bad crash? Before the commit of a database transaction is completed the changes which are involved in that are written to a write ahead log (WAL). A checkpoint is also recorded in the WAL. On recovery from a crash, PostgreSQL replays all activity from committed transactions after the last checkpoint; so nothing from a committed transaction is lost. -Kevin -- 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] View vs Stored Proc Performance
OK so in my case I have a Person, Email, Phone and Address table. I want to return the Person and an Array of the others. so my return type would be something like Person, Email[], Phone[], Address[] When passed a personId. Are you saying this is better in a view. Create a view that can return that as oppessed to 1. defining a type for a function to return or 2. a function that returns 4 out parameters (Person, Address[] ,) Thanks On Fri, Sep 11, 2009 at 1:37 PM, Merlin Moncure wrote: > On Fri, Sep 11, 2009 at 11:46 AM, Jason Tesser > wrote: > > Is it faster to use a Stored Proc that returns a Type or has Out > Parameters > > then a View? Views are easier to maintain I feel. I remember testing > this > > around 8.0 days and the view seemed slower with a lot of data. > > for the most part, a view can be faster and would rarely be slower. > Views are like C macros for you query...they are expanded first and > then planned. Functions (except for very simple ones) are black boxes > to the planner and can materially hurt query performance in common > cases. The only case where a function would win is when dealing with > conner case planner issues (by forcing a nestloop for example). > > merlin >
Re: [PERFORM] odd iostat graph
On Fri, 11 Sep 2009, Alan McKay wrote: We aren't seeing any performance problems on this per-se. But that just seems like a really odd graph to me. Can anyone explain it? In particular, how regular it is? What's the scale on the bottom there? The label says "by week" but the way your message is written makes me think it's actually a much smaller time frame. If those valleys are around around five minutes apart, those are the checkpoints finishing; the shape of the graph is right for it to be those. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] odd iostat graph
> What's the scale on the bottom there? The label says "by week" but the way > your message is written makes me think it's actually a much smaller time > frame. If those valleys are around around five minutes apart, those are the > checkpoints finishing; the shape of the graph is right for it to be those. No, that's about 6 days of stats. The numbers 04, 05, 06 are Sept 04, 05, 06 ... This is the part I found oddest - that this takes place over such a huge timeframe! Munin takes a snapshot every 5 minutes, and this graph shows it averaged over that timeframe. -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- 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] odd iostat graph
On Fri, 11 Sep 2009, Alan McKay wrote: Munin takes a snapshot every 5 minutes, and this graph shows it averaged over that timeframe. The default postgresql.conf puts a checkpoint every 5 minutes as well. It's not going to be as exact as Munin's time though, they'll be just a little longer than that. I wonder if you're seeing the checkpoint pattern anyway. If the checkpoint period is fairly stable (which it could be in your case), is slighly longer than the monitoring one, and each checkpoint has the same basic shape (also usually true), each monitoring sample is going to trace out the usual checkpoint pattern by sampling a slightly later point from successive ones. The shape of the curve you're seeing seems way too close to the standard checkpoint one to be just a coincidence. In any case, 5 minutes is unfortunately for you a really bad sampling period for a PG database because of the similarity to the checkpoint timing. You have to take measurements at least once a minute to see the checkpoints happening usefully at all. I think you're stuck generating an activity graph some other way with finer resolution to get to the bottom of this. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] View vs Stored Proc Performance
On Fri, Sep 11, 2009 at 2:56 PM, Jason Tesser wrote: > OK so in my case I have a Person, Email, Phone and Address table. I want to > return the Person and an Array of the others. so my return type would be > something like Person, Email[], Phone[], Address[] > > When passed a personId. > > Are you saying this is better in a view. Create a view that can return that > as oppessed to 1. defining a type for a function to return or 2. a function > that returns 4 out parameters (Person, Address[] ,) if you are using 8.3+ and are wiling to make a composite type: create table person_t(email text, phone text, address text); select person_id, array_agg((email, phone, address)::person_t) from person group by 1; or, detail fields are in another table: select person_id, (select array(select (email, phone, address)::person_t) from detail where person_id = p.person_id) from person_t; merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] View vs Stored Proc Performance
Right what I was wondering is is this better done in a view? or a stored proc? I am guessing based on your initial response the view is better performance. These are the types of queries I will be doing though. On Fri, Sep 11, 2009 at 5:01 PM, Merlin Moncure wrote: > On Fri, Sep 11, 2009 at 2:56 PM, Jason Tesser > wrote: > > OK so in my case I have a Person, Email, Phone and Address table. I want > to > > return the Person and an Array of the others. so my return type would be > > something like Person, Email[], Phone[], Address[] > > > > When passed a personId. > > > > Are you saying this is better in a view. Create a view that can return > that > > as oppessed to 1. defining a type for a function to return or 2. a > function > > that returns 4 out parameters (Person, Address[] ,) > > if you are using 8.3+ and are wiling to make a composite type: > > create table person_t(email text, phone text, address text); > > select person_id, array_agg((email, phone, address)::person_t) from > person group by 1; > > or, detail fields are in another table: > > select person_id, (select array(select (email, phone, > address)::person_t) from detail where person_id = p.person_id) from > person_t; > > merlin >
Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity
On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
wrote:
> On Monday 07 September 2009 03:25:23 Tom Lane wrote:
>> Vincent de Phily writes:
>> > I've been running this simple delete since yesterday afternoon :
>> >> db=# explain delete from message where datetime < '2009-03-03';
>> >> Seq Scan on message (cost=0.00..34131.95 rows=133158 width=6)
>> >> Filter: (datetime < '2009-03-03 00:00:00'::timestamp without time zone)
>> >
>> > There is no index on that column, so a seqscan is fine. But it really
>> > shouldn't take > 15 hours to delete :
>>
>> 99% of the time, the reason a delete takes way longer than it seems like
>> it should is trigger firing time. In particular, foreign key triggers
>> where you don't have an index on the referencing column. Are there
>> any foreign keys linking to this table?
>
> Yes, but they look fine to me (?). Only one FK references the table; it's an
> internal reference :
>
> Table "public.message"
> Column | Type | Modifiers
> ---+-+--
> id | integer | not null default
> nextval('message_id_seq'::regclass)
> unitid | integer | not null
> userid | integer |
> refid | integer |
> (...)
> Indexes:
> "message_pkey" PRIMARY KEY, btree (id)
> "message_unitid_fromto_status_idx" btree (unitid, fromto, status)
> "message_userid_idx" btree (userid)
> Foreign-key constraints:
> "message_refid_fkey" FOREIGN KEY (refid) REFERENCES message(id) ON UPDATE
> CASCADE ON DELETE CASCADE
> "message_unitid_fkey" FOREIGN KEY (unitid) REFERENCES units(id) ON UPDATE
> CASCADE ON DELETE CASCADE
> "message_userid_fkey" FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE
> CASCADE ON DELETE CASCADE
>
> Table "public.units"
> Column | Type | Modifiers
> -+-+
> id | integer | not null default
> nextval('units_id_seq'::regclass)
> (...)
> Indexes:
> "units_pkey" PRIMARY KEY, btree (id)
> "units_modid_ukey" UNIQUE, btree (modid)
> "units_profileid_idx" btree (profileid)
> Foreign-key constraints:
> "units_profileid_fkey" FOREIGN KEY (profileid) REFERENCES profiles(id) ON
> UPDATE CASCADE ON DELETE RESTRICT
>
> Table "public.users"
> Column | Type | Modifiers
> --+---+
> id | integer | not null default
> nextval('users_id_seq'::regclass)
> (...)
> Indexes:
> "users_pkey" PRIMARY KEY, btree (id)
> "users_login_ukey" UNIQUE, btree (login)
>
>
> Table users has a handdull of rows, table units has around 4. 43% of
> message.refid is NULL.
>
> The delete finished during the weekend (DELETE 184368). Nothing in the logs
> except the duration time (103113291.307 ms). I took a db dump before the
> delete finished, in order to be able to reproduce the issue (a 30min test
> shows me it is still slow).
I would try EXPLAIN ANALYZE DELETE ... with a query that is modified
so as to delete only a handful of rows. That will report the amount
of time spent in triggers vs. the main query, which will help you
assess whether your conclusion that the foreign keys are OK is
correct.
...Robert
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] View vs Stored Proc Performance
On Fri, Sep 11, 2009 at 5:27 PM, Jason Tesser wrote: > Right what I was wondering is is this better done in a view? or a stored > proc? I am guessing based on your initial response the view is better > performance. These are the types of queries I will be doing though. > in performance terms the view should be faster if you are doing things like joining the result to another table...the planner can see 'through' the view, etc. in a function, the result is fetched first and materialized without looking at the rest of the query. the actual mechanism you use to build the arrays is likely going to be more important than anything else. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] slow query : very simple delete, 100% cpu, nearly no disk activity
On Mon, Sep 7, 2009 at 5:05 AM, Vincent de Phily
wrote:
> On Monday 07 September 2009 03:25:23 Tom Lane wrote:
>> Vincent de Phily writes:
>> > I've been running this simple delete since yesterday afternoon :
>> >> db=# explain delete from message where datetime < '2009-03-03';
>> >> Seq Scan on message (cost=0.00..34131.95 rows=133158 width=6)
>> >> Filter: (datetime < '2009-03-03 00:00:00'::timestamp without time zone)
>> >
>> > There is no index on that column, so a seqscan is fine. But it really
>> > shouldn't take > 15 hours to delete :
>>
>> 99% of the time, the reason a delete takes way longer than it seems like
>> it should is trigger firing time. In particular, foreign key triggers
>> where you don't have an index on the referencing column. Are there
>> any foreign keys linking to this table?
>
> Yes, but they look fine to me (?). Only one FK references the table; it's an
> internal reference :
>
> Table "public.message"
> Column | Type | Modifiers
> ---+-+--
> id | integer | not null default
> nextval('message_id_seq'::regclass)
> unitid | integer | not null
> userid | integer |
> refid | integer |
> Indexes:
> "message_pkey" PRIMARY KEY, btree (id)
> "message_unitid_fromto_status_idx" btree (unitid, fromto, status)
> "message_userid_idx" btree (userid)
> Foreign-key constraints:
> "message_refid_fkey" FOREIGN KEY (refid) REFERENCES message(id) ON UPDATE
> CASCADE ON DELETE CASCADE
> "message_unitid_fkey" FOREIGN KEY (unitid) REFERENCES units(id) ON UPDATE
> CASCADE ON DELETE CASCADE
> "message_userid_fkey" FOREIGN KEY (userid) REFERENCES users(id) ON UPDATE
> CASCADE ON DELETE CASCADE
where is the index on refid?
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
