All geek questions in one place

MySQL / MariaDB runs a single query on multiple cores

I have a decently strong database server with 24 cores and 48 GB of RAM. We are launching MariaDB 10.0 as our database engine. All my tables work on the InnoDB engine. I have several queries that use fairly extensive joins on large tables and are therefore slow. One thing I'm struggling to do is try to use the power of several cores. Here are my observations:

  • If I run 2 queries in parallel, then I can use 2 cores
  • If I run 1 request, I see only 1 core used at 100%

Now, is this normal behavior? I mean, in MariaDB / MySQL there is no way to use more than one core for a single query? It will be great if one heavy request can run faster using multiple processor cores.

I tried to find several forums and played with various parameters, such as Innodb_thread_cocurrency, but still could not start one request on several kernels.

Is there any other engine that can help me (I'm reading about XtraDB right now)?

UPDATE:

Well, actually I'm trying to analyze data on social networks. So imagine a database with 2-3 million tweets. Naturally, we are talking about related tables with USER data, hashtags, images, links, etc. Thus, everything as a whole is a pretty decent database. In some cases, there are queries that require multiple joins across multiple tables and are naturally slow. So imagine, for example, a dashboard query with 5-6 different queries. When one user logs in, this puts a strain on one core, and the entire core is used for n seconds. If I have 12 cores with me, and suppose that 12 users are simultaneously trying to access the system, then I have a serious bottleneck. I fully understand that options like shards, clustering,distributed db, partitioning can help me. But now I'm trying to figure out how best to scale the system vertically before I look at horizontal scaling (in which I represent more servers). Using multiple cores would be a great option, but I think now I understand that MySQL is essentially not designed as such. I think I'll start looking at the various [possible architectures for scaling a database across multiple instances.that I’ll start to look at the various [possible architectures for scaling a database across multiple instances.that I’ll start to look at various [possible architectures for scaling a database across multiple instances.

+4
2

, . , :

1) MariaDB MySQL ( , ). , .

2) (DB2, Oracle, SQLServer, Postgres ..), , .

3) , , , . , " , , ". , " ".

4) Bottelnecking , . , , ( , ) , .

5) Parallelism concurrency. - (.. concurrency), ( ). , . - , OLTP , OLAP .

!

+8

Mjh, CPU , .

InnoDB . , - ( , , -, , IO ).

+3

Source: https://habr.com/ru/post/1611728/

More articles:


All Articles