maria-discuss team mailing list archive

[Guillaume Lefranc <guillaume@xxxxxxxxxxxx>]

Hello,

thread-handling=pool-of-threads
> max_connections = 1000
> table_open_cache = 800
> query_cache_type = 0
> innodb_buffer_pool_size = 512M
> innodb_buffer_pool_instances = 10
> innodb_adaptive_hash_index_partitions = 20
> innodb_lock_wait_timeout = 5000
>
No need to use buffer pool instances with only 512M of buffer. you said you
have 24GB of RAM - why not increase the buffer size? how big is your table
on the disk right now? If you want the best performance it must be hold in
the buffer.

>
> With this, my application can keep up with the FIFO writer, but -
> depending on the circumstances - my database can't. As I am writing this,
> there are over 1300 threads connected to my database; any command that I
> issue at the mysql CLI takes over one minute to return. I am keeping track
> on how long each thread takes to complete, and that is of the order of
> hundreds of seconds - sometimes thousands. Each thread is itself simple, in
> that it just issues a couple of simple MariaDB commands.  Currently my
> table consists of 1.6 million entries, and growing - on this basis, I
> expect that things will get only worse. Each entry,however, will never
> require more than a couple of hundred bytes of storage. The operations that
> can be undertaken on entries are insertion, deletion and modification, the
> latter being straightforward - like e.g. incrementing a counter or
> replacing a short string.
>

You are not considering many factors which is
a) limiting the number of concurrent threads - with 1300 threads you are
creating concurrency races and locking issues. Try limiting your threads to
a factor of the # of cores
b), you're writing to a disk system. The number of CPU cores won't matter
if you saturate the disk. You say nothing about the disk, if it's SSD, HDD
etc. Note that HDD's are severely handicapped when it comes to concurrent
IO operations.
c) given the factor above you could maybe try relaxing commit to disk if
integrity of the data is not of utmost importance, for example by adding
"innodb_flush_log_at_trx_commit = 2" to your config.

My system has 24 GB of  RAM and 12 cores. Occasionally all the cores are
fully busy with MariaDB activity, but most of the time barely one or two
are.

d) CPU in a DB system will mostly be used for queries (index scans) and
some for reindexing but you're only doing writes, so unless you have dozens
of indexes
e) Table design - is your table design efficient ? how many rows per sec
are you writing? maybe you could also benefit from hash partitioning or
clever data types.

>
> I am a newbie when it comes to interacting with MariaDB - please,  bear
> with me. I know I must use a single database and a single table. I also
> know - because of the nature of the data that are being written to the FIFO
> - that the  probability for two different threads to be operating on the
> same entry in the table at the same time is negligible - i.e. for all
> practical purposes, that will not happen.
> What I need is advice on how to configure my instance of MariaDB to
> perform optimally in the scenario above. In particular, I would like for it
> to make better use of all the cores available - in essence, to parallelize
> the database operations as much as possible.
>
> f) Congratulations, you have managed to contradict yourself in two
sentences. If your workload isn't parallel by design you will not make use
of the available cores. Also, your workload sounds IO-bound to me - there's
a strong change the disk is the bottleneck.
g) "I know I must use a single database and a single table." How do you
know this if you are a newbie? No offense meant but nost of monolithic
design is not the best to leverage performance....

-GL