July 30th, 2013, 11:21 AM
I'm writing an application, which should sends up to 3000 querries in a second to a mysql-server.
There are no other users than my application.
Which is the most efficient way to do it ?
1. Sending all querries via one database-connection.
2. Creating several threads,each with one database-connection,and distribute the querries to the thread.
3. Creating several threads,each with one user and a database -connection,and distribute the querries to the thread.
Which is the best way if using mariaDB ?
July 30th, 2013, 01:35 PM
You didn't mention if these are insert/update/delete or selects so I'm going to assume that almost all queries are selects.
Which would recommend: Alt 2
And a rough rule of thumb would be that the total number of active parallel threads should roughly correspond with how many cores you got on the machine.
July 30th, 2013, 08:27 PM
If you have to run 3000 select queries in a second I would question whether or not those actually need to be separate queries.
July 31st, 2013, 07:16 AM
Thank you for your answers.
Most of the queries are like:
SELECT attribute WHERE primary key =
The attribute is in most cases a BLOB which has a size on average between 100K and 5 Megabyte .
So I think parallel processing of queries would be a speed advantage.
But how can I calculate the limit ?
The more threads are used the more the system decelerates because the threads administration.
But the dbms has to:
Checking the db-user's rights.
Positioning the table's iterator.
Accessing its filesystem and reading the data
How many time needs that compared to the administration of threads ?
How it is about HEAP tables ?
I plan to use HEAP table to use as a kind of directory. Each record has the same size. It is faster to use a single connection and to serialize the queries or using threads assuming that each connection uses its own iterator ?
July 31st, 2013, 09:52 AM
HEAP tables does not support BLOB's and I generally recommend against using HEAP tables in your application unless they are absolutely necessary since using them adds complexity and usually not so much gain compared to improving other areas of the application.
A small question, do you have a good reason to store the BLOB's in the database?
Reason I'm asking is that retrieving files from the file system is usually much faster since you avoid the overhead from placing them in a database. But there are some advantages to keep them in the database also so I want to make sure that you know why you are doing it.
Some more questions:
Do you actually expect 3000 qps on average?
And how large in MB or GB will the database be?
Because 3000 qps returning on average for example 1MB BLOB = 3GB/s in just data transfers.
Which means that you need about three 10 GBPS interfaces to just keep up with the data flow out of the box. And depending on the size of the database things like RAM and storage systems etc need to be carefully chosen.
That's some serious hardware requirements.
So the question is are you really expecting this volume or are they more guesses than estimations? I'm just asking because I've so many times heard extreme requirements while the production volume is actually just a fraction which means a lot of hardware and development time has been wasted on something that never was needed in the first place when it could have been put to much better use on other places.
I MySQL one query can only be handled by one thread so parallel execution is needed to get high performance.
But at the same time if you can keep down the amount of queries you reduce the per query overhead like parsing, optimizer evaluation etc.
Try to use as many threads needed so that all cores on the machine are used. Maybe about 1x or 2x the amount of cores you got on the DB machine.
Use prepared statements for recurring queries.
July 31st, 2013, 10:15 AM
The idea of the application is a storage system.
The heap table is on one server and the BLOBs on an other.
The heap table contains the information where yo can find the BLOBs.
Thank you, your answer helped a lot.