On Friday 01 October 2004 9:04 am, Shane | SkinnyCorp wrote:
> Hey, my name is Shane Witschen and I'm the Systems Administrator
> (mainly a developer) for a small web development company. We
> recently switched over to PostgreSQL after going over some of the
> powerful features that it holds over MySQL.
>
> However, after we launched one site, we slapped ourselves in the
> head. We have run a message board for a few years now, and have
> always used MySQL for the backend, but recently switched to
> PostgreSQL. It has always been lightening fast with MySQL, but has
> slowed to nearly a halt in terms of online access time. I can't
> seem to do anything about it!! PLEASE HELP US!!
Others have mentioned issues with indexes, schema and such. Let me
explain vacuum.
If you haven't vacuumed regularly then you may have very large disk
files relative to your database size - expecially if you have much
update activity.
PG, unlike mysql, has full multi-version concurrency control. This is
just one piece of the power that sets it apart from mysql and others.
With MVCC in PG, every record that is updated within a transaction is
duplicated on-disk. The updated tuples are not visible to other
transactions until the updating transaction is complete. The old
versions of the updated tuples are no longer visible when all running
transactions no longer need the old tuples.
Vacuuming marks the unused space as reusable. If you did a lot of
development without regular vacuums then your disk size may be huge
compared to what is necessary. You can physically shrink the file by
running a "vacuum full" but this will lock the table for the duration
of the vacuum (probably not too much of a problem if you are already
seeing 10+ second page loads).
Note: getting your disk files to a size that allows them to be better
cached by the OS can make a huge difference in query speed -
especially if you are doing full table scans either intentionally or
due to bad design.
The "analyze" command updates the statistics that PG needs in order
for the query planner to make appropriate use of indexes and such.
After running your "vacuum full", be sure to run "vacuum analyze"
regularly. Check out the autovacuum daemon to help automate this
process.
I have a similar machine (but more RAM) and can easily run a count(*)
of a 4+ million row table in well under 4 seconds which makes me
suspicious of your vacuuming.
Cheers,
Steve