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?
Best Answer
MySQL Disk USage
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
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
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