对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();
}

现象如下， 基本每个线程都会新建一个连接， 然后进行查询， 基本耗时相近为毫秒级。
phenomenon_1

如果将调用调整为共享连接， 即使用连接池， 连接池设计如下：


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等方式

查看完整代码

posted @   汗牛充栋  阅读(12)  评论(0)  编辑  收藏  举报
相关博文：
·  操作系统之动态链接库
·  windows文件同步实践心得
·  高并发场景下的MySQL性能突破：多队列线程池优化实战
·  每秒50万行——MySQL写入压测并发实践
·  第二十一讲：MySQL有哪些“饮鸩止渴”提高性能的方法？
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· AI 智能体引爆开源社区「GitHub 热点速览」
· 写一个简单的SQL生成工具
点击右上角即可分享
微信分享提示