Re: Help: massive parallel update to the same table - Mailing list pgsql-performance
| From | Red Maple |
|---|---|
| Subject | Re: Help: massive parallel update to the same table |
| Date | |
| Msg-id | AANLkTink=w27xpYNJe+e8UcvTKcR62mD_0N-GhDWmD96@mail.gmail.com Whole thread Raw |
| In response to | Re: Help: massive parallel update to the same table ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
| Responses |
Re: Help: massive parallel update to the same table
|
| List | pgsql-performance |
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);
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;
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;
-- >>>>>>>> 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;
return this_id;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
On Fri, Mar 18, 2011 at 12:06 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
[rearranged - please don't top-post]
[also, bringing this back to the list - please keep the list copied]>> 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 statisticsPlease show us the part you commented out to get the faster run
time, and the source code for the function you mentioned.> [over 500 lines of configuration, mostly comments, wrapped]
> 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
>
>
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
pgsql-performance by date: