MySQL server very high load
<p>I run a website with <strong>~500 real time visitors</strong>, <strong>~50k daily visitors</strong> and <strong>~1,3million total users</strong>. I host my server on <strong>AWS</strong>, where I use several instances of different kind. When I started the website the different instances cost rougly the same. When the website started to gain users the RDS instance (MySQL DB) CPU constantly keept hitting the roof, I had to upgrade it several times, now it have started to take up the main part of the performance and monthly cost (around 95% of (2,8k$/month)). I currently use a database server with 16vCPU and 64GiB of RAM, I also use Multi-AZ Deployment to protect against failures. <strong>I wonder if it is normal for the database to be that expensive, or if I have done something terribly wrong?</strong></p>

<p><a href="https://i.stack.imgur.com/3BJQV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3BJQV.png" alt="Current settings"></a></p>

<p><a href="https://i.imgur.com/xxAXsMT.png" rel="nofollow noreferrer"><img src="https://i.imgur.com/xxAXsMT.png" alt="enter image description here"></a></p>

<p><strong>Database Info</strong></p>

<p>At the moment my database have 40 tables with the most of them have 100k rows, some have ~2millions and 1 have 30 millions. 
I have a system the archives rows that are older then 21 days when they are not needed anymore.</p>

<p><strong>Website Info</strong></p>

<p>The website mainly use PHP, but also some NodeJS and python. </p>

<p>Most of the functions of the website works like this:</p>

<ol>
<li>Start transaction</li>
<li>Insert row</li>
<li>Get last inserted id (lastrowid)</li>
<li>Do some calculations</li>
<li>Updated the inserted row</li>
<li>Update the user</li>
<li>Commit transaction</li>
</ol>

<p>I also run around 100bots wich polls from the database with 10-30sec interval, they also inserts/updates the database sometimes.</p>

<p><strong>Extra</strong></p>

<p>I have done several things to try to lower the load on the database. Such as enable database cache, use a redis cache for some queries, tried to remove very slow queries, tried to upgrade the storage type to "Provisioned IOPS SSD". But nothing seems to help.</p>

<p>This is the changes I have done to the setting paramters:</p>

<p><a href="https://i.stack.imgur.com/UXdf6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UXdf6.png" alt="enter image description here"></a></p>

<p>I have though about creating a MySQL cluster of several smaller instances, but I don't know if this would help, and I also don't know if this works good with transactions.</p>

<p>If you need any more information, please ask, any help on this issue is greatly appriciated!</p>