Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is around 2 seconds.

Application is hosted on heroku and I'm using Crane database (https://addons.heroku.com/heroku-postgresql) with 410MB of RAM.

Is there any way to speed this up? I'm querying for videos 50 times per second on average, and new videos are inserted/updated at rate of 50 per second.

explain analyze SELECT * FROM videos WHERE id = 17841464 LIMIT 1;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.892..2337.894 rows=1 loops=1)
   ->  Index Scan using videos_pkey on videos  (cost=0.00..6.43 rows=1 width=119) (actual time=2337.888..2337.888 rows=1 loops=1)
         Index Cond: (id = 17841464)
 Total runtime: 2337.943 ms

Here is how the table looks like:

 \d+ videos;
                                                           Table "public.videos"
     Column     |            Type             |                      Modifiers                      | Storage  | Stats target | Description 
----------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
 id             | integer                     | not null default nextval('videos_id_seq'::regclass) | plain    |              | 
 uuid           | character(11)               | not null                                            | extended |              | 
 channel_id     | integer                     | not null                                            | plain    |              | 
 category_id    | integer                     |                                                     | plain    |              | 
 title          | character varying(255)      |                                                     | extended |              | 
 published_at   | timestamp without time zone |                                                     | plain    |              | 
 view_count     | bigint                      |                                                     | plain    |              | 
 like_count     | integer                     |                                                     | plain    |              | 
 dislike_count  | integer                     |                                                     | plain    |              | 
 favorite_count | integer                     |                                                     | plain    |              | 
 comment_count  | integer                     |                                                     | plain    |              | 
 disabled       | boolean                     | default false                                       | plain    |              | 
 created_at     | timestamp without time zone |                                                     | plain    |              | 
 updated_at     | timestamp without time zone |                                                     | plain    |              | 
Indexes:
    "videos_pkey" PRIMARY KEY, btree (id)
    "videos_uuid_idx" UNIQUE, btree (uuid)
    "videos_latest_by_channel_idx" btree (channel_id, published_at DESC)
    "videos_top_by_channel_idx" btree (channel_id, view_count DESC)
Has OIDs: no
share|improve this question
2  
An index scan shouldn't take that long. That sounds like a hardware problem. (Btw: there is need to specify LIMIT 1 when you restrict the select to a single PK value) –  a_horse_with_no_name Sep 11 '13 at 9:55
    
It is also possible that your index or the table is bloated. What does \di+ videos_pkey show as size? –  dezso Sep 11 '13 at 10:10
    
@dezso could that happen due to many updates of the "count" columns? And would it make sense to move those columns into a separate table? –  ypercube Sep 11 '13 at 10:37
    
@ypercube Yes, as the other columns look rather static, this can be a good idea. Probably the counts table could be moved to a smaller and faster disk as well. And I find the view_count bigint a rather optimistic type choice :) –  dezso Sep 11 '13 at 11:12
2  
@a_horse_with_no_name .. probably meant to write no need for LIMIT 1. –  Erwin Brandstetter Sep 11 '13 at 11:39

1 Answer 1

up vote 1 down vote accepted

Honestly I have never seen a single value btree lookup take this long. I don't think your problem is simply in your query. I think it is elsewhere.

First you say you have a lot of writes. This probably means you are constantly pushing a lot of stuff out of the buffers and doing a lot of random disk I/O. I would not be surprised if a lot of the bottleneck is in the general I/O and your lack of RAM.

For 18 million rows, these don't look very large. They may all fit in memory.

At any rate the first thing to do if had access to the command line on the server would be to take CPU I/O wait vs user vs system time (you may be able to do that elsewhere in your code). Also run explain with (ANALYSE, BUFFERS, VERBOSE) set so that you can see much more about what is going on. These should give you an idea if your problem is underwhelming CPU activity, slow RAM, or lots of disk I/O but as it is, there is very little that can be done.

Another thing you can do is this:

VACUUM ANALYSE VERBOSE;

See if that speeds things up.

share|improve this answer
    
Thanks for the explanation. It turned out to be the disk I/O issue (due to millions reads/writes) and insufficient RAM. The other issue was the load itself (tens of concurrent connections with hundreds of read/write queries per second). I decited to migrate the "big hot part" of the database to DynamoDB and leave everything else. As a result, everything went back to normal with queries faster up to 50 times. Thanks for your help –  Milovan Zogovic Sep 14 '13 at 6:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.