您的位置: 首页  >  技术问答  >  如何从多个线程访问MySQL并发

如何从多个线程访问MySQL并发

分类: 技术问答 • 2022-06-02 21:01:35
问题描述：

我们正在做的MySQL的一小标杆，我们要看看它是如何执行我们的数据。

We're doing a small benchmark of MySQL where we want to see how it performs for our data.

这是测试的一部分就是看它是如何工作的，当多个并发线程锤与各种查询服务器。

Part of that test is to see how it works when multiple concurrent threads hammers the server with various queries.

借助 MySQL文档（5.0）是不是多线程的客户真的清楚。我要指出的是，我做的对线程安全的库链接（ libmysqlclient_r.so ）

The MySQL documentation (5.0) isn't really clear about multi threaded clients. I should point out that I do link against the thread safe library (libmysqlclient_r.so)

我使用prepared报表和两者都做的读（SELECT），写（UPDATE，INSERT，DELETE）。

I'm using prepared statements and do both read (SELECT) and write (UPDATE, INSERT, DELETE).


  • 我应该打开每个线程一个连接？如果是的话：我怎么连做这个..似乎 mysql_real_connect（）返回原始数据库句柄，我得到了，当我叫 mysql_init（） ）

  • 如果不是：我怎么确定的结果和方法，如 mysql_affected_rows 返回正确的值，而不是与其他线程的调用（互斥碰撞/锁可以工作，但它觉得不妥）

  • Should I open one connection per thread? And if so: how do I even do this.. it seems mysql_real_connect() returns the original DB handle which I got when I called mysql_init())
  • If not: how do I make sure results and methods such as mysql_affected_rows returns the correct value instead of colliding with other thread's calls (mutex/locks could work, but it feels wrong)

答

作为一个相当大的C应用程序，使得MySQL的多线程调用的维护者，我可以说我已经没有问题，简单地使每一个新的连接线。

As maintainer of a fairly large C application that makes MySQL calls from multiple threads, I can say I've had no problems with simply making a new connection in each thread. Some caveats that I've come across:

  • it seems this bullet only applies to versions < 5.5; see this page for your appropriate version: Like you say you're already doing, link against libmysqlclient_r.
  • Call mysql_library_init() (once, from main()). Read the docs about use in multithreaded environments to see why it's necessary.
  • Make a new MYSQL structure using mysql_init() in each thread. This has the side effect of calling mysql_thread_init() for you. mysql_real_connect() as usual inside each thread, with its thread-specific MYSQL struct.
  • If you're creating/destroying lots of threads, you'll want to use mysql_thread_end() at the end of each thread (and mysql_library_end() at the end of main()). It's good practice anyway.

基本上不共享创建特定于该结构（即 MYSQL_STMT S） MYSQL 结构或任何东西，因为你希望它会工作。

Basically, don't share MYSQL structs or anything created specific to that struct (i.e. MYSQL_STMTs) and it'll work as you expect.

这似乎是比建立连接池，我少的工作。

This seems like less work than making a connection pool to me.

相关推荐