Indexing --all ends with an error. How to fix?

Question

Sphinx

At startup,
indexer --all
indexing takes some time (10-15 minutes) then stops with an error

indexing index 'legal_entities'...
WARNING: Attribute count is 0: switching to none docinfo
ERROR: index 'legal_entities': sql_fetch_row: Lost connection to MySQL server during query.
total 7444241 docs, 3367671548 bytes
total 446.944 sec, 7534878 bytes/sec, 16655.85 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 2038 writes, 2.779 sec, 930.6 kb/call avg, 1.3 msec/call avg

Here is the config:

source legal_entities
{
        type = mysql
        sql_host = localhost
        sql_user = bdegrul
        sql_pass = 111111
        sql_db = bdegrul
        sql_port = 3306
        sql_sock = /var/run/mysqld/mysqld.sock

        mysql_connect_flags = 32

        sql_query_pre = SET NAMES utf8
        sql_query_pre = SET CHARACTER_SET_RESULTS=utf8
        sql_query_pre = SET SESSION query_cache_type=OFF
        sql_query_pre = SET net_read_timeout=200

        sql_query_range = SELECT MIN(id), MAX(id) FROM legal_entities
        sql_range_step = 1000

        sql_query = \
        SELECT id, name, short_name, \
        subtype, \
        head_position_name, \
        head, \
        head_phone, \
        founders, \
        region, \
        city, \
        legal_address, \
        main_okved, \
        addit_okveds, \
        phone, \
        email \
        FROM legal_entities \
        WHERE id>=$start AND id<=$end
        sql_ranged_throttle = 10
}

index legal_entities
{
        source = legal_entities
        path = /home/bdegrul/sphinx/
        docinfo = extern
        charset_type = utf-8
        mlock = 0
        ondisk_attrs = 1
}

searchd
{
        listen = 127.0.0.1:9312
        log = /var/log/sphinx/searchd.log
        query_log = /var/log/sphinx/query.log
        read_timeout = 5
        max_children = 30
        pid_file = /var/log/sphinx/searchd.pid
}

indexer
{
        mem_limit = 32M
}

If you open and monitor HTOP in the next window, it becomes obvious that the crash occurs at the moment when 10 GB of RAM runs out.
How to fix it?

Answer 1

Create indexes from chunk and index your data in chunks like 1M to 1M, 1M to 2M etc.

index main
{
        type = distributed
        agent = localhost:3312:chunk1
        agent = localhost:3312:chunk2
        agent = localhost:3312:chunk3
        .... 
        agent = localhost:3312:chunkN
}

Answer 2

max_allowed_packet=1G usually helps