MySQL – Implementing a Message Queue Per User

MySQLqueue

I am using the same user to connect 4 servers to a database.

Although the database is not overloaded, I think that if a heavy query delay occurs on one server, the other servers are affected, especially for new connections.

Mysql server accepts up to 4030 simultaneous connections and has no query limits per user.

Does Mysql queue the queries of the same user or those of all?

If I create a mysql user for each server will I better optimize user queries and queues?

Thank you so much.

Best Answer

MySQL does not queue queries for the same user. Each connection, regardless of the MySQL user, has its own thread in mysqld (unless you use the thread pool plugin, but that's a separate conversation), and the MySQL Server allows any thread to start a query at any time. It'll do its best to handle all the threads, by using OS thread scheduling. It would do the same thing if you have a single MySQL user for all threads, or a distinct MySQL user for each thread.

It's actually typical in many applications I've seen that the client connects to the MySQL Server using a single MySQL user credential. There is no queuing by user, though it is possible to run so many concurrent queries that they may queue up against each other for access to some shared resources like CPUs or locks. That has nothing to do with which MySQL users are used.

MySQL does support options for setting resource limits per user (see https://dev.mysql.com/doc/refman/8.0/en/user-resources.html), but I have not seen this feature used except in shared hosting environments.

Related Solutions

Mysql – View active MySQL connections per user

What you need is a breakdown by user and hostname along with a total

SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;

This will handle host address that have a colon separating hostname and port number

I hope everybody is not logging in as root

Mysql – Opening MySQL connection is sometimes very slow

Slow connections could be caused by the amount of memory consumed by each DB Connection being allocated.

Back on April 24, 2012, I answered the post How costly is opening and closing of a DB connection?. In that post, I mentioned how there are many buffers connected to a single DB session.

If your Java application strictly closes DB Connections and opens new ones with out, think about deallocating multiple data buffers and communication packets connected to that old connection.

Combine that with the DB Connection entering in infamous TIME_WAIT state (See my post Too Many Connections) and you get Connections that linger in the OS for quite a while before its resources are released (even if mysqld closed the connection already).

You may need to decrease the size of your sort_buffer_size, read_buffer_size, and join_buffer_size.

If you have any queries that run multiple joins in a single statement, that connection will have multiple join buffers (See my post Fast alternative for "NOT IN"). In that event, you have two choices

Increase the join_buffer_size to make sure an entire join level fits in memory (requires more RAM in the DB Server)
Decompose multiple join query that joins more than 2 tables into individual joins between 2 tables

Best Answer

Related Solutions

Mysql – View active MySQL connections per user

Mysql – Opening MySQL connection is sometimes very slow

Related Question