Slow MySQL operation

Started by islamicvashikaran, Oct 23, 2022, 09:48 AM

Previous topic - Next topic

islamicvashikaranTopic starter

So why does SQL start to work slowly and break connections?

With a large number of connections, the workbench shows the load on the CPU 3, it can reach 6, although the CPU itself is not loaded and is idle, as if it uses only 1 thread. I think because of this there are exceptions "Timeout expired.
 The timeout period elapsed prior to completion of the operation or the server is not responding."
MySQL version 8.0.35.

When all the clients (bots) have been created and connected to the channels for data collection, everything works fine. The number of connections is expected, there are no exceptions about executing requests. The problems begin precisely during the creation of new clients.
During normal operation, 300 clients create approximately 250-280 DB connections, but at the time of creation they can create 1200-1700 DB connections.
I can't figure out why yet, everything is fine in the code. When the client is created, it executes the following requests:

private void CreateDB()
        {
            try
            {
                using MySqlConnection Connect = DBUtils.GetDBConnection(_DbUserName, _DbPassword);
                Connect.Open();
                string SQL = $"CREATE DATABASE IF NOT EXISTS {_DbName} DEFAULT CHARACTER SET utf8mb4";
                using MySqlCommand command = new MySqlCommand(SQL, Connect);
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                _logWriter.LogWriterTask(e, "CreateTable()");
            }
        }
private void CreateTable()
        {
            try
            {
                using MySqlConnection Connect = DBUtils.GetDBConnection(_DbName, _DbUserName, _DbPassword);
                Connect.Open();
 
                string SQL = $"CREATE TABLE IF NOT EXISTS {_DbName}.dbUserTable ( dbID INTEGER NOT NULL AUTO_INCREMENT, TwitchID INTEGER NOT NULL UNIQUE, Name VARCHAR(30), " +
                "isSub INTEGER, isVip INTEGER, isMod INTEGER, IsBroadcaster INTEGER, UvalCon INTEGER, messageCon INTEGER, roulettCon INTEGER, roulettCD DOUBLE, UvalTimer DOUBLE, banCount INTEGER, Points DOUBLE, IsOnline INTEGER, PRIMARY KEY(dbID))";
                using (MySqlCommand Command = new MySqlCommand(SQL, Connect))
                {
                    Command.ExecuteNonQuery();
                }
 
                SQL = $"CREATE TABLE IF NOT EXISTS {_DbName}.dbUserMessageTable ( dbID INTEGER NOT NULL AUTO_INCREMENT, TwitchID INTEGER NOT NULL, Name VARCHAR(30), Message VARCHAR(600), TimeStamp DOUBLE, PRIMARY KEY(dbID))";
                using (MySqlCommand Command = new MySqlCommand(SQL, Connect))
                {
                    Command.ExecuteNonQuery();
                }
 
 
                SQL = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = '{_DbName}' AND table_name = 'dbUserTable' AND index_name = 'index_Name'";
                int i = 0;
                using (MySqlCommand Command = new MySqlCommand(SQL, Connect))
                {
                    using var sqlReader = Command.ExecuteReader();
                    sqlReader.Read();
                    i = Convert.ToInt32(sqlReader[0]);
                }
                if (i == 0)
                {
                    SQL = $"CREATE UNIQUE INDEX index_Name ON {_DbName}.dbUserTable (Name)";
                    using var createIndexCmd = new MySqlCommand(SQL, Connect);
                    createIndexCmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                _logWriter.LogWriterTask(e, "CreateTable()");               
            }
        }

Can these requests load web server so much?
  •  

patricka

I'm not a big database expert, but I can offer to do the following test for you:
If your DB and tables, cat. you create when each "At the time of creation of the client" have the same name, then just prepare them (i.e. create it all) in advance. Then, each time a new client is created, leave only the part of the work that is really necessary in this case, for example, to make a selection or update/ insert into an ALREADY EXISTING table.

I hope I was able to convey my thought.
Usually one or two DBs are allocated for one task / project, there may be many tables in them, but not 2000, but 3-30, otherwise the overall picture on the part of the developer is lost (and all sorts of nasty things like the current one can also come out).
 My IMHO - first of all, you have a problem with the architecture in the database. At the same time, I have to make a reservation - I am not an expert in this, I would have to wait for somebody more knowledgeable to respond.
  •