Re: Help: massive parallel update to the same table

Hi,

Here is my function. If I comment out the update then it would run all the
cores, if not then only one core will run....

CREATE OR REPLACE FUNCTION my_update_device(this_mac text, number_of_devices
integer, this_sysuptime integer)
RETURNS integer AS
$BODY$
DECLARE
fake_mac macaddr;
this_id integer;
new_avgld integer;
BEGIN
new_avgld = (this_sysuptime / 120) % 100;
for i in 1..Number_of_devices loop
fake_mac = substring(this_mac from 1 for 11) || ':' ||
upper(to_hex((i-1)/256)) || ':' || upper(to_hex((i-1)%256));
select into this_id id from ap where lan_mac =
upper(fake_mac::text);
if not found then
return -1;
end if;
select into this_sysuptime sysuptime from ap_sysuptime where
ap_id = this_id for update;
--
==============================================================================
-- >>>>>>>> if I comment out the next update then all cores will be running,
else only one core will be running
--
==============================================================================
update ap_sysuptime set sysuptime = this_sysuptime, last_contacted
= now() where ap_id = this_id;
select into new_avgld avg_ld_1min from colubris_device
where node_id = this_id for update;
new_avgld = (this_avgld / 120 ) % 100;
end loop;
return this_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

On Fri, Mar 18, 2011 at 12:06 PM, Kevin Grittner <
Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> [rearranged - please don't top-post]
>
> [also, bringing this back to the list - please keep the list copied]
>
> Red Maple <redmapleleaf(at)gmail(dot)com> wrote:
> > Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
> >> It should be parallel by default. Are you taking out any
> >> explicit locks?
>
> > my clients use psql to remotely run an update function on the
> > postgres server. Each client run its own psql to connect to the
> > server. What I have noticed is that if I commented out the update
> > in the function so that only query is being done then all the core
> > would kick in and run at 100%. However if I allow the update on
> > the function then only one core would run.
>
> > Currently it take 40min to update all the client statistics
>
> Please show us the part you commented out to get the faster run
> time, and the source code for the function you mentioned.
>
> > Do you know if I have configured something incorrectly?
> >
> > I am running postgres 9.0.2 on fedora core 14. Here is my
> > postgres.conf file
> >
> >
> > [over 500 lines of configuration, mostly comments, wrapped]
>
> If you're going to post that, please strip the comments or post the
> results of this query:
>
> http://wiki.postgresql.org/wiki/Server_Configuration
>
> I don't think anything in your configuration will affect this
> particular problem, but it seems likely that you could do some
> overall tuning. If you want to do that, you should probably start a
> new thread after this issue is sorted out.
>
> -Kevin
>
>