1

I've been setting up a new VPS and wanted to try out MariaDB. I'm using MariaDB 10.0.1, which as far as I understand it is the equivalent of MySQL 5.6.

Has the thread handling of threads changed dramatically since MariaDB/MySQL 5.5? This is what I see on my old server (CentOS 5.9, MySQL 5.5):

mysql 5.5 on centos 5.9

And on Centos 6.3 with MariaDB 10 (MySQL 5.6):

mariadb 10 on centos 6.3

Here's a list of facts:

On Server A (CentOS 5.9, MySQL 5.5):

  • There are about 15 databases hooked up to various websites and services
  • Plesk is installed
  • MySQL is minimally tuned:

/etc/my.cnf

[mysqld]
local-infile=0
query_cache_type = 1
query_cache_size = 32M

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Misc vars
key_buffer_size=32M
join_buffer_size=512K
tmp_table_size=32M
max_heap_table_size=32M
thread_cache_size=4
table_cache=300

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# InnoDB vars
innodb_buffer_pool_size=96M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

On server B (CentOS 6.3, MariaDB 10):

  • There are 3 databases, only one of which is currently "in use" and hooked up to a low traffic site.
  • No Plesk.
  • MariaDB minimally tuned:

/etc/my.cnf.d/server.cnf

[mysqld]
# threads
thread_concurrency=2
thread_cache_size=1
thread_handling=one-thread-per-connection
thread_pool_size=4
thread_pool_max_threads=4
thread_pool_idle_timeout=60
thread_stack=240K

# Limit Connections?
# max_connections=5

skip-external-locking
key_buffer_size=64M
max_allowed_packet=1M
table_open_cache=128
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=4M
net_buffer_length=8K
myisam_sort_buffer_size=32M
query_cache_size=16M

# innodb settings
innodb_buffer_pool_size=32M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=30
innodb_thread_concurrency=0

Why are there so many threads? I've tried many settings to try and get the number of process threads down to a reasonable level, but I can't seem to affect it. It's always using 20 or 21 threads. I was able to cut down on the memory usage by adjusting innodb_buffer_pool_size, but 32M isn't a reasonable value with 10+ sites running, so I'll be increasing it back up to 96M or 128M. At those values the ram used by mysql exceeeds 750-850M memory.

If this is just something I have to live with, that's fine (I've got more ram on the new VPS, YOLO), but I'm just curious why there's such a huge discrepancy in the memory usage.

Also worth mentioning is that if I shut mysql off on both VPS, I have almost an equal amount of ram used-- ~300M for A, ~260M for B.

Brendan
  • 145
  • 2
  • 10

1 Answers1

5

MySQL should use as much as free memory you have. The number of threads at this scale is very small and it doesn't affect the memory usage. The threads are sharing the same virtual memory space. They use only a few KB for thread metadata.

The memory usage on the new MySQL is actually smaller than before. It has allocated in the virtual memory space 1.1GB, but it has only 60MB in physical memory.

When looking for optimizing MySQL, fist try to move the bottleneck from disk I/O to memory access. Optimize also the queries (rewrite them, indexes) - enable MySQL slow query log.

Sometimes you hit the hardware limit, and the only optimization is to upgrade the hardware. For MySQL, the first thing is to add more RAM, faster disks, then more CPUs.

Mircea Vutcovici
  • 17,619
  • 4
  • 56
  • 83
  • OK, then how come the total ram usage is far higher on server B? Is this just how CentOS 6 counts memory vs CentOS 5? – Brendan Feb 11 '13 at 16:44
  • Also, I'm a bit leery letting mysql use "as much free memory as I have" since this is a full-stack web server, not a database-only server. – Brendan Feb 11 '13 at 16:44
  • How do you obtain the memory usage? Could you please post the output of `free`? – Mircea Vutcovici Feb 11 '13 at 16:47
  • 3
    The total RAM usage is far higher on server B because the newer, superior versions were able to take advantage of more of the available resources while the older, dumber server let them go to waste. Don't think "I want my RAM to be free now, in case I need to use it later". You can use it now *and* use it later. You don't have to compromise. – David Schwartz Feb 11 '13 at 16:48
  • What kind of VPS are you using? – Mircea Vutcovici Feb 11 '13 at 16:48
  • I have a 4GB mem/16core parallels container based VPS. – Brendan Feb 11 '13 at 17:02
  • Some performance problems could be caused by the other VMs. Do you have access on the physical server? Which performance problem are you experiencing? – Mircea Vutcovici Feb 11 '13 at 17:35
  • No, and no performance problem, I was just curious... – Brendan Feb 11 '13 at 17:50