Thread: Extremely high CPU usage when building tables

Extremely high CPU usage when building tables

From

Deborah Fuentes

Date:

01 July 2010, 05:50:25

Hello,

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

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

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

Any ideas would be greatly appreciated.

Thanks!
Deb

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

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

root@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

Re: Extremely high CPU usage when building tables

From

Rajesh Kumar Mallah

Date:

01 July 2010, 09:50:02

Hi,

1. Did you also check vmstat output , from sar output the i/o wait is not clear.
2. i gues you must be populating the database between creating tables and creating
     indexes. creating indexes require sorting of data that may be cpu intensive, loading/populating
     the data may saturate the i/o bandwidth . I think you should check when the max cpu utilisation
     is taking place exactly.

regds
Rajesh Kumar Mallah.

On Sat, Jun 26, 2010 at 3:55 AM, Deborah Fuentes <dfuentes@eldocomp.com> wrote:

Hello,

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

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

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

Any ideas would be greatly appreciated.

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@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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: Extremely high CPU usage when building tables

From

Rajesh Kumar Mallah

Date:

02 July 2010, 19:53:12

Dear Deb,

i feel three distinct steps are present
1. CREATE TABLE
2. LOAding of data in tables
3. Creation of indexes

It is still not clear when you are seeing your system becoming unresponsive to
other application. Is it during loading of data or creation of indexes?

1. can you give any idea about how much data you are loading ? rows count or GB data etc
2. how many indexes are you creation ?

regds
Rajesh Kumar Mallah.

#avg_ls_inline_popup { position:absolute; z-index:9999; padding: 0px 0px; margin-left: 0px; margin-top: 0px; width: 240px; overflow: hidden; word-wrap: break-word; color: black; font-size: 10px; text-align: left; line-height: 13px;}

Re: Extremely high CPU usage when building tables

From

Deborah Fuentes

Date:

04 July 2010, 12:12:23

We did see a small spike in disk I/O, but only had wait I/O for less than 10 seconds total. The low CPU idle event goes on for several minutes, wait I/O or heavier I/O does not correlate to the extended period.

System time does jump up at the same time as the user time. System times of 15% when CPU us at 60% (25% idle) is around the average for this test. We believe that jump is related to showing time spent getting processes on and off CPU to execute. No general system wait I/O is observed during this time.

Five second samples of the Fiber disk SAN volumes. Wait I/O is listed in red. Note it’s ten seconds or less.

extended device statistics