Обсуждение: PostgreSQL using 100% CPU
> 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.
>
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.
Could be some problems in the I/O,too.
--
Emanuel Calvo Franco
www.emanuelcalvofranco.com.ar
ArPug.com.ar / aosug.com.ar
On Thu, Jul 16, 2009 at 3:00 AM, rasa<openok@gmail.com> wrote: > 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. How do you detect this? Are you using top? if so, what does it say actually? Give us the first 20 or so lines of top to look at (better as an attachment so the formatting won't get all messed up). I'm just wondering if it looks like this: top - 13:11:10 up 1 day, 18:47, 5 users, load average: 0.49, 0.77, 1.03 Tasks: 137 total, 1 running, 136 sleeping, 0 stopped, 0 zombie Cpu(s): 7.7%us, 4.8%sy, 0.1%ni, 0.1%id, 91.0%wa, 0.2%hi, 0.9%si, 0.0%st Mem: 3730776k total, 3475708k used, 255068k free, 65648k buffers Swap: 4883720k total, 63748k used, 4819972k free, 2248484k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2894 postgres 20 0 861m 189m 15m S 99.5 5.2 67:26.89 postgres Or like this: top - 13:11:10 up 1 day, 18:47, 5 users, load average: 0.49, 0.77, 1.03 Tasks: 137 total, 1 running, 136 sleeping, 0 stopped, 0 zombie Cpu(s): 91.0%us, 4.8%sy, 0.1%ni, 0.1%id, 7.7%wa, 0.2%hi, 0.9%si, 0.0%st Mem: 3730776k total, 3475708k used, 255068k free, 65648k buffers Swap: 4883720k total, 63748k used, 4819972k free, 2248484k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2894 postgres 20 0 861m 189m 15m S 99.5 5.2 67:26.89 postgres Both of those show a postgres processing using ~100% of a cpu, but they're both VERY different in what they're really saying. The first is IO bound, and the only thing you can do about that is either stop using so much IO or get better hardware, the second is saying the CPU is actually doing most of the work, and in this case either stop using so much CPU or get faster ones. It's often easier to fix the second example, as it's often easier to optimise CPU hungry queries than IO hungry ones.
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
>> 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.
>
What's saying select pg_stat_sctivity() ?
You have able statistiscs?
--
Emanuel Calvo Franco
www.emanuelcalvofranco.com.ar
ArPug.com.ar / aosug.com.ar