That error message looks like a MySQL issue, not a Sphinx issue - I'd be hunting on the MySQL site for that (and following its suggestion of repairing the tables/db).
-- Pat On 13/05/2010, at 2:51 PM, Gedeon wrote: > Hi Pat, > > I tried increasing the memory up to 512M, but it does not make it any > better. > > I can reproduce the error in phpmyadmin by running the query found in > the sphinx config file and changing the $start and $end id limits. > This is the query and error i get: > > Error > > SQL query: Documentation > > SELECT SQL_NO_CACHE `projects`.`id` *5 +4 AS `id` , `projects`.`name` > AS `name` , `projects`.`mission` AS `mission` , GROUP_CONCAT( DISTINCT > IFNULL( `writeboards`.`name` , '0' ) > SEPARATOR ' ' ) AS `writeboard_name` , GROUP_CONCAT( DISTINCT > IFNULL( `writeboard_contents`.`body` , '0' ) > SEPARATOR ' ' ) AS `mr_content_body` , GROUP_CONCAT( DISTINCT > IFNULL( `writeboard_comments`.`body` , '0' ) > SEPARATOR ' ' ) AS `mr_comment_body` , GROUP_CONCAT( DISTINCT > IFNULL( `folders`.`name` , '0' ) > SEPARATOR ' ' ) AS `folder_name` , GROUP_CONCAT( DISTINCT > IFNULL( `folders`.`description` , '0' ) > SEPARATOR ' ' ) AS `folder_description` , GROUP_CONCAT( DISTINCT > IFNULL( `project_documents`.`filename` , '0' ) > SEPARATOR ' ' ) AS `file_name` , GROUP_CONCAT( DISTINCT > IFNULL( `project_documents`.`description` , '0' ) > SEPARATOR ' ' ) AS `file_description` , `projects`.`id` AS > `sphinx_internal_id` , 3759073650 AS `class_crc` , '3759073650' AS > `subclass_crcs` , 0 AS `sphinx_deleted` , GROUP_CONCAT( DISTINCT > IFNULL( `memberships`.`user_id` , '0' ) > SEPARATOR ',' ) AS `member_ids` , IF( projects.private =0, '0', > GROUP_CONCAT( memberships.user_id ) ) AS `members_or_public` > FROM `projects` > LEFT OUTER JOIN `writeboards` ON writeboards.project_id = projects.id > LEFT OUTER JOIN `writeboard_contents` ON > writeboard_contents.writeboard_id = writeboards.id > LEFT OUTER JOIN `writeboard_contents` > writeboard_comments_writeboards_join ON ( `writeboards`.`id` = > `writeboard_comments_writeboards_join`.`writeboard_id` ) > LEFT OUTER JOIN `writeboard_comments` ON > ( `writeboard_comments`.`writeboard_content_id` = > `writeboard_comments_writeboards_join`.`id` ) > LEFT OUTER JOIN `folders` ON folders.project_id = projects.id > LEFT OUTER JOIN `project_documents` ON project_documents.folder_id = > folders.id > LEFT OUTER JOIN `memberships` ON memberships.project_id = projects.id > WHERE `projects`.`id` >=1 > AND `projects`.`id` <=10 > AND `projects`.`delta` =0 > AND projects.inactive =0 > GROUP BY `projects`.`id` > ORDER BY NULL > LIMIT 0 , 30 > > MySQL said: Documentation > #126 - Incorrect key file for table 'sql_730_0.MYI'; try to repair it > > > I will try to find out exactly what part of the query is doing that. > If you have any idea, please let me know.. > > Thanks, > > Gedeon > > > > On May 9, 11:51 am, Pat Allan <[email protected]> wrote: >> It's probably worth increasing the memory limit for indexer, via the >> mem_limit >> setting:http://freelancing-god.github.com/ts/en/advanced_config.html >> (see about halfway down the page) >> >> Not sure if that will fix things, but it's definitely worth a shot. >> >> -- >> Pat >> >> On 07/05/2010, at 2:31 PM, Gedeon wrote: >> >> >> >>> Hi all, >> >>> Thanks to Pat, I got the search working properly now. However, >>> indexing is now giving me a headache... >> >>> As explained in my previous threads, I'm trying to index and search >>> Projects. It worked fine in development, with a very small test >>> database. However, in production (with tables under 2k records), >>> indexing seems to take loads of disk space (which is released once >>> indexing is over). Some examples: >> >>> indexes writeboards.writeboard_comments.body, :as >>> => :mr_comment_body (1158 rows, 416kb) >>> indexes folders.project_documents.description, :as >>> => :file_description (1457 rows, 352kb)) >> >>> has memberships.user_id, :as => :member_ids, :type => :multi (652 >>> rows, 56kb) >>> has "IF(projects.private = 0, '0', >>> GROUP_CONCAT( memberships.user_id))", :as => :members_or_public, :type >>> => :multi >> >>> requires 1.4GB >>> ----------- >>> Now, the same without :members_or_public: >>> indexes writeboards.writeboard_comments.body, :as >>> => :mr_comment_body >>> indexes folders.project_documents.description, :as >>> => :file_description >> >>> has memberships.user_id, :as => :member_ids, :type => :multi >> >>> requires 1.1GB >>> ----------- >>> Without :member_ids : >>> indexes writeboards.writeboard_comments.body, :as => :mr_comment_body >>> indexes folders.project_documents.description, :as >>> => :file_description >> >>> 75MB >>> ------------ >> >>> Obviously, there is an issue with has memberships.user_id, :as >>> => :member_ids, :type => :multi >> >>> Also, without the :member_ids, adding indexes requires more and more >>> disk space: >>> indexes writeboards.name, :as => :writeboard_name >>> indexes writeboards.writeboard_contents.body, :as => :mr_content_body >>> indexes writeboards.writeboard_comments.body, :as => :mr_comment_body >>> indexes folders.project_documents.description, :as >>> => :file_description >>> indexes folders.project_documents.filename, :as => :file_name >>> indexes folders.description, :as => :folder_description >>> indexes folders.name, :as => :folder_name >> >>> requires 1GB of disk space for indexing. >> >>> Is that a normal behaviour? If not, has anyone encountered this >>> problem? Any idea what I should check to fix it? >>> All my tables are pretty small and "many indexes" + "member_ids" >>> always crashes because I do not have enough space on the hard disk. >>> Will I need 10Gb of disk space for indexing tables with 10k+ rows? >>> All this is without the use of wildcards, infix or prefix. Enabling >>> them does not seem to have any effect on the disk space required. >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "Thinking Sphinx" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]. >>> For more options, visit this group >>> athttp://groups.google.com/group/thinking-sphinx?hl=en. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Thinking Sphinx" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group >> athttp://groups.google.com/group/thinking-sphinx?hl=en. > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/thinking-sphinx?hl=en. > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
