Intermittent Delays in Query Execution After Periods of Inactivity

[huiali]

I have implemented a global connection pool in my application using rbatis, structured as follows:

use rbatis::RBatis;
use std::collections::HashMap;
use std::sync::Arc;
use tokio::sync::RwLock;
use lazy_static::lazy_static;

pub struct DataSourceManager {
    pub data_sources: RwLock<HashMap<String, Arc<RBatis>>>,
}

lazy_static! {
    pub static ref DATA_SOURCE_MANAGER: Arc<RwLock<DataSourceManager>> = Arc::new(RwLock::new(DataSourceManager {
        data_sources: RwLock::new(HashMap::new()),
    }));
}

I'm encountering an issue where query execution experiences significant delays after a period of inactivity. Initial queries and consecutive queries when the application starts up work as expected. However, if there's a lapse in database activity (no queries or operations triggered for some time), subsequent query execution suffers from extended wait times. This seems to occur as if the connection is being re-established or there's some latency in reactivating the connection from the pool.

Questions:

Is this behavior related to how rbatis manages connections in the pool after a period of inactivity?
Could it be due to connection timeouts or some kind of connection state reset in rbatis?
What would be the recommended approach to ensure consistent query execution times, even after periods of database inactivity?
I would appreciate any insights or suggestions on how to address this issue. If there are any best practices or configurations in rbatis for managing connections over extended periods of inactivity, that information would be extremely helpful.

Thank you for your assistance!

[zhuxiujia]

I have implemented a global connection pool in my application using rbatis, structured as follows:

use rbatis::RBatis;
use std::collections::HashMap;
use std::sync::Arc;
use tokio::sync::RwLock;
use lazy_static::lazy_static;

pub struct DataSourceManager {
    pub data_sources: RwLock<HashMap<String, Arc<RBatis>>>,
}

lazy_static! {
    pub static ref DATA_SOURCE_MANAGER: Arc<RwLock<DataSourceManager>> = Arc::new(RwLock::new(DataSourceManager {
        data_sources: RwLock::new(HashMap::new()),
    }));
}

I'm encountering an issue where query execution experiences significant delays after a period of inactivity. Initial queries and consecutive queries when the application starts up work as expected. However, if there's a lapse in database activity (no queries or operations triggered for some time), subsequent query execution suffers from extended wait times. This seems to occur as if the connection is being re-established or there's some latency in reactivating the connection from the pool.

Questions:

Is this behavior related to how rbatis manages connections in the pool after a period of inactivity? Could it be due to connection timeouts or some kind of connection state reset in rbatis? What would be the recommended approach to ensure consistent query execution times, even after periods of database inactivity? I would appreciate any insights or suggestions on how to address this issue. If there are any best practices or configurations in rbatis for managing connections over extended periods of inactivity, that information would be extremely helpful.

Thank you for your assistance!

The connection pool of rbatis will retrieve the connection in the get function and perform a ping operation on the connection. If the ping fails, it will discard and choose the next connection or rebuild the connection.

[huiali]

Thank you for your prompt response and the explanation regarding how rbatis manages the connection pool.

I understand that rbatis performs a ping operation on connections retrieved from the pool and discards/rebuilds them if the ping fails. However, I am facing a peculiar issue that I hope to get some guidance on.

In my application, a simple single-table query executes in just a few milliseconds during initial and consecutive queries. However, if there's a gap in querying (after some idle time), the same query takes about one minute to execute. This significant delay after a period of inactivity is quite puzzling and seems to be disproportionately long, even considering the need to establish a new connection.

Could you please advise on the following:

Is there a configuration within rbatis that controls how often these ping operations occur or how the pool decides to discard connections?
Are there any best practices or additional configurations I should consider to minimize this delay?
Could this behavior be influenced by any external factors such as database server settings or network issues?
Any further insights or suggestions on how to resolve or mitigate this issue would be greatly appreciated. I'm looking to ensure that query response times remain consistent, even after periods of inactivity.

Thank you once again for your support and assistance.

[zhuxiujia]

Thank you for your prompt response and the explanation regarding how rbatis manages the connection pool.

I understand that rbatis performs a ping operation on connections retrieved from the pool and discards/rebuilds them if the ping fails. However, I am facing a peculiar issue that I hope to get some guidance on.

In my application, a simple single-table query executes in just a few milliseconds during initial and consecutive queries. However, if there's a gap in querying (after some idle time), the same query takes about one minute to execute. This significant delay after a period of inactivity is quite puzzling and seems to be disproportionately long, even considering the need to establish a new connection.

Could you please advise on the following:

Is there a configuration within rbatis that controls how often these ping operations occur or how the pool decides to discard connections? Are there any best practices or additional configurations I should consider to minimize this delay? Could this behavior be influenced by any external factors such as database server settings or network issues? Any further insights or suggestions on how to resolve or mitigate this issue would be greatly appreciated. I'm looking to ensure that query response times remain consistent, even after periods of inactivity.

Thank you once again for your support and assistance.

What database are you using and have you set a connection timeout for the database?
For example, the default timeout for MySQL should be 8 hours

[zhuxiujia]

Thank you for your prompt response and the explanation regarding how rbatis manages the connection pool.

I understand that rbatis performs a ping operation on connections retrieved from the pool and discards/rebuilds them if the ping fails. However, I am facing a peculiar issue that I hope to get some guidance on.

In my application, a simple single-table query executes in just a few milliseconds during initial and consecutive queries. However, if there's a gap in querying (after some idle time), the same query takes about one minute to execute. This significant delay after a period of inactivity is quite puzzling and seems to be disproportionately long, even considering the need to establish a new connection.

Could you please advise on the following:

Is there a configuration within rbatis that controls how often these ping operations occur or how the pool decides to discard connections? Are there any best practices or additional configurations I should consider to minimize this delay? Could this behavior be influenced by any external factors such as database server settings or network issues? Any further insights or suggestions on how to resolve or mitigate this issue would be greatly appreciated. I'm looking to ensure that query response times remain consistent, even after periods of inactivity.

Thank you once again for your support and assistance.

What do you use to measure query intervals? If it is a Chrome console, HTTP requests may have caching and latency due to browser issues。

But if you switch to Postman, you can see that each request takes a stable amount of time