Sphinx How can i keep connection active even if no activity for longer time?

Question

I was doing bulk inserts in the RealTime Index using PHP and by Disabling AUTOCOMIT , e.g.

// sphinx connection
$sphinxql = mysqli_connect($sphinxql_host.':'.$sphinxql_port,'',''); 

//do some other time consuming work

//sphinx start transaction
mysqli_begin_transaction($sphinxql);

//do 50k updates or inserts

// Commit transaction
mysqli_commit($sphinxql);

and kept the script running overnight, in the morning i saw

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate
212334 bytes) in

so when i checked the nohup.out file closely , i noticed , these lines ,

PHP Warning: mysqli_query(): MySQL server has gone away in /home/script.php on line 502
Warning: mysqli_query(): MySQL server has gone away in /home/script.php on line 502

memory usage before these lines was normal , but memory usage after these lines started to increase, and it hit the php mem_limit and gave PHP Fatal error and died.

in script.php , line 502 is

mysqli_query($sphinxql,$update_query_sphinx);

so my guess is, sphinx server closed/died after few hours/ minutes of inactivity.

i have tried setting in sphinx.conf

client_timeout = 3600

Restarted the searchd by

systemctl restart searchd

and still i am facing same issue.

So how can i not make sphinx server die on me ,when no activity is present for longer time ?

---

more info added -

i am getting data from mysql in 50k chunks at a time and doing while loop to fetch each row and update it in sphinx RT index. like this

//6mil rows update in mysql, so it takes around 18-20 minutes to complete this then comes this following part.

$subset_count = 50000 ;

$total_count_query = "SELECT COUNT(*) as total_count FROM content WHERE enabled = '1'" ;
$total_count = mysqli_query ($conn,$total_count_query);
$total_count = mysqli_fetch_assoc($total_count);
$total_count = $total_count['total_count'];

$current_count = 0;

while ($current_count <= $total_count){

$get_mysql_data_query = "SELECT record_num, views , comments, votes FROM content WHERE enabled = 1  ORDER BY record_num ASC LIMIT $current_count , $subset_count ";

//sphinx start transaction
mysqli_begin_transaction($sphinxql);

if ($result = mysqli_query($conn, $get_mysql_data_query)) {

    /* fetch associative array */
    while ($row = mysqli_fetch_assoc($result)) {

    //sphinx escape whole array
    $escaped_sphinx = mysqli_real_escape_array($sphinxql,$row);

    //update data in sphinx index
    $update_query_sphinx = "UPDATE $sphinx_index  
    SET 
        views       = ".$escaped_sphinx['views']." , 
        comments    = ".$escaped_sphinx['comments']." , 
        votes   = ".$escaped_sphinx['votes']." 
    WHERE 
        id          = ".$escaped_sphinx['record_num']." ";  

    mysqli_query ($sphinxql,$update_query_sphinx);

    }

    /* free result set */
    mysqli_free_result($result);
}
// Commit transaction
mysqli_commit($sphinxql);

$current_count = $current_count + $subset_count ;
}

Answer 1

You need to reconnect or restart the DB session just before mysqli_begin_transaction($sphinxql)

something like this.

<?php

//reconnect to spinx if it is disconnected due to timeout or whatever , or force reconnect
function sphinxReconnect($force = false) {
    global $sphinxql_host;
    global $sphinxql_port;
    global $sphinxql;
    if($force){
        mysqli_close($sphinxql);
        $sphinxql = @mysqli_connect($sphinxql_host.':'.$sphinxql_port,'','') or die('ERROR'); 
    }else{
        if(!mysqli_ping($sphinxql)){
            mysqli_close($sphinxql);
            $sphinxql = @mysqli_connect($sphinxql_host.':'.$sphinxql_port,'','') or die('ERROR'); 
        }
    }
}



//10mil+ rows update in mysql, so it takes around 18-20 minutes to complete this then comes this following part.

//reconnect to sphinx
sphinxReconnect(true);

//sphinx start transaction
mysqli_begin_transaction($sphinxql);

//do your otherstuff

// Commit transaction
mysqli_commit($sphinxql);