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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
