maria-discuss team mailing list archive

[Rohan M C <rohanmc@xxxxxxxxx>]

Hi All,

We're considering a switch to mariadb (5.5.33a) from mysql (5.5.31) for our
production servers. First, some background info about our system

1) ~2000 tables. 3/4th of them are innodb, the rest are myisam
2) All tables are partitioned with ~100 partitions each
3) our largest tables are partitioned (~200 partitions each) and they're
all myisam due to space constraints (and other reasons)
4) all our instances are on amazon ec2 on ephemeral store

Some of our config parameters
open-files-limit        131072
innodb-open-files        16384
table-open-cache         4096
innodb-buffer-pool-size 256M

One of the problems we're facing with mysql, and we're hoping to solve with
mariadb, is the blowup of the number of open file descriptors when some of
our long-running/heavier scripts run. So far we've been able to keep it in
control by having our scripts flush at intervals, as well as making
individual threads hit different read slaves so as to not open up mulitple
copies of myisam fds on a single machine. I do know that the above
parameters could be tweaked to set a cap on the number of file descriptors
(innodb seems to be a hard limit, so thats under control), but we've set
table-open-cache at a high number for performance reasons. Our ideal
solution would be to keep the above parameters and not run into a situation
where the number of file descriptors grow uncontrollably beyond 70k-80k. It
would be preferable to have mariadb close file descriptors once queries are
done.

We were interested in the mariadb threadpool option as it launches threads
dynamically when needed and closes them once it's done. I'm currently
testing on a mariadb instance with the default parameters. One of my
assumptions with threadpools was that when additional threads were closed,
the file descriptors opened for them would also be closed, but upon testing
I've seen that this is not the case, and that mariadb behavior as far as
threadpools are concerned is exactly the same as mysql.

For example, with thread_pool_size=1 and max_threads at 500, if I run 10
identical concurrent queries from different connections on mariadb on a
myisam table, it opens up 10 sets of 200 file descriptors.
Threadpool_threads spikes up to 10 and drops back down after some time, but
all the file descriptors initially opened are still in the open state (2200
fds ). Is this intended behavior for threadpools?

I would also be grateful for any tips, reading material or knowledge
regarding how mariadb handles file descriptors and if there are any
additional parameters that can be tuned to keep the number of file
descriptors under control, while not impacting performance too much

Thanks in advance.

Rohan