Hi, there,
I am running PostgreSQL 7.3.4 on MAC OS X G5 with dual processors and
8GB memory. The shared buffer was set as 512MB.
The database has been running great until about 10 days ago when our
developers decided to add some indexes to some tables to speed up
certain uploading ops.
Now the CPU usage reaches 100% constantly when there are a few users
accessing their information by SELECT tables in databases. If I REINEX
all the indexes, the database performance improves a bit but before
long,
it goes back to bad again.
My suspicion is that since now a few indexes are added, every ops are
run by PostgreSQL with the indexes being used when calculating cost.
This leads to the downgrade of performance.
What do you think of this? What is the possible solution?
Thanks!
Qing
The following is the output from TOP command:
Processes: 92 total, 4 running, 88 sleeping... 180 threads
13:09:18
Load Avg: 2.81, 2.73, 2.50 CPU usage: 95.2% user, 4.8% sys, 0.0%
idle
SharedLibs: num = 116, resident = 11.5M code, 1.66M data, 4.08M
LinkEdit
MemRegions: num = 12132, resident = 148M + 2.82M private, 403M shared
PhysMem: 435M wired, 5.04G active, 2.22G inactive, 7.69G used, 316M
free
VM: 32.7G + 81.5M 5281127(13) pageins, 8544145(0) pageouts
PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE
VSIZE
27314 postgres 92.2% 2:14.75 1 9 49 12.8M+ 396M 75.0M+
849M
26099 postgres 91.1% 19:28.04 1 9 67 15.9M+ 396M 298M+
850M
24754 top 2.8% 4:48.33 1 29 26 272K 404K 648K
27.1M
0 kernel_tas 1.9% 2:12:05 40 2 8476 67.1M 0K 281M
1.03G
294 hwmond 0.5% 2:26:34 8 75 57 240K 544K 1.09M
31.0M
347 lookupd 0.3% 1:52:28 2 35 73 3.05M 648K 3.14M
33.6M
89 configd 0.1% 53:05.16 3 126 151 304K 644K 832K
29.2M
26774 servermgrd 0.1% 0:02.93 1 10 40 344K- 1.17M+ 1.86M
28.2M
170 coreservic 0.1% 0:09.04 1 40 93 152K 532K 2.64M
28.5M
223 DirectoryS 0.1% 19:42.47 8 84 135 880K+ 1.44M 4.60M+
37.1M+
125 dynamic_pa 0.0% 0:26.79 1 12 17 16K 292K 28K
17.7M
87 kextd 0.0% 0:01.23 2 17 21 0K 292K 36K
28.2M
122 update 0.0% 14:27.71 1 9 15 16K 300K 44K
17.6M
1 init 0.0% 0:00.03 1 12 16 28K 320K 76K
17.6M
2 mach_init 0.0% 3:36.18 2 95 18 76K 320K 148K
18.2M
81 syslogd 0.0% 0:19.96 1 10 17 96K 320K 148K
17.7M
