Optimizing InnoDB for high-frequency concurrent API status updates?

[Aaryan Sharma]

Hi everyone,

I am looking for some architectural advice regarding a write-heavy database workload.

I manage the backend infrastructure for SMM Bear, a B2B digital routing platform for marketing agencies. Because our system relies on algorithmic drip-feed pacing, our core MySQL database handles a massive number of concurrent transactions. Specifically, our cron jobs are constantly reading queued orders, fetching data from external APIs, and writing back status updates (Processing, Completed, Partial) multiple times per minute.

We are using InnoDB. During peak hours, when thousands of status updates hit the database simultaneously, we are noticing some transaction queuing and occasional deadlock warnings in the logs.

My questions for the DBA experts here are:

For a queue-like table structure where a 'status' column is being updated constantly, what are the best practices for configuring innodb_buffer_pool_size and innodb_flush_log_at_trx_commit to maximize write throughput safely?

Do you recommend any specific indexing strategies to prevent row-locking bottlenecks when multiple API threads are trying to update different rows in the same table simultaneously?

Any advice on optimizing MySQL for a heavy, high-frequency API environment would be greatly appreciated!

Thanks,
Aaryan Sharma