0

There are times that some heavy queries increase server load and after sometime both CPU and Memory reaches 100%.

This is my.cnf configuration file:

[mysqld]
expire_logs_days= 2

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/storage/mysql

# LOGGING #
log-error                      = /var/storage/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/storage/mysql/mysql-slow.log

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# BINARY LOGGING #
log-bin                        = /var/storage/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb-log-files-in-group      = 2
innodb_log_file_size=500M
innodb_buffer_pool_size=6G
innodb_flush_log_at_trx_commit=2
# recently added for compression, omit it if mysql has problems with it
innodb_file_format=Barracuda

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-character-set-client-handshake

[client]
default-character-set   = utf8mb4

[mysql]
default-character-set   = utf8mb4

The server has 6 Cores with 16 GB of RAM. Any suggestion to restrict MySQL and make it safe?


EDIT-1 This is one of the slow queries that took 4.3 Secs from MySQL to return the data and has examined about 3 Million rows:

# Time: 190107  6:22:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 204732  Schema: my_db  QC_hit: No
# Query_time: 4.306051  Lock_time: 0.000058  Rows_sent: 0  Rows_examined: 3253235
# Rows_affected: 0
SET timestamp=1546842150;
SELECT ((old_credit + (1*amount))/10) credit
FROM `credits`
WHERE user_id=' ' or user_id=(SELECT user_id FROM accounts WHERE email=' ')
ORDER BY id DESC
LIMIT 1;

The query is created by a front-end BI application (it can be changed to some extent).

Indexes of the table is:

MariaDB [my_db]> show index from credits;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| credits |          0 | PRIMARY    |            1 | id          | A         |     2014915 |     NULL | NULL   |      | BTREE      |         |               |
| credits |          1 | credit_id  |            1 | credit_id   | A         |     2014915 |     NULL | NULL   | YES  | BTREE      |         |               |
| credits |          1 | user_id    |            1 | user_id     | A         |      134327 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

EDIT-2:

Database changed
MariaDB [my_db]> show index from accounts;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| accounts |          0 | PRIMARY             |            1 | id          | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id             |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | email_idx           |            1 | email       | A         |      486034 |      190 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            2 | email       | A         |      486034 |       40 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            3 | phone       | A         |      486034 |       15 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
  • 1
    What else is running on that machine? The config implies that you should not be running out of RAM for MySQL. As for CPU: That is almost always due to missing indexes and/or poorly formulated queries. Let's see a "slow" query to discuss. – Rick James Jan 6 at 16:52
  • @RickJames, Could you take a look at the EDIT-1 part of the question? It's good to note that there are other processes that are consuming memory and CPU like influxDB, Docker, grafana and so. – ALH Jan 7 at 6:32
  • 1
    Could you post SHOW INDEX FROM accounts: ? For a little safety, in your my.cnf max_connect_errors=10 rather than 1 million to stop hacker/cracker at 10 attempts to break in rather than after they have made 1 million password attempts. – Wilson Hauck Jan 17 at 21:43
  • @WilsonHauck The question is updated with EDIT-2 part. Could you take a look at it. tnx – ALH Jan 19 at 8:56
  • @ALH Could you post results of SHOW CREATE TABLE credits; and SCT accounts; AND results of EXPLAIN SELECT ((old_cretdit ........ Thanks – Wilson Hauck Jan 19 at 17:53
1

Do you really mean = ' '? That is checking for the string being one character long, namely a space.

OR is a performance killer, so let's get rid of it by using UNION:

    ( SELECT  id, ((old_credit + (amount))/10) AS credit
            FROM  `credits`
            WHERE  user_id=' '
            ORDER BY  id DESC
            LIMIT  1;
    )
    UNION ALL
    ( SELECT  c.id, ((c.old_credit + (c.amount))/10) AS credit
            FROM  `credits` AS c
            JOIN  accounts AS a  ON a.user_id = c.user_id
            WHERE  a.email=' ' 
            ORDER BY  c.id DESC
            LIMIT  1;
    )
    ORDER BY  id DESC
    LIMIT  1;

Indexes needed:

credits:  (user_id, id)
accounts: (email, user_id)

This might be even faster, and it eliminates the extra column (id) that the above solution has.

SELECT ((c2.old_credit + (c2.amount))/10) AS credit
  FROM (
    ( SELECT  id
            FROM  `credits`
            WHERE  user_id=' '
            ORDER BY  id DESC
            LIMIT  1;
    )
    UNION ALL
    ( SELECT  c.id
            FROM  `credits` AS c
            JOIN  accounts AS a  ON a.user_id = c.user_id
            WHERE  a.email=' ' 
            ORDER BY  c.id DESC
            LIMIT  1;
    )
    ORDER BY  id DESC
    LIMIT  1;
  ) AS x
  JOIN credits c2  ON c2.id = x.id

The above INDEXes are needed here, too. (And I am assuming that credits has PRIMARY KEY(id).)

Rows_examined: 3253235 will probably decrease to under 10.

As for how to prevent naughty queries, MariaDB 10.1+, but not MySQL, has max_statement_time.

I see no reason why you would be hitting 100% Memory. (see comment.) But each copy of that query would probably chew up one core for 4.3 seconds. (Either of my versions probably won't chew up even 4.3 milliseconds.)

Do you want help with other slow queries?

  • I really appreciate for your time, I'll apply this query optimization and give feedback later on. Just so you know, unfortunately queries like user_id = ' ' are forced by a UI application. – ALH Jan 8 at 19:11
  • And I hit 100% memory because of many similar concurrent queries. – ALH Jan 8 at 19:14
  • 1
    @ALH - up to 500 connections, each creating one (or more) tmp tables of up to 32MB each -- Yes that could blow out RAM. Consider lowering each of those settings as a preventative measure. – Rick James Jan 8 at 19:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.