I migrated some big table we’ve used previously for exporting data into big CSV files.
I see the approach we used there to have a big query returning all the results is not how manticore works - due to the limit of results allocated in the memory.
I’ve decided to introduce some iterator field in the RT index to use it with some 1k window to iterate through all the results.
For example:
SELECT * FROM job_export WHERE max_description_length>=50 AND ANY(country_id)=53 AND iterator<1723452161 ORDER BY iterator DESC LIMIT 1000;
Table looks like this:
+------------------------------+-----------+----------------+
| Field | Type | Properties |
+------------------------------+-----------+----------------+
| id | bigint | |
| job_name | text | indexed stored |
| company | text | indexed stored |
| country_id | mva | |
| region_id | mva | |
| city_id | mva | |
| channel_ids | mva | |
| channel_locale | json | |
| location_locale | json | |
| language | json | |
| iterator | uint | |
| country_code | string | |
| unique_id | string | |
| approved_date | timestamp | |
| final_description | string | |
| salary | uint | |
| salary_is_confidential | uint | |
| branch_id | uint | |
| max_description_length | uint | |
| searchengine_id | uint | |
| days_since_approval | uint | |
| company_extern_id | uint | |
| jl_design_active | uint | |
| was_manually_approved | uint | |
| was_automatically_classified | uint | |
| company_type | uint | |
| branch_id_for_url | string | |
| branch_name_for_url | string | |
| career_level_for_url | uint | |
| channel_ids_for_url | string | |
| prize_salary_for_url | uint | |
| location_names_for_url | string | |
+------------------------------+-----------+----------------+
Table got 5.5Mio of entries and I sometimes have 120 seconds per query…
Often got broken pipe errors:
/* Tue Aug 13 10:04:49.139 2024 conn 97411 (10.42.206.107:59784) real 0.154 wall 0.155 found 43712 */ SELECT * FROM job_extern_export WHERE max_description_length>=50 AND ANY(country_id)=51 AND iterator<1723544809 ORDER BY iterator desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
WARNING: send() failed: 32: Broken pipe, sock=249
/* Tue Aug 13 10:07:22.772 2024 conn 571139 (10.42.206.107:51178) real 138.929 wall 138.930 found 1563602 */ SELECT * FROM job_extern_export WHERE max_description_length>=50 AND prize_salary_for_url IN (1,2) AND company_extern_id!=367408 AND ANY(country_id)=43 AND iterator<1723581097 ORDER BY iterator desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
[Tue Aug 13 10:07:22.964 2024] [53] WARNING: send() failed: 32: Broken pipe, sock=240
WARNING: send() failed: 32: Broken pipe, sock=240
/* Tue Aug 13 10:08:14.122 2024 conn 569954 (10.42.206.107:38646) real 1135.480 wall 1135.481 found 40372 */ SELECT * FROM job_extern_export WHERE ((((max_description_length>=500 AND prize_salary_for_url IN (2,3,4,5,6,7,8)) AND days_since_approval<=180) AND ANY(country_id)=23) AND final_description!='') AND (was_manually_approved=1 OR was_automatically_classified=1) ORDER BY iterator desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
[Tue Aug 13 10:08:14.254 2024] [56] WARNING: send() failed: 32: Broken pipe, sock=266
WARNING: send() failed: 32: Broken pipe, sock=266
/* Tue Aug 13 10:08:15.824 2024 conn 571053 (10.42.206.107:55858) real 291.008 wall 291.009 found 55350 */ SELECT * FROM job_extern_export WHERE (((max_description_length>=50 AND prize_salary_for_url IN (2,3,4,5,6,7,8)) AND ANY(country_id)=83) AND final_description!='') AND (was_manually_approved=1 OR was_automatically_classified=1) ORDER BY iterator desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
[Tue Aug 13 10:08:15.920 2024] [58] WARNING: send() failed: 32: Broken pipe, sock=63
WARNING: send() failed: 32: Broken pipe, sock=63
/* Tue Aug 13 10:05:04.310 2024 conn 96971 (10.42.206.107:45432) real 400.945 wall 400.946 found 46904 */ SELECT * FROM job_extern_export WHERE (((max_description_length>=500 AND prize_salary_for_url IN (3,4,6,7,8)) AND ANY(country_id)=81) AND final_description!='') AND (was_manually_approved=1 OR was_automatically_classified=1) ORDER BY iterator desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
[Tue Aug 13 10:05:04.376 2024] [103] WARNING: send() failed: 32: Broken pipe, sock=91
WARNING: send() failed: 32: Broken pipe, sock=91
/* Tue Aug 13 10:08:24.560 2024 conn 399665 (10.42.206.107:58820) real 1.185 wall 1.186 found 46904 */ SELECT * FROM job_extern_export WHERE (((max_description_length>=500 AND prize_salary_for_url IN (3,4,6,7,8)) AND ANY(country_id)=81) AND final_description!='') AND (was_manually_approved=1 OR was_automatically_classified=1) ORDER BY iterator desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
/* Tue Aug 13 10:08:34.484 2024 conn 399679 (10.42.206.107:42296) real 0.699 wall 0.700 found 1113023 */ SELECT * FROM job_extern_export WHERE max_description_length>=50 AND prize_salary_for_url IN (3,4,5,6,7,8) AND company_extern_id!=367408 AND ANY(country_id)=43 ORDER BY iterator desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
It is allocating up to 20GB of RAM per worker node (3x worker nodes + 1 balancer node)
NAME CPU(cores) MEMORY(bytes)
job-export-balancer-8c5d9794-ptkq6 50m 199Mi
job-export-worker-0 293m 19262Mi
job-export-worker-1 60m 8178Mi
job-export-worker-2 291m 897Mi
Can you give any recommendations what to improve?