2

I created a SaaS product. I am using PHP 8.3 with a MySQL 8.0 database. Locally this is very fast! No frameworks etc are used. Production runs on Azure. I have the following setup on Azure:

Database

Azure Database for MySQL flexible server
- Burstable, B1s, 1 vCores, 1 GiB RAM, 20 GiB storage
- MySQL version 8

Cost is about 20 euros/month.

App service

Azure Web App
Basic B1, 100 ACU/vCPU, 1 vCPU, 1.75 memory (GB), 10 GB storage
OS: Linux

Cost is about 10 euros/month

the problem I am experiencing is that initially when a random user goes to my website and perfoms an action where a DB query is needed. The database is VERY slow at first. It could be anywhere from 3-10 seconds! After this 'warm-up' every new user that goes to the website doens't have any delays and the DB queries are very fast. Let's say 30-60 minutes pass and there was no activity on the website and a new user goed to the website, it's very slow again. This is NOT something I want. What do I have to do to remove this 'warm-up'?

One would say that a database for ~20 euros/month would perform better. I've had cheaper MySQL databases that performed better..

Notes:

  • Web App is set to 'Always ON'
  • My DB is VERY small and perfectly optimized, ~5 tables, ~100 records
  • My 'innodb_buffer_pool_dump_at_shutdown' is set to ON
  • My 'innodb_buffer_pool_load_at_startup' is set to ON
  • My 'innodb_buffer_pool_size' is 134217728 bytes (~128 mb)
  • MySql and App service both run in a vnet
  • MySql and App service both use ssl
  • Everything I have is in the same region (West Europe) just like my audience
Improve this question
3
  • 1
    Additional DB information request, please. OS, Version? Azure instance type? , 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.
    – Wilson Hauck
    Commented May 16 at 13:14
  • Please post TEXT results of this query. SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables LIMIT 100000; To consider additional information.
    – Wilson Hauck
    Commented May 16 at 13:16
  • Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, (If you do not have Command Line access, ask Azure to provide the data, please.) top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Commented May 16 at 13:40

3 Answers 3

Reset to default
1

~5 tables, ~100 records

How much data? If it is a lot more than 128MB, then the buffer_pool_size needs to be increased.

But... With only 1GB of RAM in the cloud instance, increasing the buffer_pool would lead to swapping, which would be terrible for performance.

Would it be practical for you to access your web page whenever the db is restarted? This might prime the cache (buffer_pool) on your time rather than the user's time.

You might have to go for a 2GB-RAM instance. I'll look at the things you post for Wilson; maybe I can make a different suggestion.

Meanwhile, here is another debugging tool: SlowLog

Improve this answer
0

Gerrit,

In your my.ini [mysqld] section, add a line

init_connect=SELECT 1;

to engage the instance.

You may be required to

USE (some_db);init_connect=SELECT 1;

Let us know if this eliminates your 'slow-at-first', please.

Improve this answer
0

Sorry for my late reply. Ive switched from "B1s burstable to D2ds general purpose" for testing the last days. The problem I was facing is a characteristic of a burstable database I guess. The D2ds general purpose DB doesn't have this problem. A burstable db won't work for my business context so I'm switching to a general purpose reserverd db.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .