3

I am running MySQL 5.5.34 on an Amazon EC2 micro instance with EBS (elastic block storage). The instance has been running for around 10 days and according to Amazon I've accumulated around 8.5 million IO requests. This seems abnormally high considering there is one 2 MB database used for Wordpress. I've used the site very lightly, just some editing in the backend, no live traffic. Here is the memory available:

# free -t
            total       used       free     shared    buffers     cached
Mem:        615376     531848      83528          0       2296      58864
-/+ buffers/cache:     470688     144688
Swap:            0          0          0
Total:      615376     531848      83528

If I have around 83 MB of free space in memory why would MySQL not keep the 2 MB database there? The reason I suspect MySQL for the high IO activity is when I run iotop mysqld always shows having the most activity. Here is my config:

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

innodb_buffer_pool_size=64M
query_cache_size=64M
key_buffer_size=64M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
query_cache_type=1
query_cache_limit=2M
table_cache=1024
join_buffer_size=4M
thread_cache_size=128
tmp_table_size=64M
max_heap_table_size=64MB

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Is there any way besides iotop to know if MySQL is using memory vs disk? Does anything in my configuration look wrong for my system?

Update: Ran query from answer below and output was:

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| InnoDB         |             1.469 MB |             0.625 MB |             2.094 MB |
| Total          |             1.469 MB |             0.625 MB |             2.094 MB |
+----------------+----------------------+----------------------+----------------------+

In /var/lib/mysql:

df -h .

Filesystem            Size  Used Avail Use% Mounted on
/dev/xvda1            7.9G  3.8G  4.1G  49% /

ls -l

total 28688
drws--S--- 2 mysql arithmakids     4096 Dec  5 05:07 arithmakids
-rw-rw---- 1 mysql mysql       18874368 Dec 13 23:03 ibdata1
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile0
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile1
drwx------ 2 mysql mysql           4096 Apr 18  2012 mysql
srwxrwxrwx 1 mysql mysql              0 Dec 13 22:54 mysql.sock
drwx------ 2 mysql mysql           4096 Apr 18  2012 performance_schema
drwx------ 2 mysql mysql           4096 Apr 18  2012 test

Based on this what would be good settings for my.cnf?

2 Answers 2

1

MySQL Disk USage

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(
B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(REPLACE(FORMAT(B.TSize/
POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,
SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

This will tell you exactly how much diskspace (in GB) MySQL is actually using for tables.

Please see my other post CPU usage on RDS instance monotonically increasing with no change to query volume on additional Storage Engine diskspace queries.

You also need to do this

cd /var/lib/mysql
df -h .
ls -l

Take note of ibdata1, ib_logfile0, ib_logfile1, and other files. You will also see how much disk is free for the disk holding /var/lib/mysql

Please run ls -l /var/log/mysqld.log to see if the log is too big.

MySQL Memory Usage

You have

innodb_buffer_pool_size=64M
query_cache_size=64M
key_buffer_size=64M

That's 192MB. Since you have a lot of other defaults, you only have about 100 or 131 max connections. I recommend downloading MySQL Administrator for Free and watching memory usage in other aspects.

Epilogue

Please look over my Amazon RDS posts for other ideas

8
  • Updated original question with additional info
    – Joe M
    Dec 13, 2013 at 23:15
  • any suggestions based on the additional info above?
    – Joe M
    Dec 16, 2013 at 20:25
  • Are you running any type of monitoring (SQLYog, Munin, Nagios, phpMyadmin, etc) ??? Dec 16, 2013 at 20:37
  • No, not running any monitoring. Just want to understand if the amount of IO I'm seeing is expected based on my memory and database size or if something is mis-configured. It seems to me like something is wrong if a tiny database is causing that much IO.
    – Joe M
    Dec 16, 2013 at 20:49
  • Is mysql the only thing running in the EC2 instance ? Are you running other things, like memcached ? Dec 16, 2013 at 20:53
0

This snippet was pretty telling:

-rw-rw---- 1 mysql mysql       18874368 Dec 13 23:03 ibdata1
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile0
-rw-rw---- 1 mysql mysql        5242880 Dec 13 23:03 ib_logfile1

Looks like you are under constant write load. What are you doing with this db?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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