对MySQL的一次并发测试
对MySQL的一次并发测试
现象
一个查询对应一个连接:
void queryDatabase(
sql::mysql::MySQL_Driver* driver,
const std::string& thread_name,
const std::string& db_url,
const std::string& user,
const std::string& password,
const std::string& db_name,
const std::string& query)
{
clock_t start = clock();
clock_t end_connect = 0;
try {
std::shared_ptr<sql::Connection> con = std::shared_ptr<sql::Connection>(driver->connect(db_url, user, password));
con->setSchema(db_name);
std::unique_ptr<sql::Statement> stmt(con->createStatement());
std::unique_ptr< sql::ResultSet> res(stmt->executeQuery(query));
while (res->next()) {
//std::cout << "Thread: " << thread_name << " - ID: " << res->getInt("id") << " ";
//std::cout << "Name: " << res->getString("name") << std::endl;
}
}
catch (sql::SQLException& e) {
std::cerr << "SQLException: " << e.what() << " (MySQL error code: " << e.getErrorCode() << ", SQLState: " << e.getSQLState() << " )" << std::endl;
}
catch (...) {
}
fprintf(stderr, "[%s] amount elapse time %.3f, connect elpase time:%.3f\n", thread_name.c_str(), double(clock() - start) / 1000.0f, double(end_connect - start) / 1000.0f);
}
设置1000个并发:
sql::mysql::MySQL_Driver* driver = sql::mysql::get_mysql_driver_instance();
std::vector<std::thread> threads;
for (int i = 0; i < 1000; ++i) {
threads.emplace_back(queryDatabase, driver, "Thread_" + std::to_string(i + 1), db_url, user, password, db_name, query);
}
for (auto& t : threads) {
t.join();
}
现象如下， 基本每个线程都会新建一个连接， 然后进行查询， 基本耗时相近为毫秒级。
如果将调用调整为共享连接， 即使用连接池， 连接池设计如下：
class ConnectionPool {
public:
ConnectionPool(const std::string& db_url, const std::string& user, const std::string& password, const std::string& db_name, int pool_size)
{
driver = sql::mysql::get_mysql_driver_instance();
for (int i = 0; i < pool_size; ++i) {
connections.push(createConnection(db_url, user, password, db_name));
}
}
~ConnectionPool() {
while (!connections.empty()) {
delete connections.front();
connections.pop();
}
}
std::shared_ptr<sql::Connection> getConnection() {
{
std::unique_lock<std::mutex> lock(mtx);
while (connections.empty()) {
cv.wait(lock);
}
}
auto conn = std::shared_ptr<sql::Connection>(connections.front(), [this](sql::Connection* conn) {
std::lock_guard<std::mutex> lock(mtx);
connections.push(conn);
cv.notify_one();
});
{
std::unique_lock<std::mutex> lock(mtx);
connections.pop();
}
return conn;
}
private:
sql::Connection* createConnection(const std::string& db_url, const std::string& user, const std::string& password, const std::string& db_name) {
sql::Connection* conn = driver->connect(db_url, user, password);
conn->setSchema(db_name);
return conn;
}
sql::mysql::MySQL_Driver* driver;
std::queue<sql::Connection*> connections;
std::mutex mtx;
std::condition_variable cv;
};
使用连接池进行查询:
void queryDatabase(
ConnectionPool* connectpool,
const std::string& thread_name,
const std::string& query)
{
clock_t start = clock();
clock_t end_connect = 0;
try {
std::shared_ptr<sql::Connection> con = connectpool->getConnection();
end_connect = clock();
std::unique_ptr<sql::Statement> stmt(con->createStatement());
std::unique_ptr< sql::ResultSet> res(stmt->executeQuery(query));
while (res->next()) {
//std::cout << "Thread: " << thread_name << " - ID: " << res->getInt("id") << " ";
//std::cout << "Name: " << res->getString("name") << std::endl;
}
}
catch (sql::SQLException& e) {
std::cerr << "SQLException: " << e.what() << " (MySQL error code: " << e.getErrorCode() << ", SQLState: " << e.getSQLState() << " )" << std::endl;
}
catch (...) {
}
fprintf(stderr, "[%s] amount elapse time %.3f, connect elpase time:%.3f\n", thread_name.c_str(), double(clock() - start) / 1000.0f, double(end_connect - start) / 1000.0f);
}
连接池调用代码:
ConnectionPool pool(db_url, user, password, db_name, 10);
std::vector<std::thread> threads;
for (int i = 0; i < 1000; ++i) {
threads.emplace_back(queryDatabase, &pool,"Thread_" + std::to_string(i + 1), query);
}
for (auto& t : threads) {
t.join();
}
从现象看基本可以观测出， 耗时基本在连接。 查询耗时可忽略不计。 而连接耗时处于一个递增状态。 由于连接池只给定了10个， 而并发数为1000个。 前10个并发基本不需要等待， 越排后等待越久。
[Thread_703] amount elapse time 0.088, connect elpase time:0.000
[Thread_915] amount elapse time 0.810, connect elpase time:0.696
[Thread_93] amount elapse time 1.076, connect elpase time:0.961
[Thread_90] amount elapse time 1.106, connect elpase time:0.988
[Thread_94] amount elapse time 1.125, connect elpase time:1.011
[Thread_95] amount elapse time 1.135, connect elpase time:1.018
[Thread_130] amount elapse time 1.542, connect elpase time:1.423
[Thread_131] amount elapse time 1.571, connect elpase time:1.454
[Thread_181] amount elapse time 2.119, connect elpase time:2.008
[Thread_182] amount elapse time 2.146, connect elpase time:2.023
[Thread_183] amount elapse time 2.166, connect elpase time:2.058
[Thread_263] amount elapse time 3.077, connect elpase time:2.957
[Thread_262] amount elapse time 3.082, connect elpase time:2.957
[Thread_272] amount elapse time 3.145, connect elpase time:3.061
[Thread_268] amount elapse time 3.146, connect elpase time:3.021
[Thread_320] amount elapse time 4.102, connect elpase time:3.671
[Thread_333] amount elapse time 4.190, connect elpase time:3.853
[Thread_355] amount elapse time 4.221, connect elpase time:4.145
[Thread_427] amount elapse time 5.087, connect elpase time:4.965
[Thread_433] amount elapse time 5.116, connect elpase time:5.032
[Thread_429] amount elapse time 5.125, connect elpase time:4.997
[Thread_439] amount elapse time 5.201, connect elpase time:5.115
[Thread_438] amount elapse time 5.209, connect elpase time:5.086
[Thread_473] amount elapse time 5.635, connect elpase time:5.496
[Thread_520] amount elapse time 6.158, connect elpase time:6.033
[Thread_522] amount elapse time 6.174, connect elpase time:6.045
[Thread_526] amount elapse time 6.175, connect elpase time:6.098
[Thread_604] amount elapse time 7.123, connect elpase time:7.011
[Thread_609] amount elapse time 7.131, connect elpase time:7.047
[Thread_607] amount elapse time 7.134, connect elpase time:7.029
[Thread_13] amount elapse time 8.359, connect elpase time:8.261
[Thread_779] amount elapse time 9.124, connect elpase time:9.027
[Thread_833] amount elapse time 9.839, connect elpase time:9.739
[Thread_832] amount elapse time 9.868, connect elpase time:9.727
[Thread_838] amount elapse time 9.930, connect elpase time:9.796
[Thread_70] amount elapse time 11.141, connect elpase time:10.983
[Thread_944] amount elapse time 11.160, connect elpase time:11.056
[Thread_946] amount elapse time 11.180, connect elpase time:11.064
[Thread_964] amount elapse time 11.361, connect elpase time:11.288
[Thread_79] amount elapse time 11.381, connect elpase time:11.304
[Thread_959] amount elapse time 11.409, connect elpase time:11.239
[Thread_969] amount elapse time 11.415, connect elpase time:11.304
[Thread_966] amount elapse time 11.416, connect elpase time:11.297
[Thread_995] amount elapse time 11.779, connect elpase time:11.644
总结
如果每个查询都使用一个连接， 最终将消耗完服务端有限的连接资源。 导致最终无法连接。 虽然使用连接池存在锁等待， 但更为保险。
优化方向可以考虑使用缓存， 优化SQL等方式
