Re: RES: [PERFORM] Priority to a mission critical transaction
Bruce, Someone should ask them to remove the article. Someone. Um, *who* taught for Big Nerd Ranch for several years, Bruce? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RES: [PERFORM] Priority to a mission critical transaction
Hi, There is an article about Lowering the priority of a PostgreSQL query (http://weblog.bignerdranch.com/?p=11) that explains how to use the setpriority() to lower PostgreSQL processes. I?m wondering how much effective it would be for i/o bound systems. Will the setpriority() system call affect i/o queue too? Reimer -Mensagem original- De: Jim C. Nasby [mailto:[EMAIL PROTECTED] Enviada em: domingo, 26 de novembro de 2006 22:52 Para: Brad Nicholson Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org Assunto: Re: [PERFORM] Priority to a mission critical transaction On Thu, Nov 23, 2006 at 03:40:15PM -0500, Brad Nicholson wrote: On Tue, 2006-11-21 at 21:43 -0200, Carlos H. Reimer wrote: Hi, We have an application that is mission critical, normally very fast, but when an I/O or CPU bound transaction appears, the mission critical application suffers. Is there a way go give some kind of priority to this kind of application? Reimer Not that I'm aware of. Depending on what the problems transactions are, setting up a replica on a separate machine and running those transactions against the replica might be the solution. The BizGres project has been working on resource quotas, which might eventually evolve to what you're looking for. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: RES: [PERFORM] Priority to a mission critical transaction
Carlos H. Reimer [EMAIL PROTECTED] writes: There is an article about Lowering the priority of a PostgreSQL query (http://weblog.bignerdranch.com/?p=11) that explains how to use the setpriority() to lower PostgreSQL processes. I?m wondering how much effective it would be for i/o bound systems. That article isn't worth the electrons it's written on. Aside from the I/O point, there's a little problem called priority inversion. See the archives for (many) past discussions of nice'ing backends. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: RES: [PERFORM] Priority to a mission critical transaction
* Carlos H. Reimer [EMAIL PROTECTED] [061128 20:02]: Hi, There is an article about Lowering the priority of a PostgreSQL query (http://weblog.bignerdranch.com/?p=11) that explains how to use the setpriority() to lower PostgreSQL processes. I?m wondering how much effective it would be for i/o bound systems. Will the setpriority() system call affect i/o queue too? Nope, and in fact the article shows the way not to do it. See http://en.wikipedia.org/wiki/Priority_inversion Basically, lowering the priority of one backend in PostgreSQL can lead to reduced performance of all, especially also the backends with higher priorities. (Think of priority inversion as a timed soft deadlock. It will eventually resolve, because it's not a real deadlock, but it might mean halting important stuff for quite some time.) Taking the example above, consider the following processes and nice values: 19x backends As nice = 0 1x backend B nice = 10 (doing maintenance work) 1x updatedb nice = 5 (running as a cronjob at night) Now, it possible (the probability depends upon your specific situation), where backend B grabs some internal lock that is needed, and then it gets preempted by higher priority stuff. Well, the A backends need that lock too, so they cannot run; instead we wait till updatedb (which updates the locate search db, and goes through the whole filesystem of the server) is finished. Luckily most if not all of these processes are disc io bound, so they get interrupted any way, and low priority processes don't starve. Well, replace updatedb with something hogging the CPU, and rethink the situation. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: RES: [PERFORM] Priority to a mission critical transaction
All, The Bizgres project is working on resource management for PostgreSQL. So far, however, they have been able to come up with schemes that work for BI/DW at the expense of OLTP. Becuase of O^N lock checking issues, resource management for OLTP which doesn't greatly reduce overall performance seems a near-impossible task. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: RES: [PERFORM] Priority to a mission critical transaction
Someone should ask them to remove the article. --- Tom Lane wrote: Carlos H. Reimer [EMAIL PROTECTED] writes: There is an article about Lowering the priority of a PostgreSQL query (http://weblog.bignerdranch.com/?p=11) that explains how to use the setpriority() to lower PostgreSQL processes. I?m wondering how much effective it would be for i/o bound systems. That article isn't worth the electrons it's written on. Aside from the I/O point, there's a little problem called priority inversion. See the archives for (many) past discussions of nice'ing backends. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: RES: [PERFORM] Priority to a mission critical transaction
Josh Berkus wrote: All, The Bizgres project is working on resource management for PostgreSQL. So far, however, they have been able to come up with schemes that work for BI/DW at the expense of OLTP. Becuase of O^N lock checking issues, resource management for OLTP which doesn't greatly reduce overall performance seems a near-impossible task. Right - I guess it is probably more correct to say that the implementation used in Bizgres is specifically targeted at BI/DW workloads rather than OLTP. At this point we have not measured its impact on concurrency in anything other than a handwaving manner - e.g pgbench on an older SMP system showed what looked like about a 10% hit. However the noise level for pgbench is typically 10% so - a better benchmark on better hardware is needed. Cheers Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Priority to a mission critical transaction
On Tue, 2006-11-21 at 21:43 -0200, Carlos H. Reimer wrote: Hi, We have an application that is mission critical, normally very fast, but when an I/O or CPU bound transaction appears, the mission critical application suffers. Is there a way go give some kind of priority to this kind of application? Reimer Not that I'm aware of. Depending on what the problems transactions are, setting up a replica on a separate machine and running those transactions against the replica might be the solution. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Priority to a mission critical transaction
Hi, We have an application that is mission critical, normally very fast, but when an I/O or CPU bound transaction appears, the mission critical application suffers. Is there a way go give some kind of priority to this kind of application? Reimer
