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:
[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:
[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.