4 ms·

I once speeded up a program by changing from the normal native wrap MySQL drivers to a pure python one. The pure python MySQL drivers were marginally slower fo

I once speeded up a program by changing from the normal native wrap MySQL drivers to a pure python one. The pure python MySQL drivers were marginally slower for cpython. The native drivers worked fine with pypy, but pypy didn’t give any real performance gains as the program spent all its time in the native code Then when testing pure python drivers we found pypy made the whole program massively faster.

qeternity 5y ago
That’s surprising if your app spent most of the time in database calls, since 1) native code is still faster than pypy and 2) most of the time spent in database libraries is spent waiting for the database to return.
- dragonwriter 5y ago
  > That’s surprising if your app spent most of the time in database calls, since 1) native code is still faster than pypy and 2) most of the time spent in database libraries is spent waiting for the database to return I would argue a speedup (though maybe not a “massive” one) from pure python over a native extension is not that surprising, specifically because of (2), which makes it possible for the runtime-extension interface overhead to be more significant than (1). Native extensions tend to give a speed boost for computationally intensive tasks, but can’t really wait any faster, and aren’t free of overhead.
- willvarfar 5y ago
  More details of my use case: The programs were ETLs that are generating big REPLACE INTO statements. Different databases and drivers do prepared statements in different ways. Postgres, for example, can receive the statement and the parameters and do the interpolation in the server. The params still need to be serial used by the client and the template isn’t reused, so it’s no big perf win. MySQL, on the other hand, needs the driver to do the interpolation. The server only knows how to execute sql strings. It turns out it is far better to prepare the sql in pure python where pypy knows all about types etc than to have the c MySQL driver do it. Exactly the same thing plays out in java. The MySQL java drivers are staggeringly inefficient at large prepared statements, and in my profiling massive amounts of time is spent in java functions in the MySQL driver that are trying to work out where the next question-mark is. Sadly the drivers are the poo they are. Iirc the mariadb java drivers are rather more optimized.
zeta0134 5y ago
Interesting. What sort of optimizations does PyPy do? Could it be that pure python unlocked more options in that department due to not needing to preserve the native bindings?

qeternity 5y ago

That’s surprising if your app spent most of the time in database calls, since 1) native code is still faster than pypy and 2) most of the time spent in database libraries is spent waiting for the database to return.

dragonwriter 5y ago
> That’s surprising if your app spent most of the time in database calls, since 1) native code is still faster than pypy and 2) most of the time spent in database libraries is spent waiting for the database to return I would argue a speedup (though maybe not a “massive” one) from pure python over a native extension is not that surprising, specifically because of (2), which makes it possible for the runtime-extension interface overhead to be more significant than (1). Native extensions tend to give a speed boost for computationally intensive tasks, but can’t really wait any faster, and aren’t free of overhead.
willvarfar 5y ago
More details of my use case: The programs were ETLs that are generating big REPLACE INTO statements. Different databases and drivers do prepared statements in different ways. Postgres, for example, can receive the statement and the parameters and do the interpolation in the server. The params still need to be serial used by the client and the template isn’t reused, so it’s no big perf win. MySQL, on the other hand, needs the driver to do the interpolation. The server only knows how to execute sql strings. It turns out it is far better to prepare the sql in pure python where pypy knows all about types etc than to have the c MySQL driver do it. Exactly the same thing plays out in java. The MySQL java drivers are staggeringly inefficient at large prepared statements, and in my profiling massive amounts of time is spent in java functions in the MySQL driver that are trying to work out where the next question-mark is. Sadly the drivers are the poo they are. Iirc the mariadb java drivers are rather more optimized.

zeta0134 5y ago

Interesting. What sort of optimizations does PyPy do? Could it be that pure python unlocked more options in that department due to not needing to preserve the native bindings?