Diagnose slow script executions on specific server I work with an unmanaged server in a remote datacenter to host web based applications. When we run the SQL scripts to create and populate an application database the whole process is painfully slow. A set of scripts that in my underpowered local PC runs in 5 seconds, on the remote server can take up to 20 minutes.
When I check the running processes I can see it isn't stuck in any specific statement, it's simply that each one takes several seconds, specially ALTER TABLE statements:
ALTER TABLE foo_bar
ADD CONSTRAINT FK_foo_bar_foo_id
FOREIGN KEY (foo_id)
REFERENCES foo (id)

It's a brand new app, so most tables are empty or have at most a few hundred rows. But slowness already happens much before the INSERT INTO statements. All tables are InnoDB.
I run the scripts through command-line PHP if that matters. There's no antivirus I'm aware of. MySQL/8.0 was installed with Windows MySQL Installer and runs as service with this configuration:
[client]
port=3306
[mysql]
no-beep
[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
default_authentication_plugin=mysql_native_password
default-storage-engine=INNODB
sql-mode=&quot;STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION&quot;
log-output=FILE
general-log=0
general_log_file=&quot;FOO.log&quot;
slow-query-log=1
slow_query_log_file=&quot;FOO-slow.log&quot;
long_query_time=10
log-error=&quot;FOO.err&quot;
server-id=1
lower_case_table_names=1
secure-file-priv=&quot;C:/ProgramData/MySQL/MySQL Server 8.0/Uploads&quot;
max_connections=151
table_open_cache=2000
tmp_table_size=16M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=0
read_rnd_buffer_size=0
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=17
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
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
loose_mysqlx_port=33060
character-set-server = utf8
collation-server = utf8_unicode_ci
log-bin = OFF
skip-bin-log

Where can I start looking at this? What diagnostics can I try?
Which version is each server using?
ALTER TABLE has become significantly slower in 8.0.  It probably has to do with the ability to rollback DDLs.
It has never been wise to depend on a lot of DDLs, but this makes it worse.
If this ALTER was being done to a brand new table, it probably would have been better to include the CONSTRAINT as part of the table definition.  Of course, if the ALTER was generated by mysqldump`, this is not practical.  Furthermore, FKs are very picky about order, so you may be stuck.