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):
And on Centos 6.3 with MariaDB 10 (MySQL 5.6):
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.
Best Answer
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.