Query time on the Software table (OCS ver. 2.8)

Question

Hi everyone,

I'm new to the forum. It's nice to be able to tell you about my problem.

We're testing OCS Inventory in our enviroment. We're importing information about 20118 computers and 37920 softwares at the moment. But we're seeing a problem with displaying data about software. We're waiting 5 minutes on results of query "All Software" for 10 entries on a page. When we're changing page or number entries on a page we have to wait next 5 minutes. This problem doesn't exist when we're displaing data about computers.

Is someone had problem like this ?

Best Regards

Richard

Server information

Operating system

• OS Name : Linux x86_64
• Version : 4.18.0-193.19.1.el8_2.x86_64
• RAM installed : 6801 MB
• Free RAM : 6015 MB
• CPU : Intel(R) Xeon(R) Gold 6152 CPU @ 2.10GHz
• Distribution : CentOS Linux 8 (Core)

Software

• PHP Version : 7.3.23
• Web Server : Apache/2.4.37 (centos) mod_perl/2.0.11 Perl/v5.26.3
• Database Server : MariaDB Server version 10.3.17-MariaDB
• Version OCSReports: 2.8

Answer 1

Same for me : 1-5 minutes to get the "All software Page" and newt 10 items....  (no io wait, no swapping...) 

Another tip  mysql were using all  available CPU,  because there were huge number of locks on software table, due to high number of  select on software table => I decreased  the load to null by adding an index  covering(HARDWARE_ID,NAME_ID,VERSION_ID);

23k machines, 83k softwares

• php 7.2.24, Apache/2.4.29 (Ubuntu),Ubuntu 18.04 version 10.1.43-MariaDB-0ubuntu0.18.04.1
• OCSReports 2.8

Operating system

• Linux x86_64 4.15.0-76-generic, 16384 MB, Intel(R) Xeon(R) CPU E5-2690 0 @ 2.90GHz 
• Ubuntu 18.04.4 LTS

 

EDIT :  no improvment for my part : still 145s to exquete the sql query with ramdisk (no surprise because most of the IO were in memory cache.

Answer 2

can you try a ramdisk ? and post the results before and after here ?

https://www.kiloroot.com/create-a-ram-disk-in-linux-tell-mysql-to-put-stuff-there/

Kind regards