Pipleline seems to be getting slower as time goes by


#1

Hi team,

I am confirming the performance of the FS pipeline.
4 Files(CSV typed) are generated with 30,000 rows per second.
During the first 20 mins, almost data is loaded just in time.
However, after that, the pipeline works getting slower and can not receive the data continuously.
Is there any configuration factor in enhancing the performance?
I share my test environment below.

[Server]
4 Hosts(16 CPUs, 64 GB RAM, 1TB DISK per host)
1 MA, 1 CA. 2 Leaves
1 Gbps Network Bandwidth

[Load Server]
16 CPUs, 64 GB RAM, 1TB DISK(same as Memsql server)
Files: 40 files are generated per sec(each file is 550K)

Thank you in advance.


#2

Can you try doing…

select 
    database_name, 
    pipeline_name, 
    batch_start_unix_timestamp, batch_time - max(batch_partition_time) as batch_overhead
from information_schema.pipelines_batches
group by database_name, pipeline_name, batch_id 
order by database_name, pipeline_name, batch_id

is the batch_overhead column growing? If so, you might need to delete or move old files once they show up as LOADED in information_schema.pipelines_files. Unfortunately, MemSQL has no way currently of ls-ing only some of the directory, so if you are adding new files in real time this could cause growing batch overhead. If not, there could be something else going on.

Hope that helps.


#3

Hi JoYo,

Thank you so much for your help.
After deleting the files loaded already, the batch overhead stays under 1 sec.
However, I have another problem. Although adding 2 leaf servers same as old, I was not able to get higher performance.
How can I configure the pipeline option such as BATCH_INTERVAL and MAX_PARTITIONS_PER_BATCH.
Is there any impact through those parameters?
Please check my statement and server specification below.

[STATEMENT]
CREATE PIPELINE sensor_data_file
AS LOAD DATA FS ‘/nfs/data/out_*’
BATCH_INTERVAL 1
MAX_PARTITIONS_PER_BATCH 16
INTO TABLE sensor_data_table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’;

Thanks.


#4

Adding leaves won’t automatically create new partitions. After adding leaves and running rebalance, you will be in a position where you have the same number of partitions but more cores, which means the concurrency the cluster can run at will be higher but the latency of individual queries (in this case pipeline batches) will be more or less the same. In MemSQL 6.8, the only way to increase the partition count is to drop your database, create a new database with more partitions, and reload the data, unfortunately.