Re: PostgreSQL using 100% CPU

Hi, All!

I migrate from Win/MSSQL to FC/PostgreSQL. After month of work on fedora
> postgresql server, accounters can't calculate one form. I detect, that
> postgres process use a 100% of CPU.
> How solve this problem? Thanks a lot.
> 2 CPU, 4 Gb RAM, 2x250 Gb (229 Gb free), autovacuum is on.
>

When try to calculate an accounter form (for 1 month of trade, winsoft) the
program just hangs and normal state of postgresql-server (about 40% uses
CPU1 & 0% uses CPU2) suddenly change to 100% using of both CPU :(
After killing the winsoft accounters program, postgre SELECT process
continue using 100% of CPU's
Fedora Core 10, PostgreSQL 8.3, 2 x CPU Dual Core AMD Opteron 2216,
autovacuum turned on. size of DB - 68 Gb

> "Try to get this data:
> select * from pg_stat_activity; (shows you the backends running)
> With this try to detect if a query is hang on the server."

I have installed phppgadmin. But I can't understand where I can apply this
SQL-request?

Here is top and ps auxww results after run calculating on winclient.

top - 12:09:48 up 1 day, 20:00, 4 users, load average: 1.70, 1.16, 0.59
Tasks: 166 total, 3 running, 162 sleeping, 0 stopped, 1 zombie
Cpu(s): 91.2%us, 0.5%sy, 0.0%ni, 8.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3634572k total, 3518520k used, 116052k free, 66208k buffers
Swap: 5630772k total, 468k used, 5630304k free, 2971768k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
21738 postgres 20 0 138m 99m 28m R 99.6 2.8 347:52.61 postmaster
22675 root 20 0 55056 28m 8536 S 68.4 0.8 3:45.95 Xorg
28431 root 20 0 37712 21m 14m R 15.2 0.6 0:05.22 gnome-system-mo
23117 rooslan 20 0 100m 23m 13m S 0.6 0.7 0:01.52 gnome-terminal
22975 rooslan 20 0 27752 13m 8800 S 0.3 0.4 0:01.24 metacity
23025 rooslan 20 0 42036 18m 5596 S 0.3 0.5 0:03.12 gnome-screensav
1 root 20 0 2008 768 564 S 0.0 0.0 0:00.80 init
2 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kthreadd
3 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
4 root 15 -5 0 0 0 S 0.0 0.0 0:00.19 ksoftirqd/0
5 root RT -5 0 0 0 S 0.0 0.0 0:00.06 watchdog/0
6 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/1
7 root 15 -5 0 0 0 S 0.0 0.0 0:00.18 ksoftirqd/1
8 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
9 root 15 -5 0 0 0 S 0.0 0.0 0:01.00 events/0
10 root 15 -5 0 0 0 S 0.0 0.0 0:00.51 events/1
11 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 khelper
85 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/0
86 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/1
88 root 15 -5 0 0 0 S 0.0 0.0 0:00.12 kblockd/0
89 root 15 -5 0 0 0 S 0.0 0.0 0:00.04 kblockd/1
91 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
92 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 kacpi_notify
189 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 cqueue
193 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 ata/0
194 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 ata/1
195 root 15 -5 0 0 0 S 0.0 0.0 0:00.00 ata_aux

ps auxww | grep ^postgres

postgres 1960 0.0 0.1 40496 4644 ? S Jul15 0:01 /usr/bin/postmaster -p 5432
-D /var/lib/pgsql/data
postgres 1962 0.0 0.0 9244 620 ? Ss Jul15 0:18 postgres: logger process
postgres 1964 0.0 0.7 40496 26004 ? Ss Jul15 0:11 postgres: writer process
postgres 1965 0.0 0.0 40496 836 ? Ss Jul15 0:05 postgres: wal writer process
postgres 1966 0.0 0.0 41092 1276 ? Ss Jul15 0:04 postgres: autovacuum
launcher process
postgres 1967 0.0 0.0 9788 980 ? Ss Jul15 1:09 postgres: stats collector
process
postgres 21738 22.9 2.8 142104 101980 ? Rs Jul16 345:39 postgres: postgres
Talane 127.0.0.1(42884) SELECT
postgres 21816 22.3 0.9 59436 35800 ? Ss Jul16 336:12 postgres: postgres
Talane 127.0.0.1(38682) idle
postgres 21832 22.7 1.2 72972 44580 ? Ss Jul16 340:01 postgres: postgres
Talane 127.0.0.1(33835) idle
postgres 21847 0.0 1.0 59992 37576 ? Ss Jul16 0:59 postgres: postgres Talane
127.0.0.1(37269) idle
postgres 21885 0.3 1.1 63876 40928 ? Ss Jul16 4:57 postgres: postgres Talane
127.0.0.1(36184) idle

Thanks again
--
With best wishes, Rooslan.