Realtime index updating slow

Hi,

I’m currently trying to update a realtime index, the whole index is around 8.000.000 records, but i’m updating 600.000 record of it. This update proces takes forever to complete. Its already running like 6 hours and its still not done. Is there something wrong in my configuration? The server has plenty of resources (more then enough RAM and CPU) and plain indexes are indexed very fast. I’m running manticore 4.2

I’m updating the date trough SQL like this:

begin;
UPDATE [indexname] SET column1 = 12345 WHERE id = 123;
UPDATE [indexname] SET column1 = 12345 WHERE id = 456;
UPDATE [indexname] SET column1 = 12345 WHERE id = 789;
commit;

I collect all 600.000 queries and sent them to manticore as one command.
I’m using PHP to connect to Manticore with PDO:

$oConnection = new PDO(‘mysql:host=123.456.789:1234’, null, null, [
PDO::ATTR_PERSISTENT => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

Config:

searchd
{
binlog_path =
listen = 1234:mysql
log = /manticore/log/searchd.log
pid_file = /manticore/log/searchd.pid
max_filter_values = 100000
collation_server = utf8_general_ci
network_timeout = 10
client_timeout = 15m
sphinxql_timeout = 1h
max_packet_size = 256M
pseudo_sharding = 1
}

index example
{
type = rt
path = /manticore/data/example
rt_field = column2
rt_attr_uint = column1
rt_attr_timestamp = column3
rt_mem_limit = 4096M
}

Can anyone see whats going wrong?

Hi

As said in the documentation :

Transactions are not supported for:

  • UPDATE (which is different from the REPLACE in that it does in-place attribute update).

W/o transactions it works fast for me:

Can insert 633K docs per sec to the index with your schema (batch size 100K, concurrency 4):

snikolaev@dev:~$ php test_slow_rt_update.php 100000 4 8000000
preparing...
100%       querying...
finished inserting
Total time: 12.643150091171
632753 docs per sec
snikolaev@dev:~$ mysql -P9315 -h0 -e "select count(*) from example;"
+----------+
| count(*) |
+----------+
|  8000000 |
+----------+
snikolaev@dev:~$ mysql -P9315 -h0 -e "select * from example order by id asc;"
+------+---------+------------+
| id   | column1 | column3    |
+------+---------+------------+
|    1 |     123 | 1641899667 |
|    2 |     123 | 1641899667 |
|    3 |     123 | 1641899667 |
|    4 |     123 | 1641899667 |
|    5 |     123 | 1641899667 |
|    6 |     123 | 1641899667 |
|    7 |     123 | 1641899667 |

Can update 600K docs in 38 seconds:

snikolaev@dev:~$ (for id in `seq 200000 800000`; do echo "UPDATE example SET column1 = 12345 WHERE id = $id;"; done;) > /tmp/sql
snikolaev@dev:~$ time mysql -P9315 -h0 < /tmp/sql

real	0m38.018s
user	0m1.152s
sys	0m6.063s

Hi Sergey,

Thank you for you’re response and for testing it, didn’t know you can test it that way. I’ve removed the transaction code and find out that the problem was in my PHP code, because when running the way you tested it, it was updated within seconds. So it isn’t a manticore problem but an other problem.

Thank you for you’re response!