0

I have recently setup an 'Azure Database for MySQL flexible server' using the burstable tier. The database is queried by a React frontend via a node.js api; which each run on their own seperate Azure app services.

I've noticed that when I come to the app first thing in the morning, there is a delay before database queries complete. The React app is clearly running when I first come to it, which is serving the html front-end with no delays, but queries to the database do not return any data for maybe 15-30 seconds, like it is warming up. After this initial slow performance though, it then runs with no delays.

The database contains about 10 records at the moment, and 5 tables, so it's tiny.

This delay could conceivably be due to some delay with the node.js server, but as the React server is running on the same type of infrastructure (an app service), configured in the same way, and is immediately available when I go to its URL, I don't think this is the issue. I also have no such delays in my dev environment which runs on my local PC.

I therefore suspect there is some delay with the database server, but I'm not sure how to troubleshoot. Before I dive down that rabbit hole though, I was wondering whether a delay when you first start querying a database (after, say, 12 hours of inactivity) is simply a characteristic of the burtsable tier on Azure?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Jess
  • 151
  • 12
  • During this warm-up period, the database server may need to spin up resources to handle the incoming queries, which can result in slower response times. However, once the server is fully warmed up, you should see faster response times for subsequent queries. – Pratik Lad Feb 17 '23 at 09:42
  • That's the exact symptom that I see. How would I prevent the 'warm-up period' ever happening - and is this an Azure issue, or a MYSQL issue? – Jess Feb 17 '23 at 09:52
  • Consider implementing caching mechanisms, such as query caching or application-level caching, to reduce the load on the database and improve performance. – Pratik Lad Feb 17 '23 at 09:55
  • Thanks Pratik, I'll look into implmenting that. – Jess Feb 17 '23 at 10:06
  • 1
    A react app is just an HTML file with extra steps - there is nothing there to "warm up". Does your node API have "always on" enabled? If not there's your problem. – Derek Gusoff Feb 17 '23 at 15:09
  • 1
    Please post TEXT results of SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool%_at_%'; We may find an assist to reduce your initial delays. – Wilson Hauck Feb 17 '23 at 16:29
  • @Wilson Hauck: when I created this issue on Stack Overflow, that would have returned nothing. Earlier today though I set 'innodb_buffer_pool_dump_at_shutdown' and 'innodb_buffer_pool_load_at_startup' to ON in the Azure config, based on guidance I found here: https://learn.microsoft.com/en-us/azure/mysql/single-server/concept-performance-best-practices – Jess Feb 17 '23 at 17:25
  • @Wilson Hauck 2: I just tested, 5 hours since there was last any activity, and the issue did not occur. One thing I'm confused about is, I read those variables control what happens after a server restart. But I wasn't experiencing the issue after a server restart, it was after a period of inactivity (it might have been happening after a restart too, I didn't test that tbh). So I'm not sure if it is totally fixed tbh. – Jess Feb 17 '23 at 17:28
  • @DerekGusoff - that setting was not turned on - I've just activated it. Thanks. – Jess Feb 17 '23 at 17:31
  • 1
    @JonathanTaylor You should have a smoother operating instance with these two variables = ON. – Wilson Hauck Feb 17 '23 at 19:16

1 Answers1

0

There may be more factors affecting this (see comments from people on my original question), but my solution has been to set two global variables which cache data, improving initial load times. The following should be set to ON in the Azure config:

'innodb_buffer_pool_dump_at_shutdown' 'innodb_buffer_pool_load_at_startup'

This is explained further in the following best practices documentation: https://learn.microsoft.com/en-us/azure/mysql/single-server/concept-performance-best-practices in the section marked 'Use InnoDB buffer pool Warmup'

Jess
  • 151
  • 12