Could cloning / imaging SSD cause MySQL data to be more fragmented?

Question

On a MySQL server (InnoDB tables) with /var/lib/mysql around 350 GB, I cloned the nvme SSD (using Macrium Reflect) in order to compare query speeds with a faster SSD. But queries are around 4 times slower after cloning to the new disk.

I compared by ... 4 computers that have same config and data is sharded equaly, so they all kind of run at same speed, the one that had disk cloned it completes same tasks 4 times slower. I restarted all servers to rule out any existent caching to ram.

I also seen that all 3 servers rebooted in seconds, while the one with cloned SSD needed a few minutes to complete some background MySQL tasks before shutting down. But I didn't notice any background SQL tasks running while it was ON. I don't think it was one of those long background processes that reverts long operations that failed.

Could cloning the SSD cause the data to be somehow more fragmented ?

I tried to "optimize" tables just to see if it improves anything, but optimizing ~15GB table runs for 24 hours and counting, so that was a bad idea.

The SSDs are different, previous one was Kingston DC1000B ("enterprise") and second one is Samsung 980 PRO. From crystaldiskmark benchmark the Samsung should be 2-3x faster reading, 6x times faster writing, double the IOPS on small files.

Answer 1

I would have to say yes because of the nature of the writes occurring in MySQL

There are files that perform writes in one of two categories

Category #1 : Sequentially Written

• Binary Log
• Relay Log
• Error Log
• Slow Log
• General log
• InnoDB's Redo Log (ib_logfile0,ib_logfile1)
• InnoDB's Insert Buffer

Category #2 : Random Written

• ibdata1 (System Tablespace, Data Dictionary, Undo Log Segments)
• .ibd Files (Tablespace File for Individual Tables)

Writes to an SSD have the same basic performance whether it is sequential or random.

If you clone an SSD disk with MySQL still running, all of those files can be changing. A file that was sequentially written could possibly be scattered on the disk far more differently that when the snapshot started compared to when it ended. The same would go for all files randomly written. You are more than likely feeling the effects of such cloning.

There are two basic ways you can clone the data to have a little more consistency

METHOD #1 : Shutdown MySQL and Clone

Step 1 : Tell mysqld to flush everything transactional setting innodb_fast_shutdown to 0

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Step 2 : Shutdown mysqld

service mysql stop

Step 3 : Perform Snapshot

Step 4 : Start mysqld

service mysql stop

METHOD #2 : Use Xtrabackup

You can execute xtrabackup against a running mysqld process.

There is a method known as streaming backup

Step 1 : On target host, wipe away data folder

cd /var/lib
rm -rf mysql
mkdir mysql

Step 2 : Get IP Address of target host

ip r

or

ip addr show

For the sake of this example, target IP is 10.20.30.253

Step 3 : On target host, setup netcat process to catch streaming backup from source host (port 9999)

screen -S streaming_backup_from_source
nc -l 9999 | xbstream -c -C /var/lib/mysql

Step 4 : On source host, launch streaming backup and redirect output to netcat on port 9999

screen -S streaming_backup_to_target
time innobackupex --no-timestamp --slave-info --user=username -ppassword --open-files-limit=100000 --stream=xbstream /dev/shm | nc -N 10.20.30.253 9999

Step 5 : Monitor screen sessions to see when streaming backup is done

Step 6 On target host, run apply log option

innobackupex --apply-log

Step 7 : On target host, make folder owner to be mysql

chown -R mysql:mysql /var/lib/mysql

Step 8 : On target host, start mysqld

service mysql start

SUGGESTION

You should move all sequentially written files to another disk. I specifically recommend binary logs and the slow log be on another disk I learned this from a Facebook Engineer's old blog post and mentioned it in old posts.