I am working with a system that pulls data using API requests, stores it and pushes data using some other API requests.
We are using a table to store unprocessed data that is being fetched through API requests using InnoDB and the size is around 40 GB for the single table.
And by the way, the system I am talking about was made some time ago with Joomla 3.7.3.
The server we are using is
- CentOS 7
- CPU count 8
- Total memory 16380312 kB
- Total swap 4194300 kB
Some of the alerts I am getting are
- Aborted connects 1.2 k
- Created tmp disk tables 230.1 k
- Handler read rnd 42.7 M
- Handler read rnd next 37.5 G
- Innodb buffer pool reads 2.6 G
- Innodb row lock time max 843
- Innodb row lock waits 34.2 k
- Opened tables 742
- Select full join 6.3 k
- Select range check 78.3 k
- Slow queries 9
- Sort merge passes 1
- Table locks waited 2.5 k
We tried adding more CPUs to the machine but was of no use. Because the system uses Joomla and we have a lot of changes to the core, we are out of ideas about things we can do to improve anything, as making major changes in the system might break things.
Please provide some suggestions. Any type of input is appreciated. Thanks in advance.