Re: Help: massive parallel update to the same table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.Grittner@xxxxxxxxxxxx> wrote:
[rearranged - please don't top-post]

[also, bringing this back to the list - please keep the list copied]

Red Maple <redmapleleaf@xxxxxxxxx> wrote:
> Kevin Grittner <Kevin.Grittner@xxxxxxxxxxxx> 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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux