1

I have a MYSQL Flexible Server in Azure with the following hardware configuration:

MYSQL 8.0 Compute Tier: Business Critical - 16 vCores Size: E16ds_v4 Storage: 269 GB AutoScale IOPS RAM: 128 GB

I would like to resize the configuration and downsize everything because in this moment the pressure and performance is not too hight so in order to save and reduce cost is neccesary change that. But how can I do it?

The database server has an average of 60-100 connections sessions every time, also around 50 K of queries everytime and CPU consuption of 10% of total and memory 50% avg.

Most of the workload in the database server is read and write, the core of the app is to offer test exams for user applications. So users are continually send and requesting data.

3
  • first you must calculate your average load and peek connection, every user needs resources and and then you at the queries you have and how oftnen they will run, as you have more dtata to process
    – nbk
    Commented Apr 15 at 17:10
  • 1
    Additional DB information request, please. OS, Version? 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 24 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 Apr 16 at 19:00
  • CPU speed and number of cores is rarely important in sizing a configuration; MySQL is rarely bounded by them. Use the SlowLog to discover which queries have the most impact on processing. Then work on optimizing them.
    – Rick James
    Commented Apr 28 at 17:54

1 Answer 1

0

It is quite hard to be precise with suggestions as we don’t know almost anything about your setup. Here are some generic suggestions and thoughts.

  1. With memory utilisation of 50% you can go lower but you want to keep an eye on the storage. Less memory may mean there will be more disk access if some part of your active data set does not fit in the memory and has to be accessed from disk. Having said that, not knowing the configuration we are assuming that 50% utilisation comes from the data set, not that the memory is underutilised due to wrong configuration

  2. CPU - you want to keep the CPU load below 50-60% to be able to handle incoming traffic. What it means is, most likely, RAM will limit you to what extent you can scale down, not CPU.

  3. I/O - you have not mentioned anything about current I/O load. If you intend to reduce available memory, this is the metric you want to track as well. Reducing memory may result in increase of I/O operations - you need to find the trade-off that works for you.

  4. Queries per second - in general, it is not important how many queries you execute per second. It is more important to keep an eye on the CPU utilisation that queries generate and the latency for the queries. You want to see query execution time stable across the board and CPU utilisation as low as possible.

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.