Proposal/discussion: thread-safe use of MySQL

[paul-rouse]

This is a discussion and proposal at the moment - I'll PR something when people have had a chance to comment.

I think we should change the MySQL scaffolding so that it uses the mysql library in a thread-safe manner. This has become possible only recently, with the release of mysql-0.1.4, which exports the necessary initialisation and finalisation functions.

tl;dr we need to use bound threads, each properly initialised for the mysql library. For details, see the MySQL manual and Roman Cheplyaka's discussion.

I cannot see any way of hiding this in the libraries, so I propose making the following MySQL-specific changes to the scaffolding:

• Use forkOS instead of forkIO in warp (annoying because we need a conditional definition of forkOSWithUnmask for base<4.9).
• Also in warp, use setOnOpen and setOnClose to arrange calling the thread initialisation and finalisation functions respectively.
• Initialise the mysql library as a whole before we start serving connections.
• Document all this. Perhaps the best place would be a blog post, referenced from short comments in the source.

Contrary to what Roman Cheplyaka says, we do need to call the thread finalisation. If we don't do it, there is definitely a memory leak, except when using recent versions of libmysqlclient. It is therefore very important to do it when creating a large number of OS threads like this.

I did wonder about leaving all this to the point where database operations are going to be done, by using runInBoundThread in runDB. I believe this solution would be OK as far as it goes, but I can't see anything similar which would work for streaming responses. However, I'm far from an expert on conduit, especially transPipe, so maybe I missed something.

Also I still wonder about moving persistent-mysql over to mysql-haskell in the long run. However, at the moment it seems to have awkward incompatibilities with the API presented by mysql-simple: it doesn't seem to offer non-TCP connections, nor any equivalent of connectOptions.

[snoyberg]

I don't have an opinion on the best route forward, but what you're saying
sounds like a good plan.

On Thu, Nov 3, 2016, 2:58 PM Paul Rouse notifications@github.com wrote:

This is a discussion and proposal at the moment - I'll PR something when
people have had a chance to comment.

I think we should change the MySQL scaffolding so that it uses the mysql
library in a thread-safe manner. This has become possible only recently,
with the release of mysql-0.1.4, which exports the necessary
initialisation and finalisation functions.

tl;dr we need to use bound threads, each properly initialised for the
mysql library. For details, see the MySQL manual
https://dev.mysql.com/doc/refman/5.7/en/c-api-threaded-clients.html and Roman
Cheplyaka's discussion
https://ro-che.info/articles/2015-04-17-safe-concurrent-mysql-haskell.

I cannot see any way of hiding this in the libraries, so I propose making
the following MySQL-specific changes to the scaffolding:

• Use forkOS instead of forkIO in warp (annoying because we need a
  conditional definition of forkOSWithUnmask for base<4.9).
• Also in warp, use setOnOpen and setOnClose to arrange calling
  the thread initialisation and finalisation functions respectively.
• Initialise the mysql library as a whole before we start serving
  connections.
• Document all this. Perhaps the best place would be a blog post,
  referenced from short comments in the source.

Contrary to what Roman Cheplyaka says, we do need to call the thread
finalisation. If we don't do it, there is definitely a memory leak, except
when using recent versions of libmysqlclient. It is therefore very
important to do it when creating a large number of OS threads like this.

I did wonder about leaving all this to the point where database operations
are going to be done, by using runInBoundThread in runDB. I believe
this solution would be OK as far as it goes, but I can't see anything
similar which would work for streaming responses. However, I'm far from an
expert on conduit, especially transPipe, so maybe I missed something.

Also I still wonder about moving persistent-mysql over to
mysql-haskell in the long run. However, at the moment it seems to have
awkward incompatibilities with the API presented by mysql-simple: it
doesn't seem to offer non-TCP connections, nor any equivalent of
connectOptions.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#139, or mute the
thread
https://github.com/notifications/unsubscribe-auth/AADBBwXnGW7a5T3WfyLqQ9O1sgxeCx-wks5q6dpwgaJpZM4KoUyy
.

[paul-rouse]

I'll close this now that #140 is done and dusted.