Description:
The sysbench "run" command hangs when attempting to create multiple tables for the oltp test using shared memory connections (on Windows).
This turns out to be due to a thread safety issue in the creation of the shared memory connections.
This problem arises with simultaneous connection attempts using shared memory due to way in which connection requests are signalled via a pair of event_connect_request and event_connect_answer Windows events. If multiple clients set the event_connect_request event more or less simultaneously, the server may only see a _single_ request and thus only set the event_connect_answer event once. This results in a single client getting a connection, and the rest waiting "forever" (or until connection timeout) for an event signal that the server will now not send.
How to repeat:
Create a schema called test in a MySQL server with shared memory connections enabled.
Run sysbench with the following command line arguments:
--test=oltp --oltp-table-size=1000000 --oltp-dist-type=uniform --oltp-num-tables=5 --oltp-auto-inc=on --init-rng=off --mysql-user=root --mysql-db=test --mysql-table-engine=innodb --mysql-protocol=memory prepare
Sysbench will (usually) hang - using a multicore machine increases the likelihood of a hang.
Note that the sysbench command line parameter --mysql-protocol=memory is a relatively recent addition to sysbench (use sysbench 0.4.14 or later).
Suggested fix:
A workaround for the specific problem with sysbench is to use tcp connections for the "prepare" command and shared memory connections for (multithreaded) "run" commands as the connections are created with different threading environments for the "prepare" and "run". The "prepare" command creates the connections on separate threads (one thread per table), leading to the problem. The "run" command creates all the connections that it will use (one per requested thread) on a single thread _before_ starting the multiple threads that use the connections, which avoids the problem.
A fix that would affect all clients of the C API would be to replace the following lines of the (MySQL 5.7) sql-common\client.c file:
/* Send to server request of connection */
if (!SetEvent(event_connect_request))
{
error_allow = CR_SHARED_MEMORY_CONNECT_SET_ERROR;
goto err;
}
/* Wait of answer from server */
if (WaitForSingleObject(event_connect_answer, connect_timeout) !=
WAIT_OBJECT_0)
{
error_allow = CR_SHARED_MEMORY_CONNECT_ABANDONED_ERROR;
goto err;
}
with protected code thus:
/* Send to server request of connection */
native_mutex_lock(&LOCK_create_shared_memory);
if (!SetEvent(event_connect_request))
{
error_allow = CR_SHARED_MEMORY_CONNECT_SET_ERROR;
native_mutex_unlock(&LOCK_create_shared_memory);
goto err;
}
/* Wait of answer from server */
if (WaitForSingleObject(event_connect_answer, connect_timeout) !=
WAIT_OBJECT_0)
{
error_allow = CR_SHARED_MEMORY_CONNECT_ABANDONED_ERROR;
native_mutex_unlock(&LOCK_create_shared_memory);
goto err;
}
native_mutex_unlock(&LOCK_create_shared_memory);
I've tested the above code, and it seems to do the trick. I hesitate to submit this as a patch, though, as it isn't using the performance schema aware versions of the mutex locking/unlocking.
Description: The sysbench "run" command hangs when attempting to create multiple tables for the oltp test using shared memory connections (on Windows). This turns out to be due to a thread safety issue in the creation of the shared memory connections. This problem arises with simultaneous connection attempts using shared memory due to way in which connection requests are signalled via a pair of event_connect_request and event_connect_answer Windows events. If multiple clients set the event_connect_request event more or less simultaneously, the server may only see a _single_ request and thus only set the event_connect_answer event once. This results in a single client getting a connection, and the rest waiting "forever" (or until connection timeout) for an event signal that the server will now not send. How to repeat: Create a schema called test in a MySQL server with shared memory connections enabled. Run sysbench with the following command line arguments: --test=oltp --oltp-table-size=1000000 --oltp-dist-type=uniform --oltp-num-tables=5 --oltp-auto-inc=on --init-rng=off --mysql-user=root --mysql-db=test --mysql-table-engine=innodb --mysql-protocol=memory prepare Sysbench will (usually) hang - using a multicore machine increases the likelihood of a hang. Note that the sysbench command line parameter --mysql-protocol=memory is a relatively recent addition to sysbench (use sysbench 0.4.14 or later). Suggested fix: A workaround for the specific problem with sysbench is to use tcp connections for the "prepare" command and shared memory connections for (multithreaded) "run" commands as the connections are created with different threading environments for the "prepare" and "run". The "prepare" command creates the connections on separate threads (one thread per table), leading to the problem. The "run" command creates all the connections that it will use (one per requested thread) on a single thread _before_ starting the multiple threads that use the connections, which avoids the problem. A fix that would affect all clients of the C API would be to replace the following lines of the (MySQL 5.7) sql-common\client.c file: /* Send to server request of connection */ if (!SetEvent(event_connect_request)) { error_allow = CR_SHARED_MEMORY_CONNECT_SET_ERROR; goto err; } /* Wait of answer from server */ if (WaitForSingleObject(event_connect_answer, connect_timeout) != WAIT_OBJECT_0) { error_allow = CR_SHARED_MEMORY_CONNECT_ABANDONED_ERROR; goto err; } with protected code thus: /* Send to server request of connection */ native_mutex_lock(&LOCK_create_shared_memory); if (!SetEvent(event_connect_request)) { error_allow = CR_SHARED_MEMORY_CONNECT_SET_ERROR; native_mutex_unlock(&LOCK_create_shared_memory); goto err; } /* Wait of answer from server */ if (WaitForSingleObject(event_connect_answer, connect_timeout) != WAIT_OBJECT_0) { error_allow = CR_SHARED_MEMORY_CONNECT_ABANDONED_ERROR; native_mutex_unlock(&LOCK_create_shared_memory); goto err; } native_mutex_unlock(&LOCK_create_shared_memory); I've tested the above code, and it seems to do the trick. I hesitate to submit this as a patch, though, as it isn't using the performance schema aware versions of the mutex locking/unlocking.