Thread: why is the db so slow?

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

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

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

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