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 two RDS instances: a R/W master and a read-only replica.

On 29th June, the replica stopped registering replication data - not sure if this is related.

On July 3rd, the master's CPU usage started increasing monotonically, and drastically: enter image description here

It's almost at critical 100% now.

The query volume and hasn't really changed, to my knowledge. The only thing which happened around then was a django-celery daemon in my web tier grabbed a whole CPU core - a force kill of that seemed to fix problems on the web tier, but it seems likely that the DB tier problems may be related.

DB size also started increasing monotonically at the same time: enter image description here

There are no long queries in the processlist, and no INSERTs at all really, so I'm not sure how to find out which tables are growing, and where that CPU is going.

Are there MySQL diagnostics while can show me table size trends? Profile global ongoing queries? Profile global CPU usage?

I've already rebooted the server a couple of times, to no avail.

There's obviously still lots of space left on the server, but when we get to 100% CPU usage, things are going to get ugly, so any help much appreciated!

share|improve this question
    
Side question : Is all your data InnoDB ??? –  RolandoMySQLDBA Jul 12 '12 at 20:24
    
Ah, should have said - yes: all InnoDB. I'm just trying dumping out show table status periodically and looking at the Data_length, but not had enough time to get useful data yet... –  James Brady Jul 12 '12 at 20:39
    
I mentioned in an answer to this question 'pt-summary', and a monitoring tool like 'nagios' and graphing such as 'cacti' that are good tools if you have access to the command line. However, since this question is about RDS which you don't have access, those tools don't apply. –  Derek Downey Jul 18 '12 at 16:37
    
How many cores? What version of MySQL? –  Rick James Jul 23 '12 at 23:49
add comment

3 Answers 3

I have some queries for you regarding table sizes that you can run in MySQL during these spikes

Database size in terms of StorageEngine (MB)

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;

Database size in terms of Databases (MB)

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
CONCAT(LPAD(FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM (SELECT
IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 2 pw) BB ORDER BY (SDSize+SXSize);

Database size in terms of Database/StorageEngine (MB)

SELECT IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,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 table_schema,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 table_schema,engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;

Pay attention to certain markers

  • Innodb_buffer_pool_pages_dirty
  • Innodb_data_reads
  • Innodb_data_writes

I recommend downloading MySQL Administrator (I know, it's old but I still you it for quick and dirty "I WANNA SEE STATS NOW" moments of day) and set it up. I customized my own graphs to watch the size of the InnoDB Buffer Pool and its dirty pages. You could also just use the Connection Health tab.

share|improve this answer
    
Thanks - I've run those queries and nothing too interesting: everything's in InnoDB, the biggest DB is ~200MB (assuming those units are MB?) which seems pretty reasonable... InnoDB status: 0 dirty pages, 1700 reads, 200 writes –  James Brady Jul 12 '12 at 22:33
    
If you change the (SELECT 2 pw) to (SELECT 3 pw), you get the reports in GB. –  RolandoMySQLDBA Jul 12 '12 at 22:36
add comment

After diffing lots of status output, I came to the conclusion that there was no difference in query volume and no unexpected / harmful queries.

As a last-ditch resort, I deleted the read-only replica and the master's binlog size reduced pretty quickly back down to 0B.

When it reached 0B, the CPU usage dropped back down to previous levels too - ~5%.

This looks something like a bug in the MySQL replication code which was triggered by the RDS outage on June 29th. The slave stopped accepting the master's replays and we got stuck there, with nothing evident to see that was the problem.

Would recommend careful checking of replication status on master and slave to anyone else in similar situation.

share|improve this answer
add comment

You can try MONyog - RDS MySQL Monitor tool which is avialable in AWS Marketplace.

MONyog - RDS MySQL Monitor and Advisor is a 'MySQL DBA in a box' that help you manage MySQL servers. MONyog needs no agents on the servers and hence it is fully cloud ready. Works great with 'Amazon RDS for MySQL' and MySQL servers on 'Amazon EC2'.

enter image description here

share|improve this answer
add comment

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.