CODEWITHSUNDEEP
singlestore
Kai Wachter
Kai Wachter

Reputation: 41

Slow performance when inserting records to memsql

When executing an insert or even a select statement using C# against a MemSQL installation it is very slow. Queries executed on the database server are incredibly fast.

So something between query code and MemSQL is not performing. When executing the sample insert program, it just inserts 40- 60 records per second, but when doing the speed test, it inserts 2.2 million per second. So the problem needs to be somewhere else. Any ideas?

Upvotes: 2

Views: 1480

Answers (2)

Mani Gandham
Mani Gandham

Reputation: 8292

The issue is the MySQL protocol itself. This connection (and pretty much all relational database wire protocols) require the query to be sent, processed and then received by the client for full completion.

This means that a single thread running queries sequentially has to wait for every single query to do a full roundtrip. You can use multiple threads to improve throughput by making several queries in parallel (recommended threads are equal to cores available). You can also use async methods so that the processing is as fast as possible and only waiting on I/O.

You can also batch together several INSERT statements into a single query which will allow for much more throughput.

Using all these techniques together will get you much more performance.

Upvotes: 1

Jack Chen
Jack Chen

Reputation: 1214

It sounds like the bottleneck is something in the way you're connecting/running queries. For example, you might be running just one insert at a time, in which case all the time is spent waiting on round-trip connection latency. The first things I'd suggest trying, if you aren't already, are inserting multiple rows per query and running multiple queries concurrently. See http://docs.memsql.com/latest/concepts/multi_insert_examples/#c for some examples. If you are already doing those, can you share more details of how you are running your test?

Upvotes: 1

Related Questions