Questions[sphinx](server)

I ran into a problem whereby our Sphinx SQL queries are getting truncated. This was due to the default limit for the Group Concat function in MySQL being 1024 bytes.

I used the following in my Sphinx.conf to fix this issue:

sql_query_pre = SET SESSION group_concat_max_len = 10000

This fixed the issue on my local machine. However, I have updated the sphinx.conf on our staging server and ran the command:

indexer --config sphinx.staging.conf --all --rotate

to update the indexes but the group concat part of the query is still being truncated at 1024 bytes.

Sphinx version: 2.2.10 Server version: CentOS Linux release 7.2.1511 Mysql: libmysql - 5.5.47-MariaDB (local version: libmysql - mysqlnd 5.0.10)

I'm not particularly experienced with Sphinx so may have missed some obvious step. Also, we do not want to change the default limits in our MySql config as these provide some protection in other situations.

Actually i am trying to start my Kaltura service. The default "sphinx" came along together with Kaltura has crushed and then got uninstalled. Then i installed clean dedicated of "Sphinx" (source version from here) outside of Kaltura. I setup at /opt/sphinx/. Then when after setup .. i run:

~ # sudo /opt/kaltura/bin/sphinx/searchd

It is showing like:

Sphinx 2.0.2-dev (r2823)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/usr/local/etc/sphinx.conf'...
listening on all interfaces, port=9312
listening on all interfaces, port=9306
precaching index 'test1'
WARNING: index 'test1': preload: failed to open /var/data/test1.sph: No such file or directory; NOT SERVING
precaching index 'test1stemmed'
WARNING: index 'test1stemmed': preload: failed to open /var/data/test1stemmed.sph: No such file or directory; NOT SERVING
WARNING: multiple addresses found for 'localhost', using the first one (ip=127.0.0.1)
precaching index 'rt'
WARNING: last message repeated 1 times
WARNING: index 'rt': preload: failed to open /var/data/rt.lock: No such file or directory; NOT SERVING
precached 3 indexes in 0.000 sec

What are these means? I'm not really getting why.

Possible Duplicate:
Can you help me with my capacity planning?

I have written an application that uses MySQL & Sphinx for searching and storing information.

the application contains a database that weights 300GB (mostly text, mediumtext and varchars). my sphinx index weights 30GB.

my problem stated when I was planning to have a 100GB~ DB and I now have a 300GB one, which can't be loaded into memory(?).

I'm looking for the best architecture that will give me the highest performance.

my application first gets a query, then runs it against the sphinx index, gets a bulk of IDs from sphinx, queries the DB for several text field for those IDs and sends the results to a .NET ranker (special algorithm).

In order to get the best performance I used a Xeon i7 W3520 server with 2 SSD disks (24GB ram) for MySQL, ain't cheap and I'm out of space and can't add more disks so I need another server.

Do you think there will be a major perfomance impact if I'll use a server with regular disks for mysql? I know that I need fast disks for the sphinx searching (this is my full text search engine), I'm using an indexed field (ID) to select rows from the DB without any complicated queries, I may need to host the DB on a cheaper server with enough storage.

can you spot my bottleneck? the .NET application needs mostly RAM and CPU, the DB needs a lot of space and or RAM and the sphinx needs fast disks and RAM.

1. is it possible to load the MySQL index to the RAM?
2. what can I do to mitigate the fact the DB's size if 300GB?
3. will a server with slower disks impact the performance by much? (I can't benchmark it without spending hundreds of dollars on hardware).
4. does it matter if I use a linux or windows machine for the MySQL?

Can't find a way to install sphinx. It's asking for libmysqlclient.so.15, which I have... What else can i do?

This is what I've tried already

[root@server1 ~]# rpm -ivh sphinx-2.0.3-1.el5.x86_64.rpm
error: Failed dependencies:
        libmysqlclient.so.15()(64bit) is needed by sphinx-2.0.3-1.rhel5.x86_64
        libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by sphinx-2.0.3-1.rhel5.x86_64

[root@server3 hooks]# ls -l /usr/lib64/libmysqlclient.so*
lrwxrwxrwx 1 root root      20 Jun 29 09:57 /usr/lib64/libmysqlclient.so -> libmysqlclient.so.18
-rwxr-xr-x 1 root root 1474856 Dec 24 01:37 /usr/lib64/libmysqlclient.so.15
lrwxrwxrwx 1 root root      24 Jun 29 09:57 /usr/lib64/libmysqlclient.so.18 -> libmysqlclient.so.18.0.0
-rwxr-xr-x 1 root root 6853576 May 11  2011 /usr/lib64/libmysqlclient.so.18.0.0
[root@server3 hooks]# ls -l /usr/lib/libmysqlclient.so*
lrwxrwxrwx 1 root root 28 Jan 12  2010 /usr/lib/libmysqlclient.so -> /usr/lib64/libmysqlclient.so
lrwxrwxrwx 1 root root 26 Dec 24 12:18 /usr/lib/libmysqlclient.so.15 -> /usr/lib/libmysqlclient.so
lrwxrwxrwx 1 root root 20 Dec 24 12:22 /usr/lib/libmysqlclient.so.18 -> libmysqlclient.so.15

I've got the latest version of sphinx installed. I have created the index with no problems and searchd starts up with no problems.

However, whenever I try to create a test table (straight from the docs I might add) mysql crashes.

I'm at wits end here.

Any ideas are appreciated.

G-Man

Here's the query:

CREATE TABLE t1 ( id INTEGER UNSIGNED NOT NULL, weight INTEGER NOT NULL, query VARCHAR(3072) NOT NULL, group_id INTEGER, INDEX(query) ) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test";