ers first on what is needed. Thanks!
Martin Foster
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend
Gaetano Mendola wrote:
Martin Foster wrote:
I run a Perl/CGI driven website that makes extensive use of PostgreSQL
(7.4.3) for everything from user information to formatting and display
of specific sections of the site. The server itself, is a dual
processor AMD Opteron 1.4Ghz w/ 2GB Ram and
Michael Adler wrote:
On Wed, Aug 04, 2004 at 03:49:11AM +, 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
Gaetano Mendola wrote:
Martin Foster wrote:
Gaetano Mendola wrote:
Martin Foster wrote:
I run a Perl/CGI driven website that makes extensive use of
PostgreSQL (7.4.3) for everything from user information to
formatting and display of specific sections of the site. The
server itself, is a dual
alBits/Tidbits/perf.html
I am simply curious, as this clearly shows that my understanding of
PostgreSQL is clearly lacking when it comes to tweaking for the hardware.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of bro
n INT which may have helped it handle
timestamps more efficiently.It also made use of three or more
queries, where two were done to generate an IN statement for the query
actually running at the time.
Martin Foster
Creator/Designer Ethereal Real
Scott Marlowe wrote:
On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote:
Martin Foster wrote:
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192< This is really too small for your
configuration
sort_mem = 2048
wal_buffers = 128< T
Scott Marlowe wrote:
On Fri, 2004-08-06 at 22:02, Martin Foster wrote:
Scott Marlowe wrote:
On Fri, 2004-08-06 at 17:24, Gaetano Mendola wrote:
Martin Foster wrote:
Gaetano Mendola wrote:
Let start from your postgres configuration:
shared_buffers = 8192< This is really too small
Jan Wieck wrote:
On 8/3/2004 2:05 PM, Martin Foster wrote:
I run a Perl/CGI driven website that makes extensive use of PostgreSQL
(7.4.3) for everything from user information to formatting and display
of specific sections of the site. The server itself, is a dual
processor AMD Opteron 1.4Ghz
.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Jeff wrote:
On Aug 8, 2004, at 1:29 AM, Martin Foster wrote:
I am currently making use of Apache::DBI which overrides the
DBI::disconnect call and keeps a pool of active connections for use
when need be. Since it offloads the pooling to the webserver, it
seems more advantageous then pgpool
he core.So not sure exactly how to proceed, since I rather
need the thing to fork additional servers as load hits and not the other
way around.
Unless I had it configured oddly, but it seems work differently then an
Apache server would to handle content.
Martin Foster
Cr
using sub-queries under EXPLAIN ANALYZE proves itself to be
less efficient and have a far higher cost then those with the penalty of
a sub-query. Since this seems to be counter to what I have been told
in the past, I thought I would bring this forward and get some
enlightenment.
Martin
Tom Lane wrote:
Martin Foster <[EMAIL PROTECTED]> writes:
The one not using sub-queries under EXPLAIN ANALYZE proves itself to be
less efficient and have a far higher cost then those with the penalty of
a sub-query. Since this seems to be counter to what I have been told
in the p
ust a near instant lock-up of the server itself and
that is with a non-privileged user.
While I know this is a Perl issue, but figured I might be able to gain
some insight on how a server could drop without at least generating a
panic. Any ideas?
Martin Foster
Creator/Designer Ether
being used and which are not. This will allow me to drop off the
unneeded ones and reduce database load as a result.
And have things changed as to allow for mismatched multi-column indexes
in version 7.4.x or even the upcoming 8.0.x?
Martin Foster
[EMAIL PROTECTED
averages which may have led to the machine dropping outright.
Any help on this matter would be appreciated.
--
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ
Simon Riggs wrote:
On Tue, 2004-11-02 at 23:52, Martin Foster wrote:
Is there a way to restrict how much load a PostgreSQL server can take
before dropping queries in order to safeguard the server?I was
looking at the login.conf (5) man page and while it allows me to limit
by processor time
John A Meinel wrote:
Martin Foster wrote:
Simon Riggs wrote:
On Tue, 2004-11-02 at 23:52, Martin Foster wrote:
[...]
I've seen this behavior before when restarting the web server during
heavy loads.Apache goes from zero connections to a solid 120,
causing PostgreSQL to spawn that
n previous threads, the scripts are completely
database driven and at the time the database averaged 65 queries per
second under MySQL before a migration, while the webserver was averaging
2 to 4.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
lly driven sites and get slammed by Slashdot for example.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
still get the spike if you have to start the webserver and
database server at or around the same time.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all l
se of it, but in
the grand scheme of things the amount of traffic they tie up is
literally inconsequential. Though I will probably move all of that
onto another server just to allow the main server the capabilities of
dealing with almost exclusively dynamic content.
Martin F
se and the apache server
were on the same machine this type of scenario would be unstable to say
the least.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading th
.
This should be my last question on the matter, does squid report the
proper IP address of the client themselves?That's a critical
requirement for the scripts.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broa
VACUUM FULL and such.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Is the use of connection pooling consider bad?
Should flush be run more then once a day? I have no intention of going
back to MySQL, and would like to make this new solution work.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
--
Shridhar Daithankar wrote:
On 5 Jul 2003 at 22:54, Martin Foster wrote:
What I would like to know is. Why? The kernel has been compiled to
handle the number of concurrent connections, the server may not be the
best, but it should be able to handle the requests: PIII 1Ghz, 1GB
SDRAM, 2 IDE
Richard Huxton wrote:
On Sunday 06 Jul 2003 5:54 am, Martin Foster wrote:
The only time that I have ever seen load averages of 30 or more under
OpenBSD is when one of my scripts goes wild.However, I can say that
I am also seeing these load averages under PostgreSQL 7.3.2 after a
migration to
child count at the right level.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
vmstat:
2:09PM up 16:45, 1 user, load averages: 0.36, 0.30, 0.35
vmstat:
procs memory
r b wavmfre
1 0 0 234036 687548
page
flt re pi po fr sr
621 0 0 0
Tom Lane wrote:
Martin Foster <[EMAIL PROTECTED]> writes:
The only time that I have ever seen load averages of 30 or more under
OpenBSD is when one of my scripts goes wild.
Note also that "high load average" is not per se an indication that
anything is wrong. In Postgres, if
example chances are it won't exactly be what your
looking for.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropri
cessor seems to be purposely sitting there twiddling it's thumbs.
Which leads me to believe that perhaps the nice levels have to be
changed on the server itself?And perhaps increase the file system
buffer to cache files in memory instead of always fetching/writing them?
Anyone more ideas?
the query planner.
Does this mean that disabling these reduces debugging overhead and
streamlines things? The documentation is rather lacking for
information on these.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
--
th=27)
-> Seq Scan on realm re
(cost=0.00..11.93 rows=193 width=27)
-> Sort (cost=5008.78..5100.22 rows=36574 width=10)
Sort Key: ch.puppeteerlogin
-> Seq Scan on puppet ch
(c
Scan using pkpuppetignore on puppetignore
(cost=0.00..5.84 rows=1 width=15) (actual time=0.01..0.01 rows=0 loops=55)
Index Cond: ((puppeteerlogin = 'root'::character
varying) AND (puppetname = $1))
Filter: (puppetignore = 'single'::character var
same
table and seems to have no performance impact from doing as such, and
the position based search is considerably faster.
I can show EXPLAIN ANALYSE for all of those if you wish.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---
of optimization is not something I have
had to deal with in the past.
Also to make this interesting. The sub-query method is faster at times
and slower in others. But doing two separate queries and working on
the PostIDNumber field exclusively is always blazingly fast...
Martin Foster
tever in order to have that
configuration run.
Perl may be useful in this for a few reasons. It's portable enough to
run on multiple Unix variants and the tools would be fairly standard, so
the code would require less considerations for more exotic implementations.
Mar
platforms. When postgres installs the databases, it checks
for 'tune.xxx' and if found uses that to generate the script itself?
This would allow for defaults on platforms that do not have them and
optimization for those that do.
Martin Foster
Creator/Designer Ethereal Rea
Dennis Björklund wrote:
On Sun, 6 Jul 2003, Martin Foster wrote:
The processor seems to be purposely sitting there twiddling it's thumbs.
Which leads me to believe that perhaps the nice levels have to be
changed on the server itself?
It could also be all the usual things that a
and grow right?
BTW, I can't thank you all enough for this general advice. It's
helping me get this thing running very smoothly.
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)--
Shridhar Daithankar wrote:
On 10 Jul 2003 at 0:43, Martin Foster wrote:
As for creating a new table, that in itself is a nice idea. But it
would cause issues for people currently in the realm. Their posts
would essentially dissapear from site and cause more confusion then its
worth.
No
l performance.
So thanks for that! As a side note, would you recommend disabling
fsync for added performance? This would be joined with a healthy dose
of a kernel file system buffer.
Simply curious, as I have been increasing certain options for the WAL to
mean it writes less often (transa
e too many rows purged for vacuum to
accurately keep track of?
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index sc
45 matches
Mail list logo