Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an InnoDB database that I'd like to migrate onto Amazon RDS.

My current implementation, hosted on my own server, shows roughly 8M queries per month.

The RDS site say that I/O Rate $0.10 per 1 million requests

Does 1 I/O = 1 query? ie, will I be billed $80/mo for this amount of usage + the RDS fees?

share|improve this question

2 Answers 2

WARNING : Be very careful with your numbers and what you view as a Query !!!

Why am I giving such a warning ???

Back in August 2011, I wrote post in ServerFault explaining how it is possible for 1 Billion Queries to be executed in 24 days.

Here is that entire post :

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.

SUMMARY

If your site does indeed make 8M queries, then an I/O Rate $0.10 per 1 million requests, should be $0.80 (80 cents) a month. If you execute 1 Billion queries in a Month, that $100.00. Please make absolutely sure these numbers jive and GET IT IN WRITING WITH YOUR CFO SITTING NEXT TO YOU !!!

UPDATE 2012-05-02 16:26 EDT

Since it is 800M queries/month, that's $80.00/month

share|improve this answer
1  
Thanks for the answer... I meant to type 800M, not 8M. Regardless, do you know if 1 query = 1 I/O? –  user8573 May 3 '12 at 20:18
    
If you could answer my related question here it'd be great: dba.stackexchange.com/questions/49869/… –  Click Upvote Sep 14 '13 at 9:11

No, one I/O operation does not equal one query. One query may result in 0 IO operations if it is handled by the query cache (and you are lucky), or it may result in multiple IO operations. Potentially, hundreds and thousands, I guess, depending on the tables, indexes, queries and other details.

http://aws.amazon.com/ebs/ states the following:

Volume storage for Standard volumes is charged by the amount you provision in GB per month until you release it. Volume I/O for Standard volumes is charged by the number of requests you make to your volume. Programs like IOSTAT can be used to measure the exact I/O usage of your system at any time. However, applications and operating systems often do different levels of caching, so for Standard volumes, you will likely see a lower number of I/O requests on your bill than is seen by your application unless you sync all of your I/Os to disk.

iostat is a low-level linux utility, which does not know anything about database queries. http://linux.die.net/man/1/iostat

The quote above is for the EBS service, but RDS is based on EC2 & EBS, so I'm fairly confident they mean the same thing in RDS.

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.