[SOLVED] 100% CPU with postgreSQL ! Help me
  • Vote Up0Vote Down YannickYannick
    Posts: 101Member
    Hi,

    I prepare my Airtime server for producing on September 22. I added several shows (see attachment) from 8h to 22h. Since I made ​​this adds, access to my library has become slow, even the home page that displays nothing yet has been slow to appear. 

    Here's my little setup : 

    CPU : AMD Athlon (tm) 64 X2 Dual Core Processor 3800 + 
    Memory : 2GB 

    Is my server is not powerful enough? Do I have too many shows? 

    Is there way to optimize the database? (only 55Mb) 

    Or see requests that cause problems? 

    Thank you

    Google translate is my friend ;)
    Post edited by Yannick at 2014-09-09 14:54:26
  • 3 Comments sorted by
  • Vote Up0Vote Down YannickYannick
    Posts: 101Member
    The query seems be : 

    UPDATE cc_files SET is_scheduled = false WHERE is_scheduled = true AND id NOT IN ( SELECT s.file_id FROM cc_schedule AS s LEFT JOIN cc_show_instances AS i ON s.instance_id = i.id WHERE s.ends > now() at time zone 'UTC'AND i.ends > now() at time zone 'UTC' );

    Whenever I run this query the process up to 99%, why? 

    If I make a SELECT * FROM cc_files WHERE is_scheduled = true,  the value of is_scheduled is always "true", why after a update ??

    Thanks
  • Vote Up0Vote Down YannickYannick
    Posts: 101Member
    Now result from auto_explain here : http://pastebin.com/bhipjiB1
  • Vote Up0Vote Down YannickYannick
    Posts: 101Member
    Well I solved my problem! I created Smart Blocks for a period of 24 hours which I attributed to the shows of one hour. There were 23 hours of excess and the query (see above) should take too long. 

    So I created two hours blocks, empty all my shows to reallocate smarty blocks. 

    Everything is back in order!
Add a Comment