very high CPU usage in mysql server

[satheesh]

---

Hi Percona Team,

Greetings.

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.

=====================================
180108 18:25:35 INNODB MONITOR OUTPUT

Per second averages calculated from the last 30 seconds

BACKGROUND THREAD

srv_master_thread loops: 3958 1_second, 3957 sleeps, 395 10_second, 2 background, 1 flush
srv_master_thread log flush and writes: 3973

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 1767147, signal count 3320312
Mutex spin waits 23638911, rounds 17043325, OS waits 163476
RW-shared spins 1834676, rounds 47616805, OS waits 1194818
RW-excl spins 199505, rounds 15759161, OS waits 366225
Spin rounds per wait: 0.72 mutex, 25.95 RW-shared, 78.99 RW-excl

[lorraine.pocklington]

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.

[satheesh]

@lorraine.pocklington;50465

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? .

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.

[sayeedanas]

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”

./innochecksum /var/lib/mysql/ibdata1

[lorraine.pocklington]

That growth isn’t expected, no. There is an article here that might help you pinpoint: https://www.percona.com/blog/2013/08…wing-in-mysql/ and it also mentions a third party tool that might help diagnose.

Percona tools that for monitoring and management are PMM (client server based visual tool) and the Percona Toolkit, which you can find out more about here https://www.percona.com/software/mysql-tools One of the team has suggested that pt-stalk might help. It captures MySQL & system data every n seconds so it is possible to compare the values between runs. https://www.percona.com/blog/2013/01/03/percona-toolkit-by-example-pt-stalk/

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.