1

I am running a MySQL server on Debian 7 in a production environment. I recently (more or less accidentally) took a look into the server stats that can be shown in phpMyAdmin. I saw that the MySQL server was generating lots of outgoing traffic by answering tons of SELECT queries.

Some examples:

  • According to phpMyAdmin, I am experiencing about 19,000 connections per hour
  • There are about 7,779,5 SELECT statements per hour
  • I am generating about 160 MB outgoing traffic per hour

You might think that this is not too much for a production environment. The problem is that the applications hosted on this server and using this MySQL host are not too popular yet, so it is somehow very unrealistic that they generate that much traffic.

To find out what causes so many queries, I enabled the detailed logging of the MySQL server and what I saw only 1 minute after restarting the server, was this:

Time                 Id Command    Argument
150306  7:14:13     1 Connect   debian-sys-maint@localhost on
                    1 Quit
                    2 Connect   debian-sys-maint@localhost on
                    2 Quit
                    3 Connect   debian-sys-maint@localhost on mysql
                    3 Query     select @@version_comment limit 1
                    3 Query     show variables like 'datadir'
                    3 Quit
                    4 Connect   debian-sys-maint@localhost on
                    4 Query     select @@version_comment limit 1
                    4 Query     SELECT count(*) FROM mysql.user WHERE user='root' and password=''
                    4 Quit
                    5 Connect   debian-sys-maint@localhost on
                    5 Query     select @@version_comment limit 1
                    5 Query     select concat('select count(*) into @discard from `',
                    TABLE_SCHEMA, '`.`', TABLE_NAME, '`')
      from information_schema.TABLES where ENGINE='MyISAM'
                    5 Quit
                    6 Connect   debian-sys-maint@localhost on
                    6 Query     select @@version_comment limit 1
                    6 Query     select count(*) into @discard from `information_schema`.`COLUMNS`
                    6 Quit
                    7 Connect   debian-sys-maint@localhost on
                    7 Query     select @@version_comment limit 1
                    7 Query     select count(*) into @discard from `information_schema`.`EVENTS`
                    7 Quit
                    8 Connect   debian-sys-maint@localhost on
                    8 Query     select @@version_comment limit 1
                    8 Query     select count(*) into @discard from `information_schema`.`PARAMETERS`

and of course a lot more entries of this kind. So, I am sure that the user debian-sys-maint is causing the traffic.

Can anybody tell me why this user is making so many queries? I know that this is not a "normal" user, but a system user which is - of course - used for maintainance.
If you need more information about the environment, just let me know.

4
  • It's used by mysql startup and logrotate scripts. At startup it checks if system tables need upgrading.
    – Dan
    Mar 6, 2015 at 8:30
  • But the traffic is not only generated at start-up, but all the time throughout the day. And I do not think that logrotate needs 7,000,000 queries per hour.
    – Niklas S.
    Mar 6, 2015 at 8:32
  • True. Is your mysql server into some restart loop? Those queries I think come from /usr/share/mysql/debian-start.inc.sh called from /etc/mysql/debian-start (at least on ubuntu)
    – Dan
    Mar 6, 2015 at 8:38
  • I would say no, because everything else is working fine. I am able to run all the applications that use this database server perfectly. The server is not even slow or something, I am just wondering about the tons of internal traffic.
    – Niklas S.
    Mar 6, 2015 at 8:42

0

You must log in to answer this question.

Browse other questions tagged .