Thread: Increasing CPU usage of PostgreSQL

Increasing CPU usage of PostgreSQL

From
Rahila Syed
Date:
Hello All,

I am using jdbcrunner-1.2 to run PostgreSQL performance tests. For
certain tests, i need to increase the CPU usage of the servers
especially at user level.
I tried using both tpcc and tpcb load with scale factor of 100. Even
after setting the number of client connections as high as 420, I am
unable to achieve high CPU usage. It is hardly 3 percent. I think this
is because most of the client connections are idle.
The tpcc and tpcb transactions seem to be consuming very less CPU.

In postgresql.conf file, I have lowered checkpoint segments to 1 in
order to overwhelm the server with checkpoints but no significant
increase in iowait of the CPU.

Can somebody suggest a better idea to load PostgreSQL servers to
increase CPU usage.?

Configurations of my machine is:

Processors:   Xeon E5-2650 Processor Kit
                      Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T,
20 MB) * 2 nos


RAM :  32GB DDR3-1600 REG Memory Kit
            8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800)

HDD:  450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos
             1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s

Disk Speed  : 10,000 RPM

RAID Controller (512MB, RAID 0/1)


Thank you,


Re: Increasing CPU usage of PostgreSQL

From
Scott Marlowe
Date:
On Fri, Oct 25, 2013 at 8:29 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:
> Hello All,
>
> I am using jdbcrunner-1.2 to run PostgreSQL performance tests. For
> certain tests, i need to increase the CPU usage of the servers
> especially at user level.
> I tried using both tpcc and tpcb load with scale factor of 100. Even
> after setting the number of client connections as high as 420, I am
> unable to achieve high CPU usage. It is hardly 3 percent. I think this
> is because most of the client connections are idle.
> The tpcc and tpcb transactions seem to be consuming very less CPU.
>
> In postgresql.conf file, I have lowered checkpoint segments to 1 in
> order to overwhelm the server with checkpoints but no significant
> increase in iowait of the CPU.
>
> Can somebody suggest a better idea to load PostgreSQL servers to
> increase CPU usage.?
>
> Configurations of my machine is:
>
> Processors:   Xeon E5-2650 Processor Kit
>                       Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T,
> 20 MB) * 2 nos
>
>
> RAM :  32GB DDR3-1600 REG Memory Kit
>             8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800)
>
> HDD:  450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos
>              1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s
>
> Disk Speed  : 10,000 RPM
>
> RAID Controller (512MB, RAID 0/1)

My guess is that you're maxing out your IO subsystem long before
you're maxing out CPU. What does

iostat -xd 10

have to say about it?

To understand recursion, one must first understand recursion.


Re: Increasing CPU usage of PostgreSQL

From
Tomas Vondra
Date:
On 25.10.2013 19:04, Scott Marlowe wrote:
> On Fri, Oct 25, 2013 at 8:29 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:
>>
>> Configurations of my machine is:
>>
>> Processors:   Xeon E5-2650 Processor Kit
>>                       Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T,
>> 20 MB) * 2 nos
>>
>>
>> RAM :  32GB DDR3-1600 REG Memory Kit
>>             8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800)
>>
>> HDD:  450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos
>>              1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s
>>
>> Disk Speed  : 10,000 RPM
>>
>> RAID Controller (512MB, RAID 0/1)
>
> My guess is that you're maxing out your IO subsystem long before
> you're maxing out CPU. What does
>
> iostat -xd 10
>
> have to say about it?

Right, that's my guess too. The problem is most likely the "sync" at the
end of the transaction.

Rahila, if you want to saturate the CPU and don't care about the
particular benchmark, try to use read-only transactions. Either just add
"-S" at the pgbench command line, or write something SELECT-only on your
own. Anyway, use '-j' in such cases.

Tomas


Increasing CPU usage of PostgreSQL

From
Rahila Syed
Date:
>Rahila, if you want to saturate the CPU and don't care about the
> particular benchmark, try to use read-only transactions. Either just add
> "-S" at the pgbench command line, or write something SELECT-only on your
> own. Anyway, use '-j' in such cases.


Thanks a lot for your suggestion. Using select only queries helped me
lower % CPU time spent on IO. Also , increasing number of threads
helped loading CPU around 87 to 90 %.

-Rahila


On Sat, Oct 26, 2013 at 7:53 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 25.10.2013 19:04, Scott Marlowe wrote:
>> On Fri, Oct 25, 2013 at 8:29 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:
>>>
>>> Configurations of my machine is:
>>>
>>> Processors:   Xeon E5-2650 Processor Kit
>>>                       Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T,
>>> 20 MB) * 2 nos
>>>
>>>
>>> RAM :  32GB DDR3-1600 REG Memory Kit
>>>             8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800)
>>>
>>> HDD:  450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos
>>>              1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s
>>>
>>> Disk Speed  : 10,000 RPM
>>>
>>> RAID Controller (512MB, RAID 0/1)
>>
>> My guess is that you're maxing out your IO subsystem long before
>> you're maxing out CPU. What does
>>
>> iostat -xd 10
>>
>> have to say about it?
>
> Right, that's my guess too. The problem is most likely the "sync" at the
> end of the transaction.
>
> Rahila, if you want to saturate the CPU and don't care about the
> particular benchmark, try to use read-only transactions. Either just add
> "-S" at the pgbench command line, or write something SELECT-only on your
> own. Anyway, use '-j' in such cases.
>
> Tomas
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Increasing CPU usage of PostgreSQL

From
Rahila Syed
Date:
> iostat -xd 10
>
> have to say about it?

Yes i was indeed maxing out my IO subsystem.  % CPU utilization for io
is around 90 percent. I could not notice this earlier because i was
firing CPU load queries from a remote machine hence network overhead
was coming into picture  .Thanks a lot for your suggestion.

-Rahila

On Fri, Oct 25, 2013 at 10:34 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Fri, Oct 25, 2013 at 8:29 AM, Rahila Syed <rahilasyed90@gmail.com> wrote:
>> Hello All,
>>
>> I am using jdbcrunner-1.2 to run PostgreSQL performance tests. For
>> certain tests, i need to increase the CPU usage of the servers
>> especially at user level.
>> I tried using both tpcc and tpcb load with scale factor of 100. Even
>> after setting the number of client connections as high as 420, I am
>> unable to achieve high CPU usage. It is hardly 3 percent. I think this
>> is because most of the client connections are idle.
>> The tpcc and tpcb transactions seem to be consuming very less CPU.
>>
>> In postgresql.conf file, I have lowered checkpoint segments to 1 in
>> order to overwhelm the server with checkpoints but no significant
>> increase in iowait of the CPU.
>>
>> Can somebody suggest a better idea to load PostgreSQL servers to
>> increase CPU usage.?
>>
>> Configurations of my machine is:
>>
>> Processors:   Xeon E5-2650 Processor Kit
>>                       Intel® Xeon ® Processor E5-2650 (2 GHz, 8C/16T,
>> 20 MB) * 2 nos
>>
>>
>> RAM :  32GB DDR3-1600 REG Memory Kit
>>             8x 4GB Registered ECC DIMM, DDR3L-1600(PC3L-12800)
>>
>> HDD:  450GB 10K Hot Plug 2.5-inch SAS HDD * 8 nos
>>              1 x 450 GB SAS HDD, 2.5-inch, 6Gb/s
>>
>> Disk Speed  : 10,000 RPM
>>
>> RAID Controller (512MB, RAID 0/1)
>
> My guess is that you're maxing out your IO subsystem long before
> you're maxing out CPU. What does
>
> iostat -xd 10
>
> have to say about it?
>
> To understand recursion, one must first understand recursion.