[SOLVED] How to speed up C++ MySQL connector?

Issue

I am using C++ MySQL connector.

sql::Connection * db = nullptr;

try {
    db = get_driver_instance()->connect("tcp://localhost:3306", "admin", "admin");
    db->setSchema("test_db");
} 
catch(exception &e) {
    return 1;  
}

The MySQL connector is dynamically linked to the application.

LDFLAGS = -L/usr/local/mysql-connector-c++/lib64
LDLIBS = -lmysqlcppconn
CXXFLAGS = -Wall -Wextra -g -std=c++11 -O3

When I run my application it takes about 13 033 µs and about 10 000 µs is only this connection to MySQL. I also tried to compile it using static library, but it didn’t help.

Is there any way to speed up the database connection?

Solution

how to say to server: give me pointer to DB connection

MySQL connections cannot be shared between processes. If your C++ process starts when an http request is handled, it must open a new MySQL connection at that time.

More typically, a high-performance web app does not fork a new process for every http request. You’re designing code using the 1990’s-era CGI protocol and expecting high performance, you should change your architecture.

For example, a FastCGI implementation should handle many http requests with a single process. That way you can use a connection pool that provides MySQL connections to request handlers without needing to reopen the connection every time.

To speed up individual MySQL connections, consider:

• Upgrade at least to MySQL 5.7, to take advantage of improved connection speed. I thought I read that 8.0 improved this speed even further, but I can’t find a reference for that right now.
• Use the UNIX domain socket interface, which is faster than TCP/IP connections. It should be as simple as using "localhost" when connecting the client to the MySQL server.
• If you do use TCP/IP, then set the option skip_name_resolve.

https://dev.mysql.com/doc/refman/8.0/en/host-cache.html says:

If you have a very slow DNS and many hosts, you might be able to improve performance either by enabling skip_name_resolve to disable DNS lookups, or by increasing the value of host_cache_size to make the host cache larger.

Answered By – Bill Karwin

Answer Checked By – Candace Johnson (BugsFixing Volunteer)