How can I queue MySQL queries to make them sequential rather than concurrent and prevent excessive I/O usage?

Question

I have multiple independent processes each submitting bulk insert queries (millions of rows) into a MySQL database, but it is much slower to have them run concurrently than in sequence.

How can I throttle the execution of these queries so that only one at a time can be executed, in sequence?

I have thought of checking if PROCESSLIST contains any running queries but it may not be the best way to properly queue queries on a real first-come, first-queued, first-served basis.

I am using C# and the MySQL Connector for .NET.

Sounds like you need your processes to talk to a service - a service that queues SQL queries. That service itself would actually update the database. In UNIX, this would be a named pipe. (Actually....hey, have you tried a named pipe?) — , Nov 29 '11 at 18:05
How can I do this from C# (running on Windows, even if the MySQL server sits on Linux) with a remote connection? Also, I would prefer to not depend on a third-party service if possible. — Erwin Mayer, Nov 29 '11 at 18:12

score 2 · Answer 1 · answered Nov 29 '11 at 18:02

I'm guessing that you're using InnoDb (which allows you to do concurrent writes). MyISAM only has table level locking so would queue up the writes.

I'd recommend an approach similar to ruakh's but that you use a table in the database to manage the locking with. The table would be called something like lock_control

Just before you try to do a bulk insert to the table you request a LOCK TABLES lock_control WRITE on this lock_control table. If you are granted the lock then continue with your bulk write and afterwards release the lock. If the table is write locked by another thread then the LOCK TABLES command will block until the lock is released.

You could do this locking with the table you're inserting into directly but I believe that no other thread would be able to read from the table either whilst you hold the lock.

The advantage over doing this locking in the db rather than on the filesystem is that you could have inserts coming in from multiple client machines and it somehow feels a little simpler to handle the locking/inserting all within MySQL.

OK - it uses row level locking so this approach should apply too — James C, Nov 29 '11 at 18:06
Thanks for the suggestion, however I would like to prevent concurrent accesses to different tables as well (as they still cause I/O traffic jam), and to not have to change the code of all clients to make it work (this is why relying on existing information such as PROCESSLIST could make sense). — Erwin Mayer, Nov 29 '11 at 18:07
+1. Yes, using a database table for this makes more sense than using a file. — ruakh, Nov 29 '11 at 18:11

score 1 · Accepted Answer · answered Jun 23 '20 at 08:54

I am not sure if this will help but here goes. I had a similar problem where my program was throwing exceptions as MySql queries were out of order. So, I decided to run my queries in sequence so succeeding queries don't fail. I found a solution here. https://michaelscodingspot.com/c-job-queues/ -

public class job_queue
    {
        private ConcurrentQueue<Action> _jobs = new ConcurrentQueue<Action>();
        private bool _delegateQueuedOrRunning = false;
        public void Enqueue(Action job)
        {
            lock (_jobs)
            {
                _jobs.Enqueue(job);
                if (!_delegateQueuedOrRunning)
                {
                    _delegateQueuedOrRunning = true;
                    ThreadPool.UnsafeQueueUserWorkItem(ProcessQueuedItems, null);
                }
            }
        }
        private void ProcessQueuedItems(object ignored)
        {
            while (true)
            {
                Action item;
                lock (_jobs)
                {
                    if (_jobs.Count == 0)
                    {
                        _delegateQueuedOrRunning = false;
                        break;
                    }
                    _jobs.TryDequeue(out item);
                }
                try
                {
                    //do job
                    item();
                }
                catch
                {
                    ThreadPool.UnsafeQueueUserWorkItem(ProcessQueuedItems, null);
                    throw;
                }
            }
        }
    }

This is a class to run methods one after another in queue. And you add methods that contain Mysql Queries to job_queue by .

var mysql_tasks= new job_queue();
mysql_tasks.Enqueue(() => { Your_MYSQL_METHOD_HERE(); });

Yes this is definitely the way to go these days. – Erwin Mayer Jun 24 '20 at 09:04 — Erwin Mayer, Jun 24 '20 at 09:04

score 1 · Answer 3 · answered Nov 29 '11 at 17:57

1

Create a Bulk insert service / class Get the client to throw the Data at it.

It does them one at a time. Message back done if you need it.

You don't want to be choking your DB to one thread, that will kill everything else.

answered Nov 29 '11 at 17:57

Tony Hopkinson

20,172
3
31
39

score 1 · Answer 4 · edited May 23 '17 at 12:03

1

Not being much of a C#-er, I can't say if this is the best way to do this; but if no one gives a better answer, one common, non-language-specific approach to this sort of thing is to use a temporary file on the file-system. Before performing one of these INSERTs, grab a write-lock on the file, and after the INSERT is done, release the write-lock. (You'll want to use a using or finally block for this.) This answer gives sample code for obtaining a write-lock in C# in a blocking way.

edited May 23 '17 at 12:03

Community

1
1

answered Nov 29 '11 at 17:58

ruakh

175,680
26
273
307

Interesting, though I would prefer to not have to modify all clients to enforce query queuing... – Erwin Mayer Nov 29 '11 at 18:09

How can I queue MySQL queries to make them sequential rather than concurrent and prevent excessive I/O usage?

4 Answers4