Re: Extremely high CPU usage when building tables - Mailing list pgsql-performance

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             

    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device

    1.0  296.9    0.0  136.5  0.0 62.3    0.0  209.0   0 168 c3

    0.0   11.4    0.0    0.1  0.0  0.5    0.0   41.6   0  30 c3t60A98000572D4275684A563761586D71d0

    0.4   28.0    0.0    0.9  0.0  3.0    0.0  104.4   0  41 c3t60A98000572D4275684A5638364D644Ed0

    0.6  257.5    0.0  135.6  0.0 58.8    0.0  227.9   0  98 c3t60A98000572D4275684A56385468434Fd0

                    extended device statistics             

    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device

   13.8  721.2    0.1  133.9  0.0 75.0    0.0  102.0   0 200 c3

    0.0   88.8    0.0    6.0  0.0 19.0    0.0  213.7   0  65 c3t60A98000572D4275684A563761586D71d0

    2.4   86.6    0.0    1.2  0.0  1.6    0.0   18.0   0  39 c3t60A98000572D4275684A5638364D644Ed0

   11.4  545.8    0.1  126.7  0.0 54.4    0.0   97.7   0  97 c3t60A98000572D4275684A56385468434Fd0

                    extended device statistics             

    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device

    3.6  769.0    0.0  123.2 29.4 182.9   38.1  236.7   5 220 c3

    0.0  104.2    0.0    1.4  0.0 34.3    0.0  329.0   0  46 c3t60A98000572D4275684A563761586D71d0

    1.0   77.0    0.0   13.1  0.0  8.1    0.0  103.2   0  77 c3t60A98000572D4275684A5638364D644Ed0

    2.6  587.8    0.0  108.8 29.4 140.5   49.9  238.0  41  98 c3t60A98000572D4275684A56385468434Fd0

                    extended device statistics             

    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device

    9.4  761.2    0.1  133.1  3.3 122.6    4.3  159.1   1 196 c3

    0.0   33.8    0.0    0.3  0.0  2.1    0.0   63.5   0  30 c3t60A98000572D4275684A563761586D71d0

    7.4   94.8    0.1    1.8  0.0 16.2    0.0  158.6   0  66 c3t60A98000572D4275684A5638364D644Ed0

    2.0  632.6    0.0  131.0  3.3 104.3    5.2  164.3  10  99 c3t60A98000572D4275684A56385468434Fd0

                    extended device statistics             

    r/s    w/s   Mr/s   Mw/s wait actv wsvc_t asvc_t  %w  %b device

    2.8  588.2    0.0  126.0  0.0 112.6    0.0  190.5   0 239 c3

    0.0   25.0    0.0    0.2  0.0  1.8    0.0   72.3   0  52 c3t60A98000572D4275684A563761586D71d0

    0.0  157.4    0.0   12.0  0.0 10.7    0.2   68.0   0  87 c3t60A98000572D4275684A5638364D644Ed0

    2.8  405.8    0.0  113.8  0.0 100.1    0.0  244.9   0 100 c3t60A98000572D4275684A56385468434Fd0

Thanks!

Deb

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.