Re: Performance Bottleneck

Michael Adler wrote:

> On Wed, Aug 04, 2004 at 03:49:11AM +0000, Martin Foster wrote:
>
>>Also note that some of these scripts run for longer durations even if
>>they are web based. Some run as long as 30 minutes, making queries to
>>the database from periods of wait from five seconds to twenty-five
>>seconds. Under high duress the timeouts should back out, based on
>>the time needed for the query to respond, normally averaging 0.008 seconds.
>
>
> I would start by EXPLAIN ANALYZE'ing those 30 minute queries.
>

The Apache process will run for 30 minutes at a time, not the query
itself. Essentially, while that process is running it will check for
new records in the table at varying intervals, since it will increase
timeouts based on load or lack of activity in order to reduce load to
the database.

>
>>martin(at)io ~$ vmstat
>> procs memory page disks faults cpu
>> r b w avm fre flt re pi po fr sr ad4 ad6 in sy cs us sy id
>> 0 0 0 498532 122848 3306 0 0 0 740 0 0 0 788 0 1675 16 21 63
>>
>
>
> vmstat without a "delay" argument (e.g. 'vmstat 1') gives you a
> cumulative or average since boot. You'd probably get better
> information by doing a real-time sampling of stats during normal and
> heavy load.
>
>
>>martin(at)io ~$ ps -uax
>>USER PID %CPU %MEM VSZ RSS TT STAT STARTED TIME COMMAND
>>postgres 32084 0.0 0.2 91616 3764 p0- R Mon12PM 4:08.99 /usr/local/bin/postmaster -D /var/postgres (postgres)
>>postgres 80333 0.0 2.1 94620 44372 ?? S 8:57PM 0:01.00 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 80599 0.0 2.1 94652 44780 ?? S 8:59PM 0:00.97 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 80616 0.0 2.4 94424 50396 ?? S 8:59PM 0:00.89 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 80715 0.0 2.2 94444 46804 ?? S 9:00PM 0:00.68 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 80788 0.0 2.1 94424 43944 ?? S 9:00PM 0:00.93 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 80811 0.0 2.1 94424 43884 ?? S 9:00PM 0:00.94 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 80902 0.0 2.1 94424 43380 ?? S 9:01PM 0:00.76 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 80949 0.0 2.2 94424 45248 ?? S 9:01PM 0:00.67 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>>postgres 81020 0.0 2.1 94424 42924 ?? S 9:02PM 0:00.74 postmaster: ethereal ethereal 192.168.1.6 idle in trans
>
>
> All the connections in your email are idle. You may benefit from using
> pgpool instead of Apache::DBI (I've never tried).
>
> http://www.mail-archive.com/pgsql-announce(at)postgresql(dot)org/msg00760.html
>

I will take a look into pgpool and see if it will serve as the solution
I need. The pre-pooling of children sounds like a good choice, however
since overhead is already a point of worry I almost wonder if I can host
it on another server in order to drop that overhead on the servers directly.

Anyone have experience with this on running it on the same machine or a
different machine then the database proper? Of course, if this works
as it should, I could easily put an older database server back into
operation provided pgpool does weighted load balancing.

Martin Foster
Creator/Designer Ethereal Realms
martin(at)ethereal-realms(dot)org