XAMPP tmp folder oversized and shows MySQL high CPU usage

Question

I am running a PHP application on XAMPP (PHP 7.3) which my users are usually uploading files (Image and PDF) and the activities on the application are increasing day by day. But Recently I am facing low space on my drive and it shows MySQL high CPU usage up to 97%.

Then I found that in XAMPP, tmp folder ('C:\XAMPP\tmp') there are files with name mysql[random number] with .MAD extension that size of each file is more than 35 GB, and it continue to generate these files until the disk become full and application stop working.

Then I need to stop the XAMPP service and delete the files that the system back to normal.

There are solutions similar to this issue such as XAMPP tmp folder oversized and it recommended to disable xdebug profile in php.ini or xdebug.ini which I couldn't found it in PHP 7.3 version but I added at the end of php.ini like below and checked the php_xdebug.dll is available in php extension folder...

[XDebug] 
xdebug.remote_autostart=0
xdebug.remote_enable=0
xdebug.profiler_enable=0

... and How can I limit the size of temporary tables? which the answers was not to the point even though I followed the answers and implemented some points but still the application is facing the same problem.

Now, I am looking for step by step solution to avoid or to understand what is reason that I could solve the problem.

Answer 1

• in my.ini make sure value of max_heap_table_size and tmp_table_size is not too high.
• as you mentioned in your question, make sure xdebug.profiler_enable is set to Off in php.ini. if doesnt exist just add it.
• make sure you optimize your database tables regularly.
• remove unneccesary indexes. index columns you use to query.
• run queries one by one and check which query is making the huge files.
• if didnt work try using innodb instead of MyISAM.