slow escaping of byte strings in Python 3

When I insert large blobs, e.g. a 10 MB byte string into a longblob field, it takes several seconds to prepare the value for insertion. Profiling indicates that most of that time is spent in the escape_bytes function in converters.py. Here is that function for reference.

def escape_bytes(value, mapping=None):
    # escape_bytes is calld only on Python 3.
    return escape_str(value.decode('ascii', 'surrogateescape'), mapping)

When I performed the same insertion through another interface, the escaping was about 100 times faster.

Is there a way to speed this up?

In [1]: import pymysql.converters

In [2]: bs = bytearray(10*1024*1024)

In [3]: %time x=bs.decode('ascii', 'surrogateescape')
CPU times: user 3.14 ms, sys: 2.51 ms, total: 5.65 ms
Wall time: 5.6 ms

In [4]: len(x)
Out[4]: 10485760

In [5]: %time pymysql.converters.escape_st
pymysql.converters.escape_str          pymysql.converters.escape_string       pymysql.converters.escape_struct_time

In [5]: %time y = pymysql.converters.escape_str(x)
CPU times: user 5.2 s, sys: 59.4 ms, total: 5.26 s
Wall time: 5.26 s

In [6]: %time y = pymysql.converters.escape_string(x)
CPU times: user 5.26 s, sys: 58.1 ms, total: 5.32 s
Wall time: 5.32 s

So slow part is escaping with regular expression.

ESCAPE_REGEX = re.compile(r"[\0\n\r\032\'\"\\]")
ESCAPE_MAP = {'\0': '\\0', '\n': '\\n', '\r': '\\r', '\032': '\\Z',
              '\'': '\\\'', '"': '\\"', '\\': '\\\\'}

def escape_string(value, mapping=None):
    return ("%s" % (ESCAPE_REGEX.sub(
            lambda match: ESCAPE_MAP.get(match.group(0)), value),))

So escaping may be slow when data contains many characters which requires escaping. (e.g. \0)

You may be able to avoid escape_string() by NO_BACKSLASH_ESCAPES.

Escaping should not take this long. Perhaps this function can be re-written more efficiently?

Is NO_BACKSLASH_ESCAPES a good solution? Will it prevent proper encoding of special characters such as quotes?

Escaping should not take this long. Perhaps this function can be re-written more efficiently?

Current escape_string() is fast enough for random bytes.
I use BLOB columns to store gzipped JSON. It is fast enough for such use cases.
I don't have any idea about speedup such function in pure CPython.
One possible future is adding speedup module written in Cython and another Python implementation tuned for PyPy.

Is NO_BACKSLASH_ESCAPES a good solution?

I think so.

Will it prevent proper encoding of special characters such as quotes?

I can't catch what is your question.
Could you rewrite it in easy English with example ?

@methane

The bytearray decode method takes excessively long for random strings:

Python 3.4.3 (default, Jun 10 2015, 19:56:14) 
[GCC 4.2.1 Compatible Apple LLVM 6.1.0 (clang-602.0.53)] on darwin

import pickle, numpy as np
import pymysql.converters
x = pickle.dumps(np.random.randn(1, 1024, 1024))
len(x)
Out[1]: 8388772

import pymysql.converters as conv
%time y=conv.escape_bytes(x)
CPU times: user 3.62 s, sys: 64.3 ms, total: 3.69 s
Wall time: 3.67 s

%time y = x.decode('ascii', 'surrogateescape')
CPU times: user 3.46 s, sys: 22.4 ms, total: 3.48 s
Wall time: 3.46 s

%time z = conv.escape_string(y)
CPU times: user 315 ms, sys: 24.6 ms, total: 339 ms
Wall time: 338 ms

@methane's example above missed this because it used a string filled with zeros.

In Python 3.5, % operator of bytes comes back. I can use it to avoid surrogateescape.
In Python 3.4, I have no way to avoid surrogateescape.

Is there to re-write the decoding routine to use the C version of mysql_real_escape_string? It is at least 100 times faster than decode with surrogateescape.

@dimitri-yatsenko

@dimitri-yatsenko mysql_real_escape_string() returns bytes. How do you use it?
As you said, decoding is slow for random bytes. not escaping.

In [3]: print("SELECT %s" % (b"'abc'"))
SELECT b"'abc'"

It's invalid SQL.

I used a C library to send the same data and the string preparation takes about 100 times less time.

Yes, I do not yet fully understand the problem and am investigating it further. I do not understand why decoding should take so long.

On my team, we are developing a database interface for scientific computing. It often requires storing large amounts of binary data such as images in blobs. We have Python and MATLAB interfaces. The MATLAB interface is about 5 times faster for inserting data. It uses C libraries for MySQL API and does not have this encoding problem. This is a major obstacle for us.

I got why surrogateescape is so slow.
I can make more faster surrogateescape for Python >3.2,<3.5

FYI: http://bugs.python.org/issue24870

So from http://bugs.python.org/issue24870, it appears that surrogateescape was not necessary in the first place?

Do we know a fix for this issue yet?

@dimitri-yatsenko

@dimitri-yatsenko

it appears that surrogateescape was not necessary in the first place?

surrogateescape is necessary. Consider this simplified example:

blob = bytes(range(256))
u"SELECT %s" % (escape_blob(blob),)

Python 3.4 requires blob is unicode text in this case.

Do we know a fix for this issue yet?

Use NO_BACKSLASH_ESCAPES sql_mode and wait Python has faster surrogateescape.

What changes do I need to make in pymysql to make NO_BACKSLASH_ESCAPES work?

When I simply set sql_mode to NO_BACKSLASH_ESCAPES, MySQL throws errors upon inserting byte arrays.

Add sql_mode='TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES' to connect options.

You can set it in server side my.cnf to avoid additional roundtrip in connection initialise.

If I simply change the sql_mode in my.cnf, then MySQL throws an error:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '����,��\\Z&\x14S��+\x1bO\x1cS۟�?\x03Z�N�\x0f�}��#��̒\x02�]\x03m���h�:�' at line 1")

What do I need to change in pymysql to make blobs work with sql_mode=NO_BACKSLASH_ESCAPES?

Can we modify pymysql so that with NO_BACKSLASH_ESCAPES it automatically skips surrogateescape?

What do I need to change in pymysql to make blobs work with sql_mode=NO_BACKSLASH_ESCAPES?

There is a bug relating to this. I'll investigate it.

Can we modify pymysql so that with NO_BACKSLASH_ESCAPES it automatically skips surrogateescape?

No. It's completely unrelated.
(a) SQL escaping with regular expression and (b) decoding bytes with surrogateescape for % are slow.
NO_BACKSLASH_ESCAPES only fixes (a).

I recommend you to use mysqlclient until I fix them.

I'm sorry. mysqlclient is faster at SQL escaping, but decoding with surrogateescape is required too.

Ah! There is another solution which can be used for now.
You can use hex to represent binary data. Both x'616263' and 0x616263represent 'abc'.

mysql> SELECT x'616263', 0x616263, "abc";
+-----------+----------+-----+
| x'616263' | 0x616263 | abc |
+-----------+----------+-----+
| abc       | abc      | abc |
+-----------+----------+-----+

It consume double network bandwidth, but it is faster than surrogateescape.
Try this monkey patching.

import binascii
import pymysql.connections

def escape_bytes(bs, mapping=None):
    assert isinstance(bs, (bytes, bytearray))
    return '0x' + binascii.b2a_hex(bs).decode('ascii')

pymysql.connections.escape_bytes = escape_bytes

thank you. This is a little better.

@methane

@methane: I was not able to apply the "monkey patch" and needed to clone pymysql to apply this fix. It does not seem possible to modify a function in an imported module as the fix above suggests.

Does PEP-461 finally resolve this issue in Python 3.5.1?
https://www.python.org/dev/peps/pep-0461/

@dimitri-yatsenko

@dimitri-yatsenko It make possible to use another way similar to Python 2.

But I find a workaround which can be used with Python 3.4. (#409)
Until I can drop Python 3.4 support, this workaround may be fast enough for most situations.

Could you try current master?

Navigation Menu

Repository navigation

slow escaping of byte strings in Python 3 #366

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

slow escaping of byte strings in Python 3 #366

Description

Activity

methane commented on Aug 13, 2015

methane commented on Aug 13, 2015

methane commented on Aug 13, 2015

dimitri-yatsenko commented on Aug 13, 2015

methane commented on Aug 13, 2015

dimitri-yatsenko commented on Aug 13, 2015

methane commented on Aug 14, 2015

dimitri-yatsenko commented on Aug 14, 2015

methane commented on Aug 14, 2015

dimitri-yatsenko commented on Aug 14, 2015

methane commented on Aug 15, 2015

methane commented on Aug 15, 2015

dimitri-yatsenko commented on Aug 18, 2015

methane commented on Aug 19, 2015

dimitri-yatsenko commented on Aug 26, 2015

dimitri-yatsenko commented on Aug 26, 2015

methane commented on Aug 26, 2015

dimitri-yatsenko commented on Aug 26, 2015

dimitri-yatsenko commented on Aug 26, 2015

methane commented on Aug 26, 2015

methane commented on Aug 26, 2015

methane commented on Aug 26, 2015

methane commented on Aug 27, 2015

dimitri-yatsenko commented on Aug 27, 2015

dimitri-yatsenko commented on Sep 1, 2015

dimitri-yatsenko commented on Dec 7, 2015

methane commented on Jan 7, 2016

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions