Re: CPU usage when building a schema

Mladen,

Thank you for your reply. I apologize for not including the system specifics in my initial post.

We are running Postgres 8.3.7 on a Sun M5000 with 2 x quad core CPUs (16 threads) running Solaris 10.

When I run an SQL to create new tables and indexes is when Postgres consumes all CPU and impacts other users on the server.

I've attached the sar data at the time of the run- here's a snip-it below.

Thanks!
Deb

****************************************************

Here, note the run queue, the left column. That is the number of processes waiting to run. 97 processes waiting to run at any time with only eight CPU cores looks very busy.

root(at)core2 # sar -q 5 500

SunOS core2 5.10 Generic_142900-11 sun4u 06/17/2010

12:01:50 runq-sz %runocc swpq-sz %swpocc
12:01:55 1.8 80 0.0 0
12:02:00 1.0 20 0.0 0
12:02:05 1.0 20 0.0 0
12:02:10 0.0 0 0.0 0
12:02:15 0.0 0 0.0 0
12:02:21 3.3 50 0.0 0
12:02:26 1.0 20 0.0 0
12:02:31 1.0 60 0.0 0
12:02:36 1.0 20 0.0 0
12:02:42 27.0 50 0.0 0
12:02:49 32.8 83 0.0 0
12:02:55 76.0 100 0.0 0
12:03:01 66.1 100 0.0 0
12:03:07 43.8 100 0.0 0
12:03:13 52.0 100 0.0 0
12:03:19 91.2 100 0.0 0
12:03:26 97.8 83 0.0 0
12:03:33 63.7 100 0.0 0
12:03:39 67.4 100 0.0 0
12:03:47 41.5 100 0.0 0
12:03:53 82.0 83 0.0 0
12:03:59 88.7 100 0.0 0
12:04:06 87.7 50 0.0 0
12:04:12 41.3 100 0.0 0
12:04:17 94.3 50 0.0 0
12:04:22 1.0 20 0.0 0
12:04:27 3.3 60 0.0 0
12:04:32 1.0 20 0.0 0
12:04:38 0.0 0 0.0 0

-----Original Message-----
From: Mladen Gogala [mailto:mladen(dot)gogala(at)vmsinfo(dot)com]
Sent: Wednesday, June 23, 2010 5:50 AM
To: Deborah Fuentes
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] CPU usage when building a schema

Deborah Fuentes wrote:
>
> Hello,
>
>
>
> I'm new to Postgres so I apologize in advance for the simple question....
>
>
>
> Why is it when I'm building an empty schema does Postgres consume all
> the CPU resources? My experience is with Informix and I've never seen
> anything like this.
>
>
>
> Thanks!
>
> Deb
>
What kind of a machine are you using? What OS, version and architecture?
What Postgres version? If Linux, did you do "strace" to see what is
going on? You can even compile PgSQL with the "-g" flag and use gprof
to find out where the time is spent. Furthermore, what do you mean by
"building an empty schema"? Are we talking about "create schema" command
or something else? I tried creating an empty schema and it wasn't even
visible on CPU:
[mgogala(at)medo ~]$ psql
Timing is on.
psql (8.4.4)
Type "help" for help.

mgogala=# create schema test;
CREATE SCHEMA
Time: 192.082 ms
mgogala=# drop schema test;
DROP SCHEMA
Time: 59.451 ms
mgogala=#

Simultaneously, in another window I did this:
[mgogala(at)medo ~]$ sar -u 3 15
Linux 2.6.18-194.3.1.el5.centos.plusPAE (medo) 06/23/2010

08:45:43 AM CPU %user %nice %system %iowait
%steal %idle
08:45:46 AM all 3.00 0.00 0.83 1.50
0.00 94.68
08:45:49 AM all 2.17 0.00 0.67 0.00
0.00 97.16
08:45:52 AM all 2.16 0.00 1.00 0.00
0.00 96.84
08:45:55 AM all 2.50 0.00 1.50 3.00
0.00 93.01
08:45:58 AM all 2.34 0.00 0.33 0.00
0.00 97.33
08:46:01 AM all 0.67 0.00 0.50 1.00
0.00 97.83

[mgogala(at)medo ~]$

It didn't even make a ripple. My Postgres is 8.4.4, Linux is CentOS 5.5:
mgogala(at)medo ~]$ uname -a
Linux medo 2.6.18-194.3.1.el5.centos.plusPAE #1 SMP Wed May 19 10:00:02
EDT 2010 i686 athlon i386 GNU/Linux
[mgogala(at)medo ~]$
What versions of OS and DB do you have? Do you have an OS or are you
running Windows?

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com