hosted Enterprise Chef: Query Adjustments for /cookbooks/_recipes Endpoint

[sean-horn]

The knife recipe list command regularly returns HTTP 500 response codes to users on Hosted Chef. The command calls the following endpoint:

/organizations/ORGNAME/cookbooks/_recipes

Erchef console logs show the following stack trace for the 500:

=ERROR REPORT==== 3-Oct-2014::17:18:46 ===
{<<"method=GET; path=/organizations/jeremiah-opscode/cookbooks/_recipes; status=500; ">>,
 {error,
     {throw,
         {error,invalid_ejson},
         [{jiffy,encode,2,[{file,"src/jiffy.erl"},{line,34}]},
          {chef_json,encode,1,[{file,"src/chef_json.erl"},{line,41}]},
          {chef_wm_cookbooks,to_json,3,
              [{file,"src/chef_wm_cookbooks.erl"},{line,85}]},
          {webmachine_resource,resource_call,3,
              [{file,"src/webmachine_resource.erl"},{line,186}]},
          {webmachine_resource,do,3,
              [{file,"src/webmachine_resource.erl"},{line,142}]},
          {webmachine_decision_core,resource_call,1,
              [{file,"src/webmachine_decision_core.erl"},{line,48}]},
          {webmachine_decision_core,decision,1,
              [{file,"src/webmachine_decision_core.erl"},{line,558}]},
          {webmachine_decision_core,handle_request,2,
              [{file,"src/webmachine_decision_core.erl"},{line,33}]}]}}}

This same endpoint works against Opsmaster which is running EC 11. This error occurs even in organizations with no uploaded cookbooks.

As a note, the underlying sql request for this operation is incredibly slow:

                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=641401.80..641401.81 rows=1 width=547) (actual time=5792.717..5792.717 rows=1 loops=1)
   ->  Sort  (cost=641401.80..641401.81 rows=1 width=547) (actual time=5792.717..5792.717 rows=1 loops=1)
         Sort Key: cookbook_versions_by_rank.name
         Sort Method: quicksort  Memory: 26kB
         ->  Subquery Scan on cookbook_versions_by_rank  (cost=591894.61..641401.79 rows=1 width=547) (actual time=3989.823..5792.689 rows=2 loops=1)
               Filter: ((cookbook_versions_by_rank.org_id = 'a62cf91ce3e54ce586838956dd54eab2'::bpchar) AND (cookbook_versions_by_rank.rank = 1))
               Rows Removed by Filter: 800203
               ->  WindowAgg  (cost=591894.61..629977.06 rows=761649 width=608) (actual time=3986.977..5693.600 rows=800205 loops=1)
                     ->  Sort  (cost=591894.61..593798.73 rows=761649 width=608) (actual time=3986.946..4505.346 rows=800205 loops=1)
                           Sort Key: v.cookbook_id, v.major, v.minor, v.patch
                           Sort Method: external merge  Disk: 483416kB
                           ->  Hash Join  (cost=22338.35..311822.70 rows=761649 width=608) (actual time=254.845..2682.368 rows=800205 loops=1)
                                 Hash Cond: (v.cookbook_id = c.id)
                                 ->  Seq Scan on cookbook_versions v  (cost=0.00..161855.49 rows=761649 width=567) (actual time=0.002..875.538 rows=800205 loops=1)
                                 ->  Hash  (cost=12126.60..12126.60 rows=479660 width=45) (actual time=254.663..254.663 rows=489068 loops=1)
                                       Buckets: 16384  Batches: 8  Memory Usage: 4780kB
                                       ->  Seq Scan on cookbooks c  (cost=0.00..12126.60 rows=479660 width=45) (actual time=0.003..114.830 rows=489068 loops=1)
 Total runtime: 5887.263 ms
(18 rows)

[sean-horn]

Closed in favor of https://github.com/opscode/server-team/issues/2

[sean-horn]

Reopened with updated root cause

[gauravsj]

Is this bug fixed as I am still getting error 500 when using knife recipe list command