pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
why is the db so slow?
4+ messages / 4 participants
[nested] [flat]

* why is the db so slow?
@ 2003-05-03 17:40  Becky Neville <rebecca.neville@yale.edu>
  0 siblings, 2 replies; 4+ messages in thread

From: Becky Neville @ 2003-05-03 17:40 UTC (permalink / raw)
  To: pgsql-performance

I am running my own database server but I don't have root privilege (and 
no hope of getting it.)

I only have 3 tables, with rowcounts of 3000, 48000 and 2 million.
I don't think this is that many rows but most things take a long time to 
run.  There are a lot of indexes on each table and creating an index on 
the 2mil row table takes forever, which I could perhaps live with BUT -

typing something as dumb as  \! pwd is not instantaneous either and there 
doesn't seem to be anyone else hogging up the CPU.

I am on Linux and due to lack of space in my own account, I have PGDATA 
pointing to /tmp.
(This is for a class project to analyze query performance ...I can 
recreate the data at any time if necessary.)

Are there any parameters I can set to speed things up?

Thanks
Becky




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: why is the db so slow?
@ 2003-05-03 18:31  Joe Conway <mail@joeconway.com>
  parent: Becky Neville <rebecca.neville@yale.edu>
  1 sibling, 0 replies; 4+ messages in thread

From: Joe Conway @ 2003-05-03 18:31 UTC (permalink / raw)
  To: Becky Neville <rebecca.neville@yale.edu>; +Cc: pgsql-performance

Becky Neville wrote:
> Are there any parameters I can set to speed things up?
> 

You haven't given us much in the way of specifics to work with, but here 
is a short list of things to try/do:

- read (amongst other things):
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/performance-tips.html
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/runtime-config.html

- run "VACUUM ANALYZE" on your database
- adjust key default configuration settings:
   shared_buffers = 1000 (or maybe 2000 or even 4000 -- above that you'd
                          need root access, and it might not help anyway)
   sort_mem = 8192 (depending on the amount of RAM in the server, this
                    might be too high/low, but start with something in
                    the 4000 to 8000K range)
- run "EXPLAIN ANALYZE" on your queries, and send in the results and the 
table structure details to the list.

HTH,

Joe




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: why is the db so slow?
@ 2003-05-03 18:32  Josh Berkus <josh@agliodbs.com>
  parent: Becky Neville <rebecca.neville@yale.edu>
  1 sibling, 1 reply; 4+ messages in thread

From: Josh Berkus @ 2003-05-03 18:32 UTC (permalink / raw)
  To: Becky Neville <rebecca.neville@yale.edu>; pgsql-performance

Becky,

> I am running my own database server but I don't have root privilege (and
> no hope of getting it.)
<snip>
> typing something as dumb as  \! pwd is not instantaneous either and there
> doesn't seem to be anyone else hogging up the CPU.

It sounds to me like the system has something wrong with it if "pwd" takes a 
while to respond.  Even if CPU isn't in heavy use, I'd guess some other 
process is eating RAM or disk I/O

> I am on Linux and due to lack of space in my own account, I have PGDATA
> pointing to /tmp.
> (This is for a class project to analyze query performance ...I can
> recreate the data at any time if necessary.)

Really?   What class?   I'm personally very interested to know of schools that 
are teaching PostgreSQL.

However, if this is for school, PostgreSQL is not very efficient being run as 
a seperate installation for each user.    For multiuser installations, it is 
far better to have one installation and many databases with restricted 
permissions.

I also suspect that you database being in /tmp may be causing you problems; 
many sysadmins put the /tmp partition on their slowest drive since it's 
regarded as disposable.

> Are there any parameters I can set to speed things up?

Lots, the settings of many of which are a matter of debate.   I suggest that 
you browse through the online archives of this list, which will be far more 
educational than me giving you a few tips.

However, be aware that no postgresql.conf settings, however clever, can make 
up for an overloaded system, poor disk configuration, or slow system I/O.   
At best correct settings ameliorate poor performance.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: why is the db so slow?
@ 2003-05-03 20:30  brew@theMode.com
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 4+ messages in thread

From: brew@theMode.com @ 2003-05-03 20:30 UTC (permalink / raw)
  To: pgsql-performance


replying to Becky and Josh's reply.....

> It sounds to me like the system has something wrong with it if "pwd"
> takes a while to respond.  Even if CPU isn't in heavy use, I'd guess
> some other process is eating RAM or disk I/O

Look into the unix command 'top'.  It lists processes and the amount of
resources they are using.  Although if it's another user using them it may
not detail them.... but I think you can get some idea of what other users
are up to from the CPU idle time and server load averages from the 'top'
display.

> However, if this is for school, PostgreSQL is not very efficient being
> run as a seperate installation for each user.  For multiuser
> installations, it is far better to have one installation and many
> databases with restricted permissions.

I can attest to that, I run a web site using virtual hosting (about 80
users, each with their own version of Apache (and in my case, my own
version of postgreSQL, I have no idea what the other users are running).
My development Linux laptop is 5 to 10 times faster than the web site, of
course, I'm it's ONLY user.

brew




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-05-03 20:30 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-03 17:40 why is the db so slow? Becky Neville <rebecca.neville@yale.edu>
2003-05-03 18:31 ` Joe Conway <mail@joeconway.com>
2003-05-03 18:32 ` Josh Berkus <josh@agliodbs.com>
2003-05-03 20:30   ` brew@theMode.com

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox