1

I'm new to the DBA forum, but not SO, and I've searched for answers on this, and tried what I've found.

We have a Rails app that lives on a Digital Ocean droplet, and a bigger and more powerful droplet for the db. Both are shared CPU droplets.

I'm trying to diagnose very slow query times on MySQL, that got 2x slower today when i ran apt upgrade on the Ubuntu 20 OS.

We are working on optimizing our Rails queries separately and I realize this is usually most of the problem. The reason I feel it may be a server issue is (1) the upgrade today slowed things down (2) Expensive queries locally are slow but reasonable: say, 1s on a 9 year old computer. On the server, the exact same query takes ~10x as long.

Another indication is, when we made an attempt last year to dist-upgrade the db server to Ubuntu 22, we had to roll it back because it blew up our query times in a similar way.

I can post my config file, our slow query log, and whatever else would help, but I don't want to waste anyone's time if i'm asking the wrong questions or missing something obvious.

Additional info:

  • OS: Ubuntu 20.10 (GNU/Linux 5.8.0-48-generic x86_64)
  • CPU: shared, 8 virtual cores (DO)
  • RAM: 16GB
  • Storage: 320GB NVMe disk, of which 3.8% used
6
  • Additional DB information request, please. OS, Version? RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 4 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Commented Jan 27 at 15:19
  • 1
    @WilsonHauck - Thank you! I added the server info in the question, and I've uploaded the mysql server output text here: jpst.it/3zcH5. It's a ton of stuff, but the formatting isn't as illegible as I thought.
    – nimmolo
    Commented Jan 29 at 22:50
  • Thanks for the additional info. Workload Analysis in process. Commented Jan 30 at 15:12
  • Do you have Replication Instance running at DO? Since you are on a Digital Ocean shared server, please ask DO to provide from OS Command Prompt, TEXT results of ulimit -a report so we can see the OS limits available. Commented Jan 30 at 16:40
  • @WilsonHauck - thank you, appreciate it! I have requested those results. We don't have a replication instance.
    – nimmolo
    Commented Jan 30 at 21:39

1 Answer 1

0

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section,

innodb_buffer_pool_instances=3  #  from 8 you only have 8 cores, leave some for other processes.
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function.
read_rnd_buffer_size=16384  # from 256K to reduce Handler_read_rnd_next RPS of 127,615.
innodb_io_capacity=900  # from 200 to use more of available NVME IOPS.
table_open_cache_instances=2  # from 16 to stay below total cores requested.

These suggestions should improve response time, when implemented and stop/start instance.

There are many more opportunities to improve your configuration to improve speed of processing. Pleaes view profile.

4
  • Thank you Wilson - trying those suggestions this morning, will report back. I was able to get the report from ulimit after all: jpst.it/3zwlR
    – nimmolo
    Commented Feb 1 at 18:36
  • @nimmolo Any feedback on suggestions? Better/worse? Commented Feb 9 at 23:13
  • Definitely better, server load is way down. I will send more details when I can dig into them, i want to check the slow query log too. THANK YOU.
    – nimmolo
    Commented Feb 10 at 0:03
  • If you will post last 40 lines of your Slow Query Log, I will try to give you a hand up. Additional info will probably be required after initial review of your log. Commented Feb 10 at 1:31

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.