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
50GB DiskSpace (don't think it's important)
Ubuntu Linux Server 18.04

Local machine:

8 Core CPU (Intel Core i7 7700)
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
  • Most likely your mysql configuration in /etc/my* actually tuned with an innodb_buffer_pool_size on the DO instance and isn't locally.
    – danblack
    Jan 9, 2019 at 23:56
  • Additional information request. Post on pastebin.com and share the links. RAM size of your MySQL Host server A) complete (not edited) my.cnf Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, df -h for a linux/unix free space list by device, for server tuning analysis. Jan 17, 2019 at 21:27
  • Hi! Can't right now. As soon as I can I will try and supply.
    – Radu Galan
    Jan 25, 2019 at 0:20

1 Answer 1

  • Check the configs on the two machines (my.cnf or wherever)
  • 100% CPU almost always means a missing index and/or a poorly formulated query.
  • Turn on the slowlog if not already on. (And have long_query_time=1)
  • DO probably had SSD drives; you have HDD. But other clues indicate that I/O is not the problem.
  • innodb_buffer_pool_size should be about 26G on your 32GB machine; did you set that?
  • Multiple crawlers implies lots of INSERTs? Do you batch the inserts or do it one row at a time?
  • See this for how to provide me data to investigate the slowlog and the tunables further.
  • Tried setting innodb_buffer_pool_size to 26GB, didn't do the trick. Trying now to enable the long_query_time. When I restarted the mysql service after setting the buffer_pool_size it seemed like the restart took a long time then I was used to ...
    – Radu Galan
    Jan 10, 2019 at 0:30
  • The restart should take under a minute on virtually any hardware.
    – Rick James
    Jan 10, 2019 at 0:35
  • The innodb_buffer_pool_size helped. After more investigations I realized that it was indeed an I/O prioblem. I had a locking system in place that was writing lock files on the HDD for every link that I was crawling. This was slowing the system down. DO was running smoothly because it had SSD, my local machine had the sysmfony project on the HDD also and that was at fault. Thank you very much for your inputs.
    – Radu Galan
    Jan 10, 2019 at 22:10
  • @RaduGalan - Pegging the I/O can (often) be cured via fixing the indexing or query formulation.
    – Rick James
    Jan 10, 2019 at 22:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.