Hi All,
I'm new here.
i was wondering if anyone could shed a light on the problem i am having.
I use a system for distributing broadband amongst users of for example a hotel, the system was designed by someone in the US and it is based on redhat 2.4 (i know its old) and the system uses Postgresl NOT mysql.
the problem i am having is that i cannot contact the company for linux support no more and the system itself is failing fast. the problem is speed. its really slow at performing database request, in some cases so slow that it just doesn't work at all.
I have a script on the system calles auto_vacumm for postgresql and it is setup in crontab to run daily, but this seems to make no difference to the performance of the server.
could anyone point me in the right direction to solving the problem, i have checked 'top' and the CPU is running @ 90%+ idle.
any information would be very much appreciated.
thanks in advance
Clive.
If the system is that old, I will assume you have a LOT of rows in your tables, unless you are actively purging and archiving data. Based on what you say, and if iostat does not show bottlenecks then it very probably has to be a db problem.
you did check iostat, right?
First: Look into record chaining. This occurs when you have updates applied to old records. Over time a full table scan on certain columns becomes almost impossible, time-wise. iostat will report more I/O because of chaining, but basically chaining can defeat disk and software caching and LRU strategies.
i have just looked at the iostat... and it is as follows
linux 2.4.20-28.7
avg-cpu:
user - 2.87
nice - 0.00
sys - 5.32
idle - 91.80
then it shows something about device.
i can access the php pages on the system fine, just if any of the refer back to the db thats when it struggles. a fresh install will work great for a few months then i am back to the slowness problem.
clive306,
Can you post the version of POSTGRESQL you are using?Do you perform a maintainance detach / attached of the whole database? quarterly maintenance ?
HOw is the disk space usage of your database in a daily basis? .
Cheers!