Egg on my face: I am so embarrassed! After weeks of dealing with this issue, I took another look at my computer configuration and discovered that the drive I believed to be HDD is actually my SSD drive (KXG50PNV2T04 NVMe TOSHIBA), and the so-called SSD drive is actually an HDD (Seagate BarraCuda 4TB Internal Hard Drive HDD). I bought the computer with both drives in 2019. The operating system is on the SSD, and I was using the HHD as an auxiliary drive. Somehow I came to believe the setup to be the reverse. (When I copy files between the drives, the SSD must take credit for the speed of the transfer.)
I will note that I ran the same tests on a laptop with similar memory, processors, and a nearly identical MySQL configuration, which has only an SSD drive. Those tests were very close to my original tests on my desktop using the SSD drive that I mistakenly believed was an HDD drive.
So for the types of queries I was running, the SSD was actually outperforming the HDD consistently by 50 times.
Original post:
To speed up query execution, I moved my MySQL data folder from an HDD to an SSD drive because it was building a table with 150M rows, which required heavy use of the drive. To my surprise, the execution became slower by more than 50 times!
To compare speeds, I ran a couple of long-running, disk-intensive tests using the same exact configuration other than the data and temp directories. The two tests consisted of a stored procedure and a PHP script containing a number of queries that queried the tables and added rows one at a time. Each was each executed tens of thousands of times over several days with a number of reboots between periods of testing.
I do not believe the SSD drive is defective. I use it often for other tasks for which it remains noticeably much faster than the HDD, especially when copying & pasting large files. Windows tells me the drive has no errors. I plan to run some disk utility checks when I have completed this project.
The MySQL server runs privately offline, with no other users, under Windows 10. The data directory is excluded from Microsoft Security virus scanning and is not subject to any other scanning such as real-time backup procedures or search indexing. When I moved the data directory, I also moved the temporary directory ("tmpdir=" and "innodb_tmpdir=") to a directory on the SSD likewise excluded from virus or other scanning. The 4TB drive was slightly more than half-full.
The two tests consisted of creating individual records from a table whose records consisted of strings of delimited values. The setting "innodb_file_per_table" is true; the files for the two tables were 150GB (150M records) and 30GB (600M records).
Any idea what might have gone wrong?
Some relevant settings:
MySQL version 9.1
innodb_buffer_pool_size=25769803776
general_log = 'OFF'
slow_query_log='OFF'
disable-log-bin=1
max_connections=25
table_open_cache=2000
tmp_table_size=2G
max_heap_table_size=2G
thread_cache_size=10
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=0
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
flush_time=0
join_buffer_size=1M
max_allowed_packet=1G
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=100M
table_definition_cache=1400
