Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've written a data intensive website, but phpmyadmin is telling me there has been 1 billion queries in 24 days. This seems a bit high.

I know there are not that many people using the site, as whilst it is live, there is not really any data. I would have expected something in the hundreds of thousands, from the queries I've been doing for testing.

Is something wrong?

Edit: The number of queries seems to be growing at a rate of 500/second. This is all very new to me, so I don't know if something is wrong.

share|improve this question
1  
"is something wrong" -- only you can answer this! Is it having an effect on performance, are you running out of disk space, are the tables getting locked? It sounds like your question should be "how do I analyse what all these queries are", in which case I would suggest a possible technique of enabling mysql bin logs, and running them through mk-query-digest. –  Coops Aug 10 '11 at 15:51
    
@Coops You're right, in that only I can no for sure. I guess what I am looking for are indicators that something like robots or bots are hitting it a lot. I am brand spanking new to all of this. I will try to find more info on doing what you suggested, because currently it's all greek to me. –  roviuser Aug 10 '11 at 15:55
1  
have you only looked at the phpmyadmin stats? What about your webserver acccess logs? –  Coops Aug 10 '11 at 15:56
    
@Coops phpmyadmin stats are where I got the number in the first place. I'm currently looking into access logs and trying to figure it all out. –  roviuser Aug 10 '11 at 15:57
1  
This question deserves a +1 because this is one of those "OMG Is Something Wrong with MySQL...Oh, is that all ?" questions that many have asked over the years that deserved a clear answer. –  RolandoMySQLDBA Aug 10 '11 at 20:01

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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