MariaDB & MySQL very slow on Windows 10

Admin 1
Viewed 3194

I'm using WAMP on a Windows 10 (i5,ssd) machine and noticed the websites are quite slow. Much slower than on my old Windows 7 (i3,hd) PC.

When I loop a simple script with a calculation: Win10: 0.4 sec Win7: 9.5 sec

But when I add database queries in a loop, it's the opposite: Win10: 147 sec Win7: 15 sec

The script I use insert a simple hash in a table "test":

CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `testvalue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The PHP testscript:

<?php

set_time_limit(0);
$time_start = microtime(true);

$insertTotal = 100000;

$servername = "127.0.0.1";
$username = "root";
$password = "";
$dbname = "speedtest";
$db = new mysqli($servername, $username, $password, $dbname);

$db->query("TRUNCATE test");

for($i=0;$i<$insertTotal;$i++)
{
    $db->query("INSERT INTO test VALUES(null,'".md5(time().rand(0,999999999))."')");
}

$time_end = microtime(true);
$execution_time = ($time_end - $time_start);
echo 'Total exec time: '.$execution_time.' sec.';

There are quite a few topics about this, so I will sum up all I already did:

  1. Change servername from localhost into 127.0.0.1
  2. Apache: disable CGI
  3. PHP: disable Xdebug
  4. Disabled firewall
  5. Disabled virusscanner
  6. Data dir on different Harddisk

raised buffers a lot in my.ini:

key_buffer_size = 512M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M

So what else I'm missing here?

Some additional info: when executing the script, the SSD is having 0% load and the CPU has about a 30% load.

Update:

  • Reinstalled Wamp 3.2.3. Tested it with MySql 5.7.31, 8.0.21 And MariaDB 10.4.10, 10.4.13, 10.5.5. There is a difference of a few seconds here and there, but still slow.
  • Also I changed the script to use $db->multi_query($sql) see if this works. The result for inserting 10.000 rows is 0.13sec on the old computer and 8.5 seconds on the new one.
  • Just to be sure it's not the SSD or something I wrote a same testscript only using SQlite3 this time and insert 1.000.000 rows in just 7 seconds, so it is only MySQL and MariaDB only.
  • Installed VMWare windows 7 on the new PC with Wamp 2.5. Interesting, the execution time is about the same, also slow.
mariadb wamp settings performance
2 Answers

#update# There was actually a problem with my PC indeed. I fixed it at last (https://superuser.com/questions/1584203/windows-10-very-slow-how-to-find-the-cause) and now the test script runs 34 times faster. So also the better script that use multi value insert is way faster. The whole WAMP runs many times faster now. Glad this is solved. #update#

After spending way to much hours on this issue I went on working on my actual import script and suddenly realize I have been barking up on the wrong tree all along. I have been trying to fix a problem that was not actually a problem.

Sure, it's not correct I have a write speed of 500 seperate queries per second when the old PC can do 50.000 or so, but I should never gonne use seperate queries after all. I was so focused on this problem, that I did not realize the performance issue was not effecting everything else persé that MySQL and MariaDB could do.

So Rick James answer was actually right thinking.

Sorry guys for wasting your time. Thank you for helping me! I have been working days on problems before and fix them, but never work on a problem so long that was not a real problem at all!

To give an example, I modified the test script a bit. This can insert 200 million rows in about 14 minutes:

<?php

set_time_limit(0);
$time_start = microtime(true);

$insertTotal = 200000000;

$servername = "127.0.0.1";
$username = "root";
$password = "";
$dbname = "speedtest";
$db = new mysqli($servername, $username, $password, $dbname);

$db->query("TRUNCATE test");

$counter = 0;
$sql = array();
for($i=0;$i<$insertTotal;$i++)
{
    $sql[] = "(null,'".md5(time().rand(0,999999999))."')";
    if($counter == 5000)
    {
        $sql = implode(",",$sql);
        $db->query("INSERT INTO test VALUES ".$sql.";");
        $counter = 0;
        $sql = array();
    }
    $counter++;
}
$sql = implode(",",$sql);
$db->query("INSERT INTO test VALUES ".$sql.";");    

$time_end = microtime(true);
$execution_time = ($time_end - $time_start);
echo 'Total exec time: '.$execution_time.' sec.';
Admin1
  • Move from MyISAM to InnoDB
  • Batch 100 rows in a single INSERT; it will run about 10 times as fast.
  • Depending on the amount of spare RAM and which Engine you are using, some tuning should be done.
  • Multi-query should never be used.
  • autocommit is irrelevant to MyISAM.
  • 100K INSERTs in a single transaction has other problems. Either do the batching, above, or put only 100 inserts in a transaction. (Going above 100 in either case is "diminishing returns".)
user_17668310
Related