Thread: Search then Delete Performance
Hi Everyone, I am fairly new to practical databases, but I am trying out the c interface to postgres and am wondering how to improve performance. I am a researcher, and I am trying to perform a large parameter sweep. Since this will involve a couple of thousand simulations, I have a process that manages which simulations have been done, and which still need to be done, so I can run it easily on a cluster. So, I have a fairly simple schema of 4 tables. -- na, ca,ks,kf,lk,iinj are the parameters for my simulation. CREATE TABLE alljobs ( id SERIAL, ca int, na int, lk int, ks int, kf int, iinj int, PRIMARY KEY(id) ); CREATE TABLE assignedjobs ( jobid int, nodeid varchar(100), assignedtime timestamp, PRIMARY KEY(jobid) ); CREATE TABLE completedjobs ( jobid int, PRIMARY KEY(jobid) ); CREATE TABLE unassignedjobs( jobid int, PRIMARY KEY(jobid) ); alljobs is initially populated, and contains all the simulations that will ever be run unassignedjobs contains the ids in alljobs that havent been run yet assignedjobs contains the ids in alljobs that have been dispatched to some cpu on the cluster and are currently simulating completedjobs contains all the completed jobs. So fairly simply, I have a daemon running on a machine, which accesses this DB. Clients connect and request the details for say 1000 simulations, at which point the daemon takes 1000 entries from the unassigned table and moves them to the assigned table. The once the client is finished with those jobs, it signals this to the daemon, which then move those jobs from 'assigned' to 'complete'. So this is fairly simple to implement, but my problem is that it is very slow. In particular, I have a 'select' waiting for network connections, which then calls this function: typedef vector<long> VectorLong; VectorLong assignJobs(PGconn* pDB, int number, string nodename) { char buffer[1000]; sprintf(buffer,"SELECT jobid from unassignedjobs LIMIT %d",number); PGresult* pRes = PQexec(pDB, buffer); printf("assigning jobs"); //PGresult* pRes = PQexec(pDB, "SELECT * from alljobs LIMIT 100"); //PGresult* pRes = PQexec(pDB, "SELECT * from alljobs"); int nRes = PQntuples(pRes); printf("Results found: %d",nRes); VectorLong jobs; for(int i=0;i<nRes;i++) { long id = atol( PQgetvalue(pRes,i,0) ); cout << id << " "; jobs.push_back(id); sprintf(buffer, "DELETE FROM unassignedjobs WHERE jobid = %ld", id); PQexec(pDB, buffer); sprintf(buffer, "INSERT INTO assignedjobs (jobid,nodeid) VALUES (%ld, %s)", id, nodename.c_str() ); PQexec(pDB, buffer); } return jobs; } but it is painfully slow. I was wondering if there is a way to improve this? I feel there should be since I already have a 'pointer' to the rows I want to delete. Any help would be greatly appreciated. Many thanks Mike Hull
Hi, It's probably slow because you run many queries where a few would work: DELETE FROM unassignedjobs WHERE jobid IN (6, 8 ,2, 99, 66) But I wouldn't know how to build a query like that in C. A script in python or even bash that dit it would be faster than your C implementation. What you can do in C is this: sprintf( buffer, "INSERT INTO assignedjobs (jobid,nodeid)\n" "SELECT jobid, '%s' from unassignedjobs LIMIT %d\n", nodename.c_str(), number ); Some smart SQL with some 'RETURNING' clauses could run the whole assignJobs function in a single query and it would be a lot faster. Met vriendelijke groet, Arjen Nienhuis On Wed, Sep 15, 2010 at 2:55 AM, Michael Hull <mikehulluk@googlemail.com> wrote: > Hi Everyone, > I am fairly new to practical databases, but I am trying out the c > interface to postgres and am wondering how to improve performance. I > am a researcher, and I am trying to perform a large parameter sweep. > Since this will involve a couple of thousand simulations, I have a > process that manages which simulations have been done, and which still > need to be done, so I can run it easily on a cluster. > > So, I have a fairly simple schema of 4 tables. > > -- na, ca,ks,kf,lk,iinj are the parameters for my simulation. > CREATE TABLE alljobs ( > id SERIAL, > ca int, > na int, > lk int, > ks int, > kf int, > iinj int, > PRIMARY KEY(id) > ); > > > CREATE TABLE assignedjobs ( > jobid int, > nodeid varchar(100), > assignedtime timestamp, > PRIMARY KEY(jobid) > ); > > > CREATE TABLE completedjobs ( > jobid int, > PRIMARY KEY(jobid) > ); > > CREATE TABLE unassignedjobs( > jobid int, > PRIMARY KEY(jobid) > ); > > > > alljobs is initially populated, and contains all the simulations that > will ever be run > unassignedjobs contains the ids in alljobs that havent been run yet > assignedjobs contains the ids in alljobs that have been dispatched to > some cpu on the cluster and are currently simulating > completedjobs contains all the completed jobs. > > So fairly simply, I have a daemon running on a machine, which accesses > this DB. Clients connect and request the details for say 1000 > simulations, at which point the daemon takes 1000 entries from the > unassigned table and moves them to the assigned table. The once the > client is finished with those jobs, it signals this to the daemon, > which then move those jobs from 'assigned' to 'complete'. > > So this is fairly simple to implement, but my problem is that it is very slow. > > > In particular, I have a 'select' waiting for network connections, > which then calls this function: > > typedef vector<long> VectorLong; > VectorLong assignJobs(PGconn* pDB, int number, string nodename) > { > char buffer[1000]; > sprintf(buffer,"SELECT jobid from unassignedjobs LIMIT %d",number); > PGresult* pRes = PQexec(pDB, buffer); > > printf("assigning jobs"); > > //PGresult* pRes = PQexec(pDB, "SELECT * from alljobs LIMIT 100"); > //PGresult* pRes = PQexec(pDB, "SELECT * from alljobs"); > > int nRes = PQntuples(pRes); > printf("Results found: %d",nRes); > > VectorLong jobs; > for(int i=0;i<nRes;i++) > { > long id = atol( PQgetvalue(pRes,i,0) ); > cout << id << " "; > jobs.push_back(id); > > sprintf(buffer, "DELETE FROM unassignedjobs WHERE jobid = %ld", id); > PQexec(pDB, buffer); > > sprintf(buffer, "INSERT INTO assignedjobs (jobid,nodeid) VALUES > (%ld, %s)", id, nodename.c_str() ); > PQexec(pDB, buffer); > } > > > return jobs; > } > > but it is painfully slow. I was wondering if there is a way to improve > this? I feel there should be since I already have a 'pointer' to the > rows I want to delete. > > > Any help would be greatly appreciated. > > Many thanks > > > Mike Hull > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Michael Hull <mikehulluk@googlemail.com> writes: > I am fairly new to practical databases, but I am trying out the c > interface to postgres and am wondering how to improve performance. I > am a researcher, and I am trying to perform a large parameter sweep. > Since this will involve a couple of thousand simulations, I have a > process that manages which simulations have been done, and which still > need to be done, so I can run it easily on a cluster. There's basically no way for the performance of that loop to not suck. You're incurring two round trips to the server per jobid change, plus query parse/plan times; so even though the underlying table manipulations are relatively simple, there's just too much overhead. One way to fix this problem is to push the procedural logic into a stored procedure that runs on the server, so that your app sends one query, gets back one rowset of its newly assigned jobs, and all the database update side-effects are done by the procedure. That's only a small conceptual leap from where you are, but does require getting up to speed on plpgsql or one of the other procedural languages. The more radical way to fix it is to write the whole thing as one SQL command. This requires thinking about your problem as an operation on a set of rows, rather than an iteration, so it can be a pretty big conceptual jump for database novices. I'm not sure it's actually possible to do it given the specific table organization you've adopted --- if you need to both delete rows in unassignedjobs and insert rows in assignedjobs, there's no way to do it in one SQL operation. But if you're not yet wedded to that representation, you should consider having just one table and implementing the state change as an update to a status column instead of moving the data to a different table. Then you could probably implement the operation in a single UPDATE ... RETURNING command. Also, this looks suspiciously like a priority queue, which means you're basically reinventing the wheel. People have sweated the details on this type of thing before, and come up with very clever solutions that perform well even with multiple clients concurrently trying to obtain job assignments. Simple solutions tend to slow way down or even deadlock under those conditions :-(. Check the Postgres list archives. regards, tom lane
On 09/14/10 5:55 PM, Michael Hull wrote: > So fairly simply, I have a daemon running on a machine, which accesses > this DB. Clients connect and request the details for say 1000 > simulations, at which point the daemon takes 1000 entries from the > unassigned table and moves them to the assigned table. The once the > client is finished with those jobs, it signals this to the daemon, > which then move those jobs from 'assigned' to 'complete'. > > So this is fairly simple to implement, but my problem is that it is very slow. > > instead of moving data from one table to another, it might be better to just have a table of simulations, then another table which just contains the PK of each simulation, and a flag that says its assigned or unassigned (and maybe the client its assigned to? and anything else thats related to this assignment?)... so instead of moving your big table rows, which involves deleting them from one table and inserting them into another, you just update the row of this small table. if you create this small table with a fillfactor like 75%, the updates likely will easily be handled by HOT
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of John R Pierce > Sent: Tuesday, September 14, 2010 8:41 PM > To: Michael Hull > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Search then Delete Performance > > On 09/14/10 5:55 PM, Michael Hull wrote: > > So fairly simply, I have a daemon running on a machine, which > accesses > > this DB. Clients connect and request the details for say 1000 > > simulations, at which point the daemon takes 1000 entries from the > > unassigned table and moves them to the assigned table. The once the > > client is finished with those jobs, it signals this to the daemon, > > which then move those jobs from 'assigned' to 'complete'. > > > > So this is fairly simple to implement, but my problem is that it is > very slow. > > > > > > instead of moving data from one table to another, it might be better to > just have a table of simulations, then another table which just > contains > the PK of each simulation, and a flag that says its assigned or > unassigned (and maybe the client its assigned to? and anything else > thats related to this assignment?)... so instead of moving your big > table rows, which involves deleting them from one table and inserting > them into another, you just update the row of this small table. if > you > create this small table with a fillfactor like 75%, the updates likely > will easily be handled by HOT Or just a status integer in the main table along the lines of: 1 = unassigned 2 = assigned 3 = running 4 = completed Etc. And then update the status as appropriate and check the status as needed. If you want until a batch is done, you would also be able to update like this: UPDATE jobs SET status = 4 WHERE status = 3 As you like, with a single statement. There are lots of job schedulers on SOURCEFORGE. http://sourceforge.net/search/?words=scheduler+workflow&type_of_search=soft&sort=latest_file_date&sortdir=desc&limit=100
On Wed, Sep 15, 2010 at 5:15 AM, Dann Corbit <DCorbit@connx.com> wrote: >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >> owner@postgresql.org] On Behalf Of John R Pierce >> Sent: Tuesday, September 14, 2010 8:41 PM >> To: Michael Hull >> Cc: pgsql-general@postgresql.org >> Subject: Re: [GENERAL] Search then Delete Performance >> >> On 09/14/10 5:55 PM, Michael Hull wrote: >> > So fairly simply, I have a daemon running on a machine, which >> accesses >> > this DB. Clients connect and request the details for say 1000 >> > simulations, at which point the daemon takes 1000 entries from the >> > unassigned table and moves them to the assigned table. The once the >> > client is finished with those jobs, it signals this to the daemon, >> > which then move those jobs from 'assigned' to 'complete'. >> > >> > So this is fairly simple to implement, but my problem is that it is >> very slow. >> > >> > >> >> instead of moving data from one table to another, it might be better to >> just have a table of simulations, then another table which just >> contains >> the PK of each simulation, and a flag that says its assigned or >> unassigned (and maybe the client its assigned to? and anything else >> thats related to this assignment?)... so instead of moving your big >> table rows, which involves deleting them from one table and inserting >> them into another, you just update the row of this small table. if >> you >> create this small table with a fillfactor like 75%, the updates likely >> will easily be handled by HOT > > Or just a status integer in the main table along the lines of: > 1 = unassigned > 2 = assigned > 3 = running > 4 = completed > Etc. > > And then update the status as appropriate and check the status as needed. > > If you want until a batch is done, you would also be able to update like this: > > UPDATE jobs SET status = 4 WHERE status = 3 > > As you like, with a single statement. > > There are lots of job schedulers on SOURCEFORGE. > http://sourceforge.net/search/?words=scheduler+workflow&type_of_search=soft&sort=latest_file_date&sortdir=desc&limit=100 > > Thanks everyone for the input - I will have an investigate Mike