General performance is lower than expected compere to PHP

[anking]

trafficstars

This is not really a bug in MySql connector but rather an observation I made when converting legacy PHP code to the .net core 5.0.

I've took one of the PHP API requests and rewrote it with all processing and queries done by MySqlConnector. The request makes one giant query first(with tons of joins) and then based on the results of it it makes a bunch of smaller queries (~300).

Results: PHP(mysqli) big query ~40ms, small queries ~775ms, total request fetch time ~850ms (pretty consistent) C#(MySqlConnector) big query ~45ms, small queries ~1050ms, total request time ~1200ms

These are the best results I was able to come up to so far.

At first I was creating a new connection for every query(due to a bunch of nested calls). Then I got rid of that and started reusing a single connection for all of then that saved me ~200ms overall request timing (used to be ~1400ms).

I also attempted to use parallel execution but ran into a problem when MySql was erroring out on "too many connections".

I also tried to use Unix socket(as this is what PHP currently using) to connect to DB instead of TCP but this didn't give any noticeable performance increase.

Another observation: whereas PHP request timing stays consistent ~850ms, netcore has a bit of warmup time after a break(this is where the request total execution time goes up to 2000ms, this is not caused by MySQL as the requests timing still remains the same)

I used best practices in terms of async/await code execution as outlined in documentation.

I actually expected much better results from compiled C# code compare to interpreted PHP. But thing that still bothers me in particular is why the requests are in general slower about 30%? Is this something I may have overlooked configuration-wise that is done in PHP but not default in MySqlConnector?

OS Ubuntu Linux 64bit PHP v7.0.32 mysqlInd 5.0.12 MySQL v 5.7.24

netcore5.0

[bgrainger]

I used best practices in terms of async/await code execution as outlined in documentation.

FWIW async/await is a best practice for throughput and scalability, but it's slower in terms of per-request latency (see the benchmark charts in the README).

Are you able to provide any sample code?

Without seeing any code, my guess would be: The C# code is doing more than the PHP code is, and that's why it takes longer. This may be hidden from you, due to different approaches mysqli (or PDO) and ADO.NET take. mysqli tends to be more low-level; ADO.NET is a little higher-level and may have a bit more overhead. In particular, C# async code adds some overhead (as mentioned). Secondly, MySqlConnector has some "safe" defaults such as Connection Reset = True that you have to opt out of for highest performance.

[anking]

@bgrainger Thanks for a quick reply!

Code sample might be quite big to include but it basically breaks breaks down to multiple nested loops that have this sample inside them: https://mysqlconnector.net/tutorials/basic-api/

Since same connection cannot be reused while readers are still open I have to read them thru and close before the next part can happen. But other that that it is very simple, just a bunch of simple queries ported directly from PHP code.

I believe you right about a lot of extra overhead that C# is doing compare to PHP and I'm trying to figure out if there is a way to configure it in such way that it avoids it. Or should I use a different driver for it? You mentioned PDO? is there a .net alternative for it maybe?

In fact I already tried the MySql.Data library and got very similar results. Big query ran with the same speed and bunch of smaller ones averaged about ~1060ms(about 10 ms slower than MySqlConnector, but could be within permissible measurements error)

This request I'm working on is very timing sensitive and I have to make it so it works at least not slower than PHP

[bgrainger]

multiple nested loops that have this sample inside them: https://mysqlconnector.net/tutorials/basic-api/

Are you creating and disposing MySqlConnection objects multiple times through the loops? If so, that sounds unnecessarily inefficient. Opening one connection at the top of the method and closing it at the end would likely be better.

Since same connection cannot be reused while readers are still open I have to read them thru and close before the next part can happen.

This is the expected behaviour (at least for C#). Is the PHP code different? Does it not read the results?

[ghost]

Don't get carried away by "overhead" of c#; the overhead is not in the milliseconds range. At that levels, the difference most likely comes from your code. For reference most of my c# code calling into mysql returns in less than 1ms iirc.

Connections not supporting multiple threads at the same time comes from the db engine and the wire protocol actually. sql server had mars support long ago to accommodate a somewhat close use case. don't think mysql has anything close.

And most of the time you shouldn't need this; just open multiple connections separately and execute concurrently; that's what i do quite often. just be careful ab reusing connections.

You don't need to close/reopen to execute the next reader; just use the connection as is. at most you might need to reset it to clear out the session.

If you could post some pseudo-code of what you're doing you might get more better feedback.