I have a Symfony project in which I crawl rss feeds and collect articles. I used to have it hosted on Digital Ocean with a droplet instance of 4 cores and 8GB RAM. In the biggest table I have around 500k rows, on Digital Ocean the MySQL instance was running pretty ok handling the crawl processes (between 11 and 20 parallel crawl processes). Due to the fact that the disk space was filled on my droplet (it reached 44GB on my biggest table) I decided to take another approach and move the whole project on my local machine.
DO Instance:
4 vCPUs
8GB RAM
50GB DiskSpace (don't think it's important)
Ubuntu Linux Server 18.04
Local machine:
8 Core CPU (Intel Core i7 7700)
32 GB RAM
2TB HDD (mysql data folder is held on this hard disk)
128GB SSD - os is installed on this
Ubuntu Linux Desktop 18.04
Is there a difference between MySQL versions installed on desktop and server ubuntu versions? My DO instance CPU usage (htop) was almost always approaching 100% on all 4 CPUs, on my local machine the CPU usage is around 20% on each core while running 20 crawl processes at a time. I might be missing a certain configuration on my local machine? Running a simple UPDATE query on an InnoDB table that is not used by the project but is in the same database takes forever (I've been waiting for 10 minutes now almost). Running a SELECT (over through ssh tunneling) on the same table runs smoothly. All tables in my project are InnoDB. Having a look at mytop I have around 20 SELECT queries and 4 update queries right now.
The DO instance was running both a webserver and the mysql instance and it handled everything well from my point of view. On my local machine at this point I'm using the webserver only on my internal network.
Can't figure it out, can anyone give me a hint?
On the DO instance I didn't do any special settings to MySQL and neither to my local machine other than changing the connection_timeout to 24hours + 1 minute (I needed it in another project where I had a process sleep for 24 hours and needed the connection still available when resuming the process).
mysql -V returns:
mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper
innodb_buffer_pool_size
on the DO instance and isn't locally.