Re: hight cpu %sy usage

[Jeff Janes <jeff.janes@xxxxxxxxx>]

On Mon, Feb 27, 2017 at 6:13 AM, dbyzaa@xxxxxxx <dbyzaa@xxxxxxx> wrote:

hello everyone,

 

i have PostgreSQL 9.5.3 server running on redhalt 6.6

when i run one query  with pgbench the cpu is 80% and sy% is 60%

Why is this a problem?  If you run the query as fast as you can, all of the time spent running the query has to go somewhere.  Is there something inherently wrong with much of it going to sy rather than something else?

Can you show us the pgbench command you used?  -c, -j, etc., 

1.the query runing is 2.7ms

Where is that from?  It contradicts the information from the "explain analyze".

 

i drop the btree index  table_name_1_user_id_idx 

create index table_name_1_user_id_idx  on talbe_name_1 using hash(user_id);

vacuum analyze table_name_1

then the cpu is normal !!!!

What happened to the TPS?  It is easy to shift load from sy to us if you do it by making things slower overall by bloating the time spent in user space.  I suspect that that is what happened.

Cheers,

Jeff