8000 Python3 slow query execution · Issue #475 · PyMySQL/PyMySQL · GitHub
[go: up one dir, main page]
Skip to content

Python3 slow query execution #475

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
paulfantom opened this issue Jun 15, 2016 · 5 comments
Closed

Python3 slow query execution #475

paulfantom opened this issue Jun 15, 2016 · 5 comments

Comments

@paulfantom
Copy link

I am creating mysql plugin for grabbing "GLOBAL STATUS" data for netdata. It executes one query ("SHOW GLOBAL STATUS") every second which doesn't carry very much data. However I found that your implementation of cursor.execute(query) is sluggish. It takes about 2-3x more time than MySQLdb implementation. After some investigation I think that the main reason behind it is usage of socket.makefile() which has some well known performance issues (https://bugs.python.org/issue18329)
@methane
Copy link
Member
methane commented Jun 15, 2016

https://bugs.python.org/issue18329 is about for line in fileobj.
PyMySQL doesn't uses it.

I measured performance again and again. I believe performance bottleneck is parsing packet, not reading.

After some investigation

Results?

@methane
Copy link
Member
methane commented Jun 15, 2016

When loading large number of records, I think bottleneck is here:
https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L1399-L1428

MySQLdb uses C implementation. 3x slower than MySQLdb is good enough for current PyMySQL.

@paulfantom
Copy link
Author
paulfantom commented Jun 15, 2016 •
edited
Loading

All times are measured on old PC with intel Atom and populated mysql database (same can be achived by using raspberryPi).
I am using time() from python time library.
Results:

sql = "SHOW GLOBAL STATUS"
cursor.execute(sql):
PyMySQL: ~0.0397s
MySQLdb: ~0.0082s

I have checked you suspicions and you are right:

_read_result_packet(first_packet): ~0.0332s

average of one iteration of while loop in _read_rowdata_packet: ~0.00007s
average of self.connection._read_packet() in _read_rowdata_packet: ~0.00004s
while loop iterates 446 times

Do you have any recommendations how can I speed it up other than using MySQLdb? (google didn't help much)

@methane
Copy link
Member
methane commented Jun 15, 2016

How about CyMySQL?
https://github.com/nakagami/CyMySQL/

I'm closing this issue since there is #297 already.

@methane methane closed this as completed Jun 15, 2016
@paulfantom
Copy link
Author

Ok, thanks.

@github-actions github-actions bot locked as resolved and limited conversation to collaborators Aug 4, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@methane @paulfantom
0