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.

  • i'll bet those 'Documentation' in above alerts list are links to the documentation? – Luuk Feb 24 at 17:09
  • i.e. Innodb buffer pool reads – Luuk Feb 24 at 17:11
  • Kind of difficult to give advice without knowing what the table looks like, nor what kind of queries that are executed against it. If possible post CREATE TABLE statement together with keys and indexes and some sample queries that illustrates the workload. – Lennart Feb 24 at 18:29
  • My advice would be to use SQL to extract data from an RDBMS, not an API. The results will be astonishing. – Gerard H. Pille Feb 24 at 18:54
  • 1
    depending on the uptime for these stats, created tmp disk tables looks like you have poor indexing/queries. Configure the slow query log and start looking the queries. Fixing the indexing on those queries will reduce CPU usage. – danblack Feb 25 at 3:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.