title: MySQL connection pool in python?
body: <p>I'm trying to process large amount of data using Python and maintaining processing status in MySQL. However, I'm surprised there is no standard connection pool for python-mysql (like HikariCP in Java).</p>
  
  <p>I initially started with PyMySQL, things were great until the program ran for first few hours. After few hours, things started to fail. I was getting lot of errors like:</p>
  
  <pre><code>pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 99] Cannot assign requested address)")
  </code></pre>
  
  <p>Moreover, lot of ports were stuck in TIME_WAIT state because I'm opening and closing connections too frequently because of lack of connection pooling</p>
  
  <pre><code>/d/p/950 ❯❯❯ netstat -nt | wc -l
  84752
  </code></pre>
  
  <p>Per <a href="https://vincent.bernat.im/en/blog/2014-tcp-time-wait-state-linux" rel="noreferrer">this</a> and <a href="http://www.fromdual.com/huge-amount-of-time-wait-connections" rel="noreferrer">this</a>, I tried to set tcp_fin_timeout and ip_local_port_range, but hardly anything improved.</p>
  
  <pre><code>echo 30 &gt; /proc/sys/net/ipv4/tcp_fin_timeout
  echo 15000 65000 &gt; /proc/sys/net/ipv4/ip_local_port_range
  </code></pre>
  
  <p>Then I found out that MySQL provides mysql.connector which comes with <a href="https://dev.mysql.com/doc/connector-python/en/connector-python-connection-pooling.html" rel="noreferrer">pooling</a> functionality. After doing all that performance actually deteriorated. More processes started to get failed. I'm using Python's multiprocessing module to simultaneously run 29 processes(multiprocessing.Pool picked this no by default) on a 24 core machine. Following was the code, of course I was using .my.cnf to pass all the credential to avoid committing them to git :</p>
  
  <pre><code>    import mysql.connector
      from mysql.connector import pooling
      conn_pool = pooling.MySQLConnectionPool(pool_name="mypool1",
                                                pool_size=pooling.CNX_POOL_MAXSIZE,
                                                option_files=MYSQL_CONFIG,
                                                option_groups=MYSQL_GROUP_NODE1,
                                                allow_local_infile=True)
  conn = conn_pool.get_connection()
  </code></pre>
  
  <p>Finally, reverted back to old code. Still using PyMySQL and though errors are less frequent it is still causing a major problem. I looked at SQLAlchemy and couldn't really found much of a documentation around pooling. </p>
  
  <p>I'm wondering how's everyone else dealing with mysql-python connection pooling issue? I really believe there should be something out there so that I don't have to reinvent the wheel. </p>
  
  <p>Any pointers are much appreciated.</p>