Re: how could select id=xx so slow?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



yes the system seems overloaded, I am dealing with a simple "INSERT" but not sure if it is normal that it took more time than the explain estimated:


explain analyze INSERT INTO vote_content ( thing1_id, thing2_id, name, date) VALUES (1,1, E'1', '2012-07-12T12:34:29.926863+00:00'::timestamptz)

                                       QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Insert  (cost=0.00..0.01 rows=1 width=0) (actual time=79.610..79.610 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.058..0.060 rows=1 loops=1)
 Total runtime: 79.656 ms

it is a table with 50 million rows, so not sure if it is too large... I have attached the schema below:

  Column   |           Type           |                                     Modifiers                                      
-----------+--------------------------+------------------------------------------------------------------------------------
 rel_id    | bigint                   | not null default nextval('vote_content_rel_id_seq'::regclass)
 thing1_id | bigint                   | not null
 thing2_id | bigint                   | not null
 name      | character varying        | not null
 date      | timestamp with time zone | not null
Indexes:
    "vote_content_pkey" PRIMARY KEY, btree (rel_id)
    "vote_content_thing1_id_key" UNIQUE, btree (thing1_id, thing2_id, name)
    "idx_date_vote_content" btree (date)
    "idx_name_vote_content" btree (name)
    "idx_thing1_id_vote_content" btree (thing1_id)
    "idx_thing1_name_date_vote_content" btree (thing1_id, name, date)
    "idx_thing2_id_vote_content" btree (thing2_id)
    "idx_thing2_name_date_vote_content" btree (thing2_id, name, date)

besides, it not the rush hour, so the disk IO is not the problem currently(I think):
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    44.50    9.50   21.50    76.00   264.00    21.94     0.16    5.10   12.42    1.86   4.39  13.60
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00



On Thu, Jul 12, 2012 at 2:56 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote:
On 07/12/2012 01:10 PM, Yan Chunlu wrote:
after check out the wiki page Maciek mentioned, turns out that heavy connection also burden the disk hardly.
looks like I am in the vicious circle:
1, slow query cause connection blocked so the client request more connection. 
2, more connection cause high disk io and make even the simplest query slow and block.

While true, you can often control this by making sure you don't completely overload your hardware, queuing queries instead of running them all at once.

You may still discover that your hardware can't cope with the workload in that your queues may just keep on getting deeper or time out. In that case, you certainly need to optimise your queries, tune your database, and/or get bigger hardware.

--
Craig Ringer


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux