MySQL memory settings
What are the best memory settings and how do I adjust them for a MySQL server running on Mac OS? I use both MyiSAM and InnoDB tables.
Pawan Kumar
You can refer this article for optimal memory settings - https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Tips for memory tuning.
Summary: Use MariaDB + run mysqltuner repeatedly, over long periods of time.
1) Use mysqltuner (always latest version) + clear up most diagnostics. A good bit of research will be required to determine how best to tune.
For example, I only run InnoDB + never MyISAM, as most client sites I host have extremely high traffic.
So I run query_cache_size + query_cache_type both set to 0, as lnnoDB performance testing shows better performance with these off.
2) Deinstall all mysql packages for your Distro + install all MariaDB packages from the MariaDB Official Repositories which will likely give you a big performance boost + allow you to use all modern tunings.
MariaDB == MySQL that works. https://mariadb.org/about/
I've been running MariaDB exclusively for... geez... 4-5 years now + it just works, with none off the odd MySQL behavior, I was seeing before with high traffic sites... like threads getting stuck + unstuck... randomly...
3) The only way to target correct memory settings is to continually run mysqltuner, starting with daily, then move to weekly.
Over time, statistics collected allow mysqltuner to make more intelligent suggestions.
The longer you run + the better your tuning.
4) As site(s) traffic patterns change, this may substantially change your tunings.
5) You mention using MyISAM. Likely best you run some sysbench checks to prove to yourself InnoDB is way faster than MyISAM.
And, this only applies if you're using latest MariaDB. The MySQL code base... is... well... to me, badly broken. You can also prove this to yourself by attempting to build the code base from scratch + seeing that usually 60%+ of build tests fail.
With MariaDB, source builds yield 100% pass rate, which is what should occur.
If you're using FTS (Full Text Searching), MariaDB has supported this with InnoDB (use to only work with MyISAM) for years now.
Summary: Use MariaDB + run mysqltuner repeatedly, over long periods of time.
1) Use mysqltuner (always latest version) + clear up most diagnostics. A good bit of research will be required to determine how best to tune.
For example, I only run InnoDB + never MyISAM, as most client sites I host have extremely high traffic.
So I run query_cache_size + query_cache_type both set to 0, as lnnoDB performance testing shows better performance with these off.
2) Deinstall all mysql packages for your Distro + install all MariaDB packages from the MariaDB Official Repositories which will likely give you a big performance boost + allow you to use all modern tunings.
MariaDB == MySQL that works. https://mariadb.org/about/
I've been running MariaDB exclusively for... geez... 4-5 years now + it just works, with none off the odd MySQL behavior, I was seeing before with high traffic sites... like threads getting stuck + unstuck... randomly...
3) The only way to target correct memory settings is to continually run mysqltuner, starting with daily, then move to weekly.
Over time, statistics collected allow mysqltuner to make more intelligent suggestions.
The longer you run + the better your tuning.
4) As site(s) traffic patterns change, this may substantially change your tunings.
5) You mention using MyISAM. Likely best you run some sysbench checks to prove to yourself InnoDB is way faster than MyISAM.
And, this only applies if you're using latest MariaDB. The MySQL code base... is... well... to me, badly broken. You can also prove this to yourself by attempting to build the code base from scratch + seeing that usually 60%+ of build tests fail.
With MariaDB, source builds yield 100% pass rate, which is what should occur.
If you're using FTS (Full Text Searching), MariaDB has supported this with InnoDB (use to only work with MyISAM) for years now.
ASKER
Thanks Arnold! That helped.