MySQL Forums
Forum List  »  InnoDB

Very slow performance when inserting, updating, deleting and changing schema
Posted by: Nicolás Sabaj
Date: January 27, 2021 01:27PM

Hello,

I'm facing an issue that the only way I found myself to solve it is making a factory reset of my machine.

The problem I have is that the inserts, updates, deletes and schema modifications are taking a lot of time. The disk goes up to 100%, but performs with very slow speeds. This happens when making modifications by:
- Restoring backups from Workbench (speeds up to 5MB/s)
- Applying migrations and seeding a new db from a NodeJS process (speeds up to 1MB/s): this process used to be finished in 2 minutes, but now it takes about 30/40 minutes.

I think this started happening when I started trying to restore big backups (like 14GB of data). I believe this is not a coincidence.

The computer works great. Copying and pasting big files speeds the disk up to 400MB/s, what is a great performance. I use it for gaming and never see any related issues, so seems like the problem only happens with MYSQL.


My environment is:
- Razer Blade 15, 16GB RAM, 512 GB SSD NvME (CA5-8D512), I7 10TH processor
- Windows 10
- WSL 2 (Ubuntu 20.04)
- MySql 8.0 (running on WSL)
- MySQL Workbench (running on Windows)

My mysqld.cnf file looks like this:

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
## * Basic Settings
##
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3308
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-log-bin

bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

myisam-recover-options = BACKUP

log_error = /var/log/mysql/error.log

max_binlog_size = 100M
expire_logs_days = 10

sql_mode=''
default-time-zone=+00:00

Additional information: I tried also using Ubuntu OS, but the same issue happened.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.