#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0

    MySQL Multithreading


    Hello,
    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 ?
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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.
    /Stefan
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,296
    Rep Power
    7170
    If you have to run 3000 select queries in a second I would question whether or not those actually need to be separate queries.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0
    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 ?
  8. #5
  9. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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.

    Threads:
    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.

    Prepared statements:
    Use prepared statements for recurring queries.
    /Stefan
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0
    A small question, do you have a good reason to store the BLOB's in the database?
    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.

IMN logo majestic logo threadwatch logo seochat tools logo