Optimize Mysql

[ericosman]

Hi all!

I ran the mysql tuner
And it gave me the following:

skip-name-resolve=ON
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache (400) > 3122 or -1 (autosizing if supported)
performance_schema=ON
key_buffer_size (~ 27M)
innodb_buffer_pool_size (>= 843.6M) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.

Well, i could just "copy and paste" it in my.cnf but i think this would be good? (*note i could be an idiot with these vallues) :

join_buffer_size = 512M
tmp_buffer_size = 64M
max_heap_table_size = 64M
table_definition_cache = 5120
performance_schema = ON
key_buffer_size = 2GB
innodb_buffer_pool_size = 2GB
innodb_log_file_size = 1GB

I have 30 GB op RAM on my server.

Thanks!

 

[Zhenyapan]

join_buffer_size = try don't touch first time
tmp_buffer_size = 64M OK
max_heap_table_size = 64M OK
table_definition_cache = 5120 OK
performance_schema = ON as you wish
key_buffer_size = 32M no sense set it bigger than needed.
innodb_buffer_pool_size = 1GB no sense set it bigger than needed.
innodb_log_file_size = 128M

 

[BillyS]

Do NOT set this value:

join_buffer_size = 512M

Where did you even come up with that number? That is a per connection value.

What version of mariadb / mysql are you using? How large is the database and server? Are all the tables innodb?

See here:

MySQL Memory Calculator

www.mysqlcalculator.com

Check this out too

To see all MariaDB default values: https://mariadb.com/kb/en/server-system-variables/

I would suggest that you not change a variable without first learning what it does and how it is used. Generally, the default values today are very good.

 

[Zhenmue]

Hi all!

I ran the mysql tuner
And it gave me the following:


Well, i could just "copy and paste" it in my.cnf but i think this would be good? (*note i could be an idiot with these vallues) :


I have 30 GB op RAM on my server.

Thanks!

mysql tuner told you:
join_buffer_size (> 256.0K

higher than 256k
why you used 512M? it's a very big jump