1 billion mysql queries in 24 days? Can something be wrong?
Solution 1:
MySQL will call for queries internally. In fact, just about anything you do in MySQL is a query.
If you turn on the general log or the slow query log, everything mysqld does gets recorded.
If you have --log-queries-not-using-indexes enabled, everything not involving indexes lands in the slow log.
Let's say you run this query:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| annarbor |
| dude |
| example |
| garbage |
| lovesh |
| mysql |
| performance_schema |
| replagdb |
| stuff |
| test |
| tostinni |
| wordpress |
| zipcodes |
+--------------------+
14 rows in set (0.06 sec)
Yes, SHOW DATABASES; is a query. In fact, what the information_schema equivalent ???
mysql> select schema_name "Database" from information_schema.schemata;
+--------------------+
| Database |
+--------------------+
| information_schema |
| annarbor |
| dude |
| example |
| garbage |
| lovesh |
| mysql |
| performance_schema |
| replagdb |
| stuff |
| test |
| tostinni |
| wordpress |
| zipcodes |
+--------------------+
14 rows in set (0.08 sec)
Does the table information_schema.schemata have an index ???
mysql> show create table information_schema.schemata\G
*************************** 1. row ***************************
Table: SCHEMATA
Create Table: CREATE TEMPORARY TABLE `SCHEMATA` (
`CATALOG_NAME` varchar(512) NOT NULL DEFAULT '',
`SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '',
`DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
`DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',
`SQL_PATH` varchar(512) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
No, it does not. So, SHOW DATABASES; would land in a general log and the slow log (with --log-queries-not-using-indexes enabled)
Therefore, many operations we do not think would constitute a query may just be a query, but internal to mysqld.
If you are using any monitoring tools that are connected to mysqld, this would also run up the counts on queries.
Example:
mysql> show global status like 'uptime'; select * from information_schema.global_status where variable_name='uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 613 |
+---------------+-------+
1 row in set (0.00 sec)
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| UPTIME | 613 |
+---------------+----------------+
1 row in set (0.00 sec)
Just retrieving the uptime of mysqld is a query. Internally, how does MySQL count the queries being executed ? Here are two status variables that may shed some light:
Queries : The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.
Questions : The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.
Please don't be that concerned if your MySQL Server is being monitored because the monitoring that calls for status variables are running queries internally to retrieve requested data.
1 billion in 24 days is
- 41.7 million queries per day
- 1.736 million queries per hour
- 28,935 queries per minute
- 482 queries per second
For a MySQL instance that is being monitored, these numbers are not farfetched at all.
If you are using MySQL Workbench, MySQL Administrator, or phpMyAdmin, any page these products generate or update will summon these little status queries and run numbers up quickly.