MySQL sort_buffer_size problem

[bistline]

I'm having an issue with large indices and sort_buffer_size. I work in a controlled distributed environment where I don't have the option of setting this configuration parameter for MySQL on a shared server, and I'm unable to index my database because it keeps running out of memory. Is there a way to pass this as a parameter when trying to run rake ts:index in a production environment? I tried setting the mem_limit option in thinking_sphinx.yml and it had no effect. I got around the issue in development because I can up the limit on my machine, but the production MySQL server is shared amongst multiple groups and the DBAs refused to up the limit for performance reasons.

I'm using MySQL 5.6 and InnoDB.

[pat]

You could try setting sort_buffer_size as part of the indexing process via config/thinking_sphinx.yml:

production:
  sql_query_pre:
    - SET sort_buffer_size=10000

sql_query_pre statements get run before the main indexing query, so they can be used to customise the database's session.

Also, feel free to share your index definitions here, perhaps there's other alterations for some performance improvements.

[pat]

Closing this thread due to inactivity. Can re-open if needed, though :)

[madhuri033]

I have added a new field "hash_tags" in indexes which is the type of text

ThinkingSphinx::Index.define :post, :with => :active_record, :delta => true do
  indexes description, as: 'post_description', sortable: true
  indexes hash_tags, as: 'post_hash_tags'
  indexes name, as: 'post_name', sortable: true
  indexes id, as: 'post_id'
  indexes store_addition.name, as: 'store_name'
  indexes items.description, as: 'items_desc'

  has created_at
  has original_post_time
  has id, :as => :post_ids
  has categories.id, as: 'categories_ids'
  has post_type_id, as: 'post_type_ids'
  has store_addition_id, as: 'store_addition_ids'
end

on running rake ts:rebuild I am getting error ERROR: index 'post_core': sql_fetch_row: Out of sort memory, consider increasing server sort buffer size.
Above solution is not working for me.

[pat]

@madhuri033 are you using MySQL or PostgreSQL as your database? If you're using MySQL, it's worth noting that the sort_buffer_size setting may need to be increased at the database configuration level itself (e.g. the my.cnf file) to ensure the available upper limit is high enough (I'm guessing connection-level settings can only obtain whatever the server maximum is).

However, getting too carried away with modifying sort_buffer_size is not recommended:
https://www.xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size/

I would recommend instead removing the sortable: true flag from fields that do not need it - and in particular, I'm guessing the post_description field perhaps doesn't need to be sorted in Sphinx queries? If it's a text column (rather than varchar/length-limited string), then sorting certainly doesn't feel appropriate.