anonymous user

Forums   Register   Login   Forgot your login/password?   Search

How to setup main+delta to index on a slave.

Common forum | 1 | 2 | 3 | 4 | 5 | ... | 451 | 452 | 453 | 454 | next »» | Create new thread

barryhunter

Name: Barry Hunter
Posts: 5570

2010-10-13 12:38:54 | reply!

As pointed out on the Wish List thread, when using main+delta scheme, you have a
'counter' table, that tracks the current cut-off of the main index. As sphinx/indexer
writes to this table, it makes it not normally possibly to index against the slave.
(because the counter table will be out of sync)

But there is a solution for this. The FEDERATED engine!

Here is what I just did on my system:

#######################
##Connect to master

$ mysql my_database -hdb-master

mysql> show create table sph_counter\G
* 1. row *
              Table: sph_counter
Create Table: CREATE TABLE `sph_counter` (
    `counter_id` varchar(64) NOT NULL,
    `max_doc_id` int(11) NOT NULL,
    PRIMARY KEY (`counter_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create view sph_counter_view as select * from sph_counter;
Query OK, 0 rows affected (0.03 sec)

mysql> exit

#######################
##Connect to slave

$ mysql my_database -hdb-slave

mysql> drop view sph_counter_view;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `sph_counter_view` (
    `counter_id` varchar(64) NOT NULL,
    `max_doc_id` int(11) NOT NULL,
    PRIMARY KEY (`counter_id`)
) ENGINE=FEDERATED
CONNECTION='mysql://my_user:my_passowrd@db-master/my_database/sph_counter';

Query OK, 0 rows affected (0.01 sec)

#######################

...

Now sphinx_conf, just use sph_counter_view instead of sph_counter table in the main/delta
source queries. Can then run indexing on either the master OR THE SLAVE!
genexp

Name: Brian Corrigan
Posts: 1

to: barryhunter, 2012-06-09 02:22:30 | reply!

> As pointed out on the Wish List thread, when using main+delta scheme, you have a
> 'counter' table, that tracks the current cut-off of the main index. As sphinx/indexer
> writes to this table, it makes it not normally possibly to index against the slave.
> (because the counter table will be out of sync)
>
> But there is a solution for this. The FEDERATED engine!
>

I don't understand why we need federated tables here. Why not just create the table on
the slave and don't create it on the master (it's not useful on the master for any reason
is it?)

B
barryhunter

Name: Barry Hunter
Posts: 5570

to: genexp, 2012-06-09 13:39:01 | reply!

> Why not just create the table on the slave and don't create it on the master...?


The main issue - if your slave dies can't just transparently switch indexing to the
master (or another slave for that matter) as the counter table will be gone. So will have
to either manually rebuild the counter table. Or reindex the main to recreate a good
counter for the delta.

I've also seen setups that load balance read requests among servers (eg haproxy) with the
view above, can actually point indexing at this load-balancing/auto-failover stream.

Common forum | 1 | 2 | 3 | 4 | 5 | ... | 451 | 452 | 453 | 454 | next »» | Create new thread