[HOWTO] Install Sphinx for Adult Script Pro

symtab · 2011-02-13 14:29:58

Hi,

Here's a small howto on installing Sphinx Search (http://sphinxsearch.com/) and configuring it to work with Adult Script Pro. By using sphinx
you will be able to allow users to search for video on your adult tube site by title, description and tags. The search results will be automatically
ordered by relevance (its almost the same as using mysql MATCH_AGAINST, but faster and it can support even 1.000.000 videos).

You will need ssh root access. The installation itself is quite easy, depending on your distribution:

Ubuntu (tested on Ubuntu 10.10):

sudo apt-get update
sudo apt-get install sphinxsearch

CentOs (tested on 5.4):

yum install sphinx

Configuring sphinx si also straight forward, all you need to do is edit the sphinx configuration file and add:

source videos
{
        type        = mysql
    sql_host    = localhost
    sql_user    = mysql-username
    sql_pass    = mysql-password
    sql_db      = mysql-database
    sql_sock    = /var/lib/mysqld/mysqld.sock (this depends on your server)
    sql_port    = 3306

    sql_query_pre       = SET NAMES utf8
    sql_query           = \
        SELECT v.video_id, v.title, v.description, v.mobile, v.add_time, \
         v.rating, v.total_views, v.duration, v.video_id AS video, \ #added in RC4
         GROUP_CONCAT(DISTINCT t.name SEPARATOR ' ') AS tags \
        FROM video AS v \
        LEFT JOIN video_tags AS t ON (t.video_id = v.video_id) \
        WHERE v.status = 1 \
        GROUP BY v.video_id

        sql_attr_uint           = video #added in RC4
        sql_attr_uint           = mobile
        sql_attr_uint           = total_views
        sql_attr_float          = duration
        sql_attr_float          = rating
        sql_attr_timestamp  = add_time
        sql_attr_multi          = uint categories FROM query; SELECT video_id, cat_id FROM video_category
        sql_query_info          = SELECT video_id, add_date FROM video WHERE video_id=$id
}

index videos
{
    source          = videos
    path            = /var/lib/sphinxsearch/data/videos (this depends on your distribution)
        charset_type    = utf-8
        min_prefix_len  = 3
        enable_star     = 1
}

searchd
{
        port            = 3312
        log                     = /var/log/sphinxsearch/searchd.log (this depends on your distribution)
        query_log       = /var/log/sphinxsearch/query.log (this depends on your distribution)
        pid_file        = /var/log/sphinxsearch/searchd.pid (this depends on your distribution)
}

The sphinx data and log folders might depend on your distribution.

After this you need to create the initial index with the following command:

indexer --all --config /path/to/your/sphinx/configuration/file.conf

You will also need to create a cron file and place it into the /etc/cron.hourly (or add it to cron from cpanel or console). The script
should be named sphinx and chmoded to 755, with the following contents:

#!/bin/bash
/usr/bin/indexer --all --rotate --config /path/to/your/sphinx/configuration/file.conf

Thats it :-)

If you dont have the time do the installation yourself, or you dont like the linux console, just drop us a email and we can do the installation
for 49$.

symtab · 2011-02-13 14:33:37

Adult Script Pro can work without Sphinx also (so its not a requirement), however the search is done only by video tags. Here's why
we dont search by default via title, description, tags from mysql directly:

* mysql LIKE is very slow (even if you use LIKE 'string%' and it uses the key, it will be slow once you have > 10.000 rows and the search results are incomplete)
* mysql MATCH_AGAINST is very good from the results point of view, however it will be very slow once you have > 10.000 rows)

UPDATE: we have changed to MATCH_AGAINST. Busy sites can use sphinx if the related videos code uses to much CPU.

marshalbros · 2011-05-05 14:25:13

What did I do wrong. I get this error from indexer --all --config /etc/sphinx/sphinx.conf

using config file '/etc/sphinx/sphinx.conf'...
indexing index 'videos'...
ERROR: index 'videos': source 'videos' not found.
ERROR: index 'videos': no valid sources configured; skipping.
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg

thiva7 · 2011-05-05 14:27:28

you can explane what is that doinf on server?? get videos, works faster script ??? what ???

symtab · 2011-05-05 14:56:32

@marsalbros: source videos is not in the file (eg: source videos { ... })

@thiva7: it allows you to do complex searches and it works very fast. Basically in mysql, if you have like 100.000 rows and you want to do a search, it will be slow (even if you use keys, once you have a lot of visitors, it will be slow).

marshalbros · 2011-05-05 16:10:21

Fixed my previous errors, now I have this with the cron

/usr/bin/indexer --all --rotate --config /etc/sphinx/sphinx.conf
Sphinx 0.9.9-release (r2117)
Copyright (c) 2001-2009, Andrew Aksyonoff

using config file '/etc/sphinx/sphinx.conf'...
indexing index 'videos'...
collected 8486 docs, 2.8 MB
collected 8664 attr values
sorted 0.0 Mvalues, 100.0% done
sorted 1.9 Mhits, 100.0% done
total 8486 docs, 2838950 bytes
total 0.915 sec, 3102460 bytes/sec, 9273.66 docs/sec
total 4 reads, 0.009 sec, 2432.1 kb/call avg, 2.4 msec/call avg
total 31 writes, 0.042 sec, 770.1 kb/call avg, 1.3 msec/call avg
WARNING: failed to open pid_file '/var/log/sphinx/searchd.pid'.
WARNING: indices NOT rotated.

so I tried
touch /var/log/sphinx/searchd.pid
chmod 777 /var/log/sphinx/searchd.pid
then again indexer --all --config /etc/sphinx/sphinx.conf
and /usr/bin/indexer --all --rotate --config /etc/sphinx/sphinx.conf

error is
WARNING: failed to scanf pid from pid_file '/var/log/sphinx/searchd.pid'.
WARNING: indices NOT rotated.

marshalbros · 2011-05-05 16:27:01

Found the problem, searchd wasnt running.

New question. Does this search integrate into the script with no other modifications. Install and configure sphinx, set the cron, then nothing else to do?

symtab · 2011-05-05 16:27:48

Thats not good. Do a ps ax and see under which user sphinx runs, once you got it stop searchd and then chown usersnameforsearchd.usernameforsearchd /var/run/sphinx. Try to start searchd after this. It should work (the pid file is needed for the indexer to send a -HUP to searchd).

lude · 2011-05-07 08:37:25

can you install the sphinx for me? i will pay for it

symtab · 2011-05-07 08:54:04

Yeah. Just send me ssh root access via email.

marshalbros · 2011-05-15 13:31:47

Can this be used for two installs on the same server?

symtab · 2011-05-15 14:00:27

Yeah. It can be used for as many installs as you want, but you have to configure more than 1 indexes/sources in sphinx and make a few small mods to the video search and video related page.

asko · 2011-06-01 17:14:35

My host install service has asked me to ask you if you can provide a working configuration file(that works with database) so they can complete installation. That's just what they asked me.

Last edited by asko (2011-06-01 17:18:52)

Coscast · 2011-06-01 17:25:18

This is what they want:

source videos
{
        type        = mysql
    sql_host    = localhost
    sql_user    = mysql-username
    sql_pass    = mysql-password
    sql_db      = mysql-database
    sql_sock    = /var/lib/mysqld/mysqld.sock (this depends on your server)
    sql_port    = 3306

    sql_query_pre       = SET NAMES utf8
    sql_query           = \
        SELECT v.video_id, v.title, v.description, v.mobile, v.add_time, \
         v.rating, v.total_views, v.duration, \
         GROUP_CONCAT(DISTINCT t.name SEPARATOR ' ') AS tags \
        FROM video AS v \
        LEFT JOIN video_tags AS t ON (t.video_id = v.video_id) \
        AND v.status = 1 \
        GROUP BY v.video_id

        sql_attr_uint           = mobile
        sql_attr_uint           = total_views
        sql_attr_float          = duration
        sql_attr_float          = rating
        sql_attr_timestamp  = add_time
        sql_attr_multi          = uint categories FROM query; SELECT video_id, cat_id FROM video_category
        sql_query_info          = SELECT video_id, add_date FROM video WHERE video_id=$id
}

index videos
{
    source          = videos
    path            = /var/lib/sphinxsearch/data/videos (this depends on your distribution)
        charset_type    = utf-8
        min_prefix_len  = 3
        enable_star     = 1
}

searchd
{
        port            = 3312
        log                     = /var/log/sphinxsearch/searchd.log (this depends on your distribution)
        query_log       = /var/log/sphinxsearch/query.log (this depends on your distribution)
        pid_file        = /var/log/sphinxsearch/searchd.pid (this depends on your distribution)
}

This works 100%, did an install today.

asko · 2011-06-01 18:46:20

Thank you, very nice of you, but I think they already have that.
Are you a adultscriptpro programmer? Because didn't you just copy/paste the above instructions, maybe symtab will check my question.

Last edited by asko (2011-06-01 18:53:05)

Coscast · 2011-06-01 18:49:54

Yes i copied this. And this is what they need as sphinx.conf. Nothing more nothing less. This config pulls out the needed Data from your ASP SQL-DB.

There is nothing else to "edit" for sphinx to work with ASP.

Last edited by Coscast (2011-06-01 18:50:44)

Coscast · 2011-06-02 02:36:33

Uhm,

So basically your provider says that he does not know which databases are yours ?

Who if not him knows what databases are yours ?

symtab · 2011-06-02 07:23:23

You dont have the correct mysql database name is sphinx.conf.

Coscast · 2011-06-02 20:07:51

No that is not normal.
I did a fresh install yesterday and this table is called "adv", in my database

asko · 2011-06-06 18:37:45

How can i check if sphinx is fully functional to verify if i installed correctly?
Is the search supposed to return result for every single word in titles?

So far i see the search is returning all words with minimum of 5 letters. It will not return any searches under 5 letters.
Words 4 letters and less such as: hot, bdsm, tits, cute, blow are not being returned
???????????????????????????????????????????????????????????????????????

Last edited by asko (2011-06-06 19:08:58)

Coscast · 2011-06-06 19:41:19

It should return search results for three letter words - mine does.
Did you create the initial index and set the search to sphinx in acp ?

Searchd listens on port 3312, you can check that via ssh console.

netstat -npl | grep 3312

asko · 2011-06-06 23:11:31

I do not understand:
"Did you create the initial index and set the search to sphinx in acp ?"
Do you mean my custom index page is using javascript to send query to default search?

Maybe search is only returning 5+ letter words as script default? because sphinx is not working at all?

I even tried changing the section:
min_prefix_len = 3-------what does that do anyways?

Last edited by asko (2011-06-06 23:23:24)

Coscast · 2011-06-06 23:24:19

First you have to create the inital index by typing:

indexer --all --config /path/to/your/sphinx/configuration/file.conf

then you have to go to your admin panel in AdultScriptPro and set your search to "Complex (Sphinx Search )"

additionaly the searchd daemon should be up and running. You can check that by typing the command i posted above.

asko · 2011-06-07 04:19:42

I set the search options to sphinx(complex) admin and now i get 0 results for all searches

symtab · 2011-06-07 08:03:07

You did not configure sphinx correctly and thats why the search does not return any results. If you could get me ssh root access i can install and configure sphinx for you and make sure it works perfectly.

Adult Script Pro Community Forums

#1 2011-02-13 14:29:58

[HOWTO] Install Sphinx for Adult Script Pro

#2 2011-02-13 14:33:37

Re: [HOWTO] Install Sphinx for Adult Script Pro

#3 2011-05-05 14:25:13

Re: [HOWTO] Install Sphinx for Adult Script Pro

#4 2011-05-05 14:27:28

Re: [HOWTO] Install Sphinx for Adult Script Pro

#5 2011-05-05 14:56:32

Re: [HOWTO] Install Sphinx for Adult Script Pro

#6 2011-05-05 16:10:21

Re: [HOWTO] Install Sphinx for Adult Script Pro

#7 2011-05-05 16:27:01

Re: [HOWTO] Install Sphinx for Adult Script Pro

#8 2011-05-05 16:27:48

Re: [HOWTO] Install Sphinx for Adult Script Pro

#9 2011-05-07 08:37:25

Re: [HOWTO] Install Sphinx for Adult Script Pro

#10 2011-05-07 08:54:04

Re: [HOWTO] Install Sphinx for Adult Script Pro

#11 2011-05-15 13:31:47

Re: [HOWTO] Install Sphinx for Adult Script Pro

#12 2011-05-15 14:00:27

Re: [HOWTO] Install Sphinx for Adult Script Pro

#13 2011-06-01 17:14:35

Re: [HOWTO] Install Sphinx for Adult Script Pro

#14 2011-06-01 17:25:18

Re: [HOWTO] Install Sphinx for Adult Script Pro

#15 2011-06-01 18:46:20

Re: [HOWTO] Install Sphinx for Adult Script Pro

#16 2011-06-01 18:49:54

Re: [HOWTO] Install Sphinx for Adult Script Pro

#17 2011-06-02 02:36:33

Re: [HOWTO] Install Sphinx for Adult Script Pro

#18 2011-06-02 07:23:23

Re: [HOWTO] Install Sphinx for Adult Script Pro

#19 2011-06-02 20:07:51

Re: [HOWTO] Install Sphinx for Adult Script Pro

#20 2011-06-06 18:37:45

Re: [HOWTO] Install Sphinx for Adult Script Pro

#21 2011-06-06 19:41:19

Re: [HOWTO] Install Sphinx for Adult Script Pro

#22 2011-06-06 23:11:31

Re: [HOWTO] Install Sphinx for Adult Script Pro

#23 2011-06-06 23:24:19

Re: [HOWTO] Install Sphinx for Adult Script Pro

#24 2011-06-07 04:19:42

Re: [HOWTO] Install Sphinx for Adult Script Pro

#25 2011-06-07 08:03:07

Re: [HOWTO] Install Sphinx for Adult Script Pro

Board footer