Menu
I have a decently strong database server with around 24 cores and 48 GB of RAM. We are running MariaDB 10.0 as our database engine. All my tables are running on InnoDB engine.I have a few queries that employ rather extensive joins over large tables and hence are naturally slow. One thing I am struggling to do is to try and leverage the power of multiple cores. Here are my observation:
Now, is this the normal behavior? What I mean is that is there no way in MariaDB/MySQL to utilize more than one core for a single query? It will be great if a single heavy query can run faster by utilizing multiple CPU cores.
I did try and search multiple forums and have played with different parameters like Innodb_thread_cocurrency, but so far haven't been able to run a single query on multiple cores.
Is there any other engine which can help me do that (I am currently reading about XtraDB)?
UPDATE:
Well, actually I am trying to analyze social media data. So imagine a database with 2-3 million tweets. Naturally we are talking associated tables with USer data, hashtags, images, links etc. So all in all a decently large database. In certain cases there are queries which require some joins on multiple tables and are naturally slow. So, imagine , for instance a dashboard query with 5-6 different queries. When a single user logins, this puts load on a single core and the entire core is utilized for n seconds. If I have 12 cores with me, and suppose 12 users simultaneously try to access the system, then I have a serious bottleneck. I understand completely, that options like sharding, clustering, distributed DB, partitioning could help me. But right now I am trying to understand how best to Scale a system vertically before I look at horizontal scaling (wherein I introduce more servers). Utilization of multiple cores would have been a great option, but I guess now I understand that, MySQL isn't inherently designed as such. I think I will start looking at various [possible architectures to scale the DB over multiple instances.
758
The short answer is yes. It will use a thread per connection by default which you can delegate queries to concurrently, which will utilize multiple cores on the operating system level.
Yes. MySQL is fully multithreaded, and makes use of all CPUs made available to it. Not all CPUs may be available; modern operating systems should be able to utilize all underlying CPUs, but also make it possible to restrict a process to a specific CPU or sets of CPUs.
MySQL server uses only 1 out of 48 CPU cores with GROUP BY queries.
Though this thread is more than half a year old, some of the comments raise some concern. As a hardcore database performance tuner, I'd like to add a little input here:
1) As of this writing, MariaDB and MySQL do not support running a single query on multiple processors ( unless you are talking about sharding of course). It does not mean they won't support it in the future.
2) Parallelizing query can be done in other RDBMS ( DB2, Oracle, SQLServer, Postgres, etc), and it's an important feature for environment such as data warehouse.
3) For long running query, more often than not, parallelizing it would shorten the execution time, though the improvement may not be linear. So, it is incorrect to say that 'it does not make sense one query uses multiple cores to make it work faster'. It makes perfect sense for certain workload, unless we have a different definition of 'work faster'.
4) Bottelnecking CPU does not necessarily imply 'you are using database the wrong way'. Yes, it's true that some people do write horrible query ( or procedure that drive queries) and choke the database to death, but that's a different issue altogether.
5) Parallelism would reduce concurrency. If your goal is to support more users (ie, higher concurrency), you will have to give up running query on multiple processors ( or just running on less processor per query). It's a trade off you will need to make. At the risk of over-generalizing, you would want OLTP environment query to run in serial,and OLAP query to run in parallel.
Cheers!
127
As Mjh explained, if CPU is the bottleneck, then you must be using your database the wrong way.
InnoDB does not support parallelisation of a single query. You might be able to achieve some kind of parallelisation by spreading your tablespaces across several physical drives (but I am not even sure performance is the primary target for this feature, and if this improves anything, then it does when IO is the bottleneck).
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
