Since I don't know your memory capacity and the batch processing size you've allocated, let's define two variables for flexibility and to balance memory usage:

Create a thread pool for handling CSV file reads: Initialize this with 4 threads and allow it to scale up to a maximum of 8 threads. This will efficiently manage IO operations without overwhelming system resources. Set the size of each CSV chunk: Given that local IO speed is typically fast, you can set the chunk size to 50,000 rows. Even for datasets as large as 1 million rows, this should ensure minimal IO overhead. Local IO operations should take very little time, even for large datasets like 1 million rows. The core bottleneck appears to be in database processing and some custom logic you have implemented. Without knowing the complexity of your logic, here are some suggestions specifically for optimizing database handling:

Establish a database connection pool: This allows you to reuse connections rather than establishing and tearing down connections repeatedly, which can save significant time. Batch queries based on common fields: If your operations primarily involve queries, look for commonalities among the fields you're querying. MySQL can read thousands or even tens of thousands of records very quickly if indexes are properly set up. Batch similar queries together to reduce the number of individual database calls. You can consider these recommendations, although they are quite general since I don’t have specific details about your setup. Optimizing based on these points should help improve performance.