We run a Ubuntu 14.04 based 500GB+ sized mysql5.5 database with about 500+ tables in our production environment. We had a year-end data migration based on some table/schema changes for our database on 30th December 2017. To have minimal downtime, the migration was done in another server and a full dump using innobackupex version 2.3.9 was generated and brought back to the main production server and the database was restored.
Since then, in the production server the CPU usage is always around 90-100% and the load average is quite high., that we have to restart mysqld every 2 hours or so. We are hosted on Amazon ec2 r4.2xlarge instance which has 61MB RAM and 8 CPUs (amazon AWS notion equivalent to 27 core).
As per some advise, ran “show innodb engine status” but not sure if there anything amiss there.
Appreciate your inputs and how we can progress further in this scenario.
Hello, there’s not really enough information here for us to diagnose the cause of your performance problem.
However, as you have changed the schema and design then you would want to look at the changes that you have made and check the impact of those. For example, did you miss an index in the migration, what are the slow queries, what does an explain on the slow queries tell you about the activity, are there mixed data types on joins etc. Did you change the database configuration as well as make schema changes? And so on.
You will find lots of information on our database performance blog about tracking down performance issues in your application. There are also white papers and webinars that might help. If you can focus the problem down to something more specific that you need help with, please post an update.
Thanks Lorraine for your inputs. Given that it was my first post, I had to be brief in my earlier post.
We have been meticulously pouring into the mysql slow query log and compared with our earlier slow queries and there are many queries which are indeed slow in the order of 20-80seconds. But all these slow queries were there even before migration and thankfully we do not see any new hits there. Also indexes look ok and we did add 2 indexes to 2 our huge tables which helped a bit.
Our ibdata1 file size used to be 5 GB which has since the migration has grown to about 14 GB. Should this be a cause of concern ? Can you please suggest some tools that can be run immediately on the database to check for data integrity and to check if innodb internals look healthy.
Once again thanks for your response and do ask if you need any more metrics.
satheesh, you can try below command for check what your ibdata contain, Might be you need to install “innochecksum” first, Most probably, you have many undo logs pages there if your server is heavily written load sever And I believe, you have already enabled “innodb_file_per_table”
Hopefully you will be able to figure it out, or someone from the community can make some more suggestions, but if you have an overall situation with multiple slow queries in a production environment you might find having a performance audit by an experienced consultant majorly useful if you are able to consider that. Obviously I don’t know your situation, so I am just putting that out there. If you want someone to talk with about that you can drop me an email (forum username ‘at’ percona.com) and I can put you in touch.