Re: Article about PostgreSQL and RAID in Brazil
От | Qing Zhao |
---|---|
Тема | Re: Article about PostgreSQL and RAID in Brazil |
Дата | |
Msg-id | 224B107D-0821-11D9-B278-000A95AB8896@supplyfx.com обсуждение исходный текст |
Ответ на | Re: Article about PostgreSQL and RAID in Brazil (Josh Berkus <josh@agliodbs.com>) |
Ответы |
Re: Question about PG on OSX
(Josh Berkus <josh@agliodbs.com>)
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: