Speed / tune your system.

Discussions about the usage of KeyHelp.
Post Reply
User avatar
BasHeijermans
KeyHelp Translator
Posts: 158
Joined: Mon 20. Jun 2022, 12:01
Location: Heppen Belgium
Contact:

Speed / tune your system.

Post by BasHeijermans »

Hi all,

As you may or may not know, MariaDB has a big impact on the speed of your system and how fast websites respond.
My VPS has 2GB ram, and without tuning MariaDB (Mysql = same) it can make a difference between crawling or go as lightning.

The key here is in /etc/mysql/mariadb.conf.d/50-server.cnf and a few settings. I know this from a long time ago when I was running a 512MB server with millions of users and database that was several GB's in size.
Yes it works, yes it can be done.

In the file you find a few settings that handle this, if you don't adjust them the result is bad, and lot of unused memory to burn and a slow system.
Or on a low memory system, it burns memory and starts swapping, result is just as worse. :D

Unrem keybuffer, make it at least 25% of the size of your database, if you have plenty memory, 100% of the size. As this buffer stores parts or the whole database in memory (for reading). More is fast, but beware it doesn't start to swap, that is bad.

Second, thread_stack, unrem it, and anywhere between 128K to 256K should be fine.
Third, thread_cache_size, 8 to 16 usually works.
Forth, table_cache, 64 to 128 is often enough.

And last, query_cache_size, 16M is enough for most unless you have a lot of repeating queries, then a higher number is needed.

I'm not saying this will work for everybody, but on small systems where memory is key, this will help. On systems with loads of memory, often the memory isn't used properly and large database will suffer.

These are the few, of many parameters, the impact of these few is huge.

Enjoy.

Bas..
Greetings Bas.

Ik heb KeyHelp naar het Nederlands vertaald, contacteer me als er translatie fouten zijn.
(I have translated KeyHelp into Dutch, contact me if there are translation errors.)
Next version 24 of KeyHelp 100% translated.
User avatar
Alexander
Keyweb AG
Posts: 3810
Joined: Wed 20. Jan 2016, 02:23

Re: Speed / tune your system.

Post by Alexander »

As a note, KeyHelp installs a config called tuning.cnf which improves various settings.

Files can be found here:

On MariaDB: /etc/mysql/mariadb.conf.d/90-tuning.cnf
On MySQL: /etc/mysql/conf.d/90-tuning.cnf

There are two variants of this file, one for

Less than 2 GB Ram

Code: Select all

#
# KeyHelp Tuning Settings
# System Memory: < 2 GB
#

[mysqld]

# MySQL Tuning
max_connections         = 100
key_buffer_size         = 64M
max_allowed_packet      = 16M
thread_stack            = 256K
tmp_table_size          = 128M
max_heap_table_size     = 128M
thread_cache_size       = 150
table_cache             = 8192
table_definition_cache  = 4096
open_files_limit        = 16544

# InnoDB Configuration
innodb_file_per_table   = 1
innodb_buffer_pool_size = 128M

# Query Cache Configuration
query_cache_limit       = 64M
query_cache_size        = 128M
query_cache_type        = 1

2 GB Ram and more

Code: Select all

#
# KeyHelp Tuning Settings
# System Memory: < 2 GB
#

[mysqld]

# MySQL Tuning
max_connections         = 200
key_buffer_size         = 250M
max_allowed_packet      = 16M
thread_stack            = 256K
tmp_table_size          = 128M
max_heap_table_size     = 128M
thread_cache_size       = 150
table_cache             = 8192
table_definition_cache  = 8192
open_files_limit        = 16544

# InnoDB Configuration
innodb_file_per_table   = 1
innodb_buffer_pool_size = 500M

# Query Cache Configuration
query_cache_limit       = 64M
query_cache_size        = 512M
query_cache_type        = 1

Feel free to create and post your own variation of these files that you think will work better.
Maybe we can come out with an improved version of these files here :).
Mit freundlichen Grüßen / Best regards
Alexander Mahr

**************************************************************
Keyweb AG - Die Hosting Marke
Neuwerkstr. 45/46, 99084 Erfurt / Germany
http://www.keyweb.de - http://www.keyhelp.de
**************************************************************
User avatar
24unix
Posts: 1560
Joined: Sun 21. Jun 2020, 17:16
Location: Kollmar
Contact:

Re: Speed / tune your system.

Post by 24unix »

Alexander wrote: Wed 26. Oct 2022, 10:05 Feel free to create and post your own variation of these files that you think will work better.
Maybe we can come out with an improved version of these files here :).
I think this one is always advisable:

https://github.com/BMDan/tuning-primer.sh
mfg Micha
--
If Bill Gates had a nickel for every time Windows crashed …
… oh wait, he does.
Post Reply