Tag: mysql

SQLAlchemy gevent Mysql python drivers comparison

sqlalchemy-gevent-mysql-drivers-comparison

Compare different mysql drivers working with SQLAlchemy and gevent, see if they support cooperative multitasking using coroutines.

The main purpose of this test is to find which mysql driver has best concurrency performance when we use SQLAlchemy and gevent together.
So we won’t test umysql, which isn’t compatible with DBAPI 2.0.

Code is here. Thank you CMGS.

Result example

100 sessions, 20 concurrency, each session take 0.5 seconds (by select sleep)

mysql://root:@localhost:3306/mysql_drivers_test total 100 (20) 50.5239 seconds
mysql+pymysql://root:@localhost:3306/mysql_drivers_test total 100 (20) 2.6847 seconds
mysql+oursql://root:@localhost:3306/mysql_drivers_test total 100 (20) 50.4289 seconds
mysql+mysqlconnector://root:@localhost:3306/mysql_drivers_test total 100 (20) 2.6682 seconds

With greenify ptached MySQLdb (mysql-python).

mysql://root:@localhost:3306/mysql_drivers_test total 100 (20) 2.5790 seconds
mysql+pymysql://root:@localhost:3306/mysql_drivers_test total 100 (20) 2.6618 seconds
mysql+oursql://root:@localhost:3306/mysql_drivers_test total 100 (20) 50.4437 seconds
mysql+mysqlconnector://root:@localhost:3306/mysql_drivers_test total 100 (20) 2.6340 seconds

Pure python driver support gevent’s monkey patch, so they support cooperative multitasking using coroutines.
That means the main thread won’t be block by MySQL calls when you use PyMySQL or mysql-connector-python.

1000 sessions, 100 concurrency, each session only have 1 insert and 1 select

mysql://root:@localhost:3306/mysql_drivers_test total 1000 (100) 10.1098 seconds
mysql+pymysql://root:@localhost:3306/mysql_drivers_test total 1000 (100) 26.8285 seconds
mysql+oursql://root:@localhost:3306/mysql_drivers_test total 1000 (100) 6.4626 seconds
mysql+mysqlconnector://root:@localhost:3306/mysql_drivers_test total 1000 (100) 22.4569 seconds

Oursql is faster than MySQLdb in this case.
In pure python driver, mysql-connector-python is a bit faster than PyMySQL.
use greenify or not won’t affect the testing result in this user scenario.

Setup

mkvirtualenv mysql_drivers_test # workon mysql_drivers_test
pip install --upgrade setuptools pip cython
pip install -r requirements.txt
python mysql_drivers_comparison.py

Test with greenify MySQL-python (on OSX with homebrew).

mkvirtualenv mysql_drivers_test # workon mysql_drivers_test
pip install --upgrade setuptools pip cython
pip install -r requirements.txt
git clone https://github.com/CMGS/greenify
cd greenify
cmake -G 'Unix Makefiles' -D CMAKE_INSTALL_PREFIX=$VIRTUAL_ENV CMakeLists.txt
make & make install
cd ..
export LIBGREENIFY_PREFIX=$VIRTUAL_ENV
pip install git+git://github.com/CMGS/greenify.git#egg=greenify # if you are using zsh, use \#egg=greenify
git clone https://github.com/CMGS/mysql-connector-c
cd mysql-connector-c
export DYLD_LIBRARY_PATH=$VIRTUAL_ENV/lib
cmake -G 'Unix Makefiles' -D GREENIFY_INCLUDE_DIR=$VIRTUAL_ENV/include -D GREENIFY_LIB_DIR=$VIRTUAL_ENV/lib -D WITH_GREENIFY=1 -D CMAKE_INSTALL_PREFIX=$VIRTUAL_ENV CMakeLists.txt
make & make install
cd ..
git clone https://github.com/CMGS/MySQL-python.git
cd MySQL-python
export DYLD_LIBRARY_PATH=$VIRTUAL_ENV/lib
export LIBRARY_DIRS=$VIRTUAL_ENV/lib
export INCLUDE_DIRS=$VIRTUAL_ENV/include
unlink /usr/local/lib/libmysqlclient.18.dylib
ln -s $VIRTUAL_ENV/lib/libmysql.16.dylib /usr/local/lib/libmysqlclient.18.dylib
python setup.py install
brew switch mysql [version] # brew switch mysql 5.6.15 on my env, brew info mysql to check which version is available on your env
cd ..
python mysql_drivers_comparison.py

If the greenify doesn’t work for you, you can use otool -L _mysql.so in your $VIRTUAL_ENV/lib/python2.7/site-packages MySQL-python folder. Can’t find otool even after you installed XCode’s command line tools? Follow this link.

I need say thank you to CMGS. He guided me how to install greenify and how it works, he also help me triage the issues I met (include the otool part). Make greenify and mysql work on OSX make no sense, you shold do it on your application server which probably will be a linux, hope you will figure out how to.

不得不抱怨Mysql的权限实在很二。我昨晚搞了半天都没有搞定我的MBP上的古怪问…

不得不抱怨Mysql的权限实在很二。我昨晚搞了半天都没有搞定我的MBP上的古怪问题：

我在机器上面的mysql在命令行下面用mysql -u root可以直接登上去，但是在django里面设置user = ‘root’, password=”却登不上去，而用有密码的用户可以登上去，告诉我access denied。

我尝试重置密码，用sql更新总是不管用，而且我发现更新结果实际上就在mysql.user表里面，直接更新表比使用命令还简单。

幸好 @nicholasdsj 同学指了一下问题，发现因为我的Terminal里面的mysql客户端即使-h登录进去它也默认使用socket连接，但是socket连接忽略了密码……我不知道这是否是我的机器的配置有问题，但是这个古怪的原因造成我使用mysql客户端尝试密码完全失效……实际上总是有密码。最后干脆执行sql清空一下密码：

update user set Password = '' where User = 'root' and Host = 'localhost';

而后python连接没有问题了，清净了。可是Terminal里面每次都需要输入-p参数然后回车进去了。