The script do not working with mariadb 10.4

at z_aiops_mysql.conf
{"message": "Internal server error"}

Maybe at the bottom add a comment of what has changed

changed

table_open_cache = 512 ### Previous value : 431

table_definition_cache = 640 ### Previous value : 524288

Test the script on server with more resources ( CPU and RAM )

Some times mysql runs in docker container with memory-limits (-m flag)
and has no access for all server RAM
https://docs.docker.com/config/containers/resource_constraints/

There are two ways for work around

1. Ask for current limit and get recommendations that will meet the limits
2. Make recommendations for increasing container memory-limits

Update configuration file example in Readme.md

Linux version: Amazon Linux 2

Mariadb 10.3.27

Errors:

It is extremely important for you to fully understand each change you make to a MySQL database server. If you don't understand portions of the script's output, or if you don't understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust. Always test your changes on staging environments, and always keep in mind that improvements in one area can negatively affect MySQL in other areas.

It's also important to wait at least a day of uptime to get accurate results. In fact, running mysqltuner on a fresh restarted server is completely useless.

Add to the bash script check for HTTP status code.

If 500 then show message that configuration file is not correct.

Also may be response with empty configuration file.

Convert recommended values of the parameters to Gb, Mb, Kb

И еще — понятно, что это не ваш баг, но mysqltuner пытается получить данные через hostname -I. Проблема в том, что у меня на сервере hostname не понимает -I, ему вместо этого надо дать -i. Вручную подправить две строчки в скрипте — не проблема, но он каждый раз скачивается заново. Это действительно необходимо — каждый раз качать сторонний скрипт?

Automate safely apply recommended configuration depending on MySQL version

Impove MySQL Query cache calculation
https://maxchadwick.xyz/blog/mysql-query-cache-hit-rate

Error receiving configuration file for mysql 8

We need to:

• Improve documentation
• Check mysql version

Exclude "MySQL client" information from MySQLTuner JSON report, because it could contain password which user input manually. And it don't contain password if you use file with mysql credentials.

The value of table_definition_cache should be greater than the number of tables in mysql

MySQLConfigurer set uncorrect timeout
interactive_timeout = 1200 ### Previous value : 28800
wait_timeout = 1200 ### Previous value : 28800
https://habr.com/ru/post/499410/#comment_21558528

Improve tests description with sysbench parameters

Display MySQLTuner Recommendations during executing script

MySQLTuner recommendation to set the value of innodb_buffer_pool_size to more than RAM is available
Example:
cat .mysqlconfigurer/z_aiops_mysql.conf
...
innodb_buffer_pool_size = 3019898880 ### Previous value : 1073741824
...

free -h
total used free shared buffers cached
Mem: 4.0G 3.7G 327M 87M 60M 1.3G
-/+ buffers/cache: 2.3G 1.7G

The values in recommendations recommended value and previous value aren't in the same terminology
query_cache_size = 128M ### Previous value : 134217728

Параметр innodb_buffer_pool_instances должен быть равен целому количеству ГБ в innodb_buffer_pool_size.
Из рекомендаций mysqltuner можно добавить установку параметров:

1. table_definition_cache - Оставлять по умолчанию если не требуется увеличение
2. innodb_log_file_size - 25% от innodb_buffer_pool_size

Your service is recommending these changes as the last ones:
table_definition_cache=2218496 ### Previous value =524288
table_open_cache=4436224 ### Previous value =19745

And we had them updated for 3 nights in a row but they were never effective.

Now a supporter found that it was because they were too high for MySQL 8.

MySQLTuner recommend set this variable to 25% of the innodb_buffer_pool_size

For example:
https://blog.programs74.ru/how-to-change-innodb_log_file_size-safely/

#1

500 Internal Server Error Oh no! Something bad happened. Please check supported MySQL versions. Thanks.

mysql: percona-server-server-5.6 5.6.39-83.1-1.bionic

Add parameters for generate config with different innodb_flush_log_at_trx_commit values. Because for some servers critical to set value - 0, but for some servers you could use value - 2.

Hello!
I tried to use your script, but as a result I got file z_aiops_mysql.cnf
# 500 Internal Server Error Oh no! Something bad happened. Please check supported MySQL versions. Thanks.
To be sure I checked MySQL version.
$ mysql -v
.......
Server version: 5.5.65-MariaDB MariaDB Server
And then I checked the length of mysqltuner report - it's non empty.
-rw-r--r--. 1 adm77807 domain users 30535 Apr 29 14:00 mysqltunerreport.json

Could you help me, please?
Thanks!
Aleksey B.

Round up variables to 64 MB for variables with memory size

When the MySQLTuner use invalid credentials it returns message:

MySQLTuner 1.7.19 - Major Hayden [email protected] >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with ‘--help’ for additional options and output filtering[OK] You have the latest version of MySQLTuner(1.7.19)[!!] Attempted to use login credentials, but they were invalid.

And when that message send to api.support-servers.com as result we have Internal server error

[root@vm147000 ~]# /bin/bash mysqlconfigurer.sh
Can't locate Data/Dumper.pm in @inc (@inc contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /tmp/.mysqlconfigurer/mysqltuner.pl line 52.
BEGIN failed--compilation aborted at /tmp/.mysqlconfigurer/mysqltuner.pl line 52.

The value of innodb_buffer_pool_instances ignores the number of cores on the server.

Add support for MySQL 8

Add script download and execution in one line
https://stackoverflow.com/questions/12787501/wget-and-run-remove-bash-script-in-one-line

root@sud:~# wget https://raw.githubusercontent.com/initlabopen/mysqlconfigurer/master/mysqlconfigurer.sh --2020-03-11 19:17:12-- https://raw.githubusercontent.com/initlabopen/mysqlconfigurer/master/mysqlconfigurer.sh Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 151.101.112.133 Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|151.101.112.133|:443... connected. ERROR: The certificate of ‘raw.githubusercontent.com’ is not trusted. ERROR: The certificate of ‘raw.githubusercontent.com’ hasn't got a known issuer.

`root@sud:~# curl -o mysqlconfigurer.sh https://raw.githubusercontent.com/initlabopen/mysqlconfigurer/master/mysqlconfigurer.sh
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0
curl: (60) SSL certificate problem: unable to get local issuer certificate
More details here: https://curl.haxx.se/docs/sslcerts.html

curl performs SSL certificate verification by default, using a "bundle"
of Certificate Authority (CA) public keys (CA certs). If the default
bundle file isn't adequate, you can specify an alternate file
using the --cacert option.
If this HTTPS server uses a certificate signed by a CA represented in
the bundle, the certificate verification probably failed due to a
problem with the certificate (it might be expired, or the name might
not match the domain name in the URL).
If you'd like to turn off curl's verification of the certificate, use
the -k (or --insecure) option.`

I think I might have a problem with local issuer certificates, but reinstalling ca-certificates didn't help me.

https://rtfm.co.ua/mysqlmariadb-tyuning-proizvoditelnosti-1-thread_cache_size/#_thread_cache_size

Make setting the value of the variable as
thread_cache_size = Status->Max_used_connections + 10%

Display runtime information to see is it working or not.

Can you improve your guide so it includes text about where to place the optimized MySQL file if you are using a CentOS 7 server?

We had different supporters at Hosting company doing the different moving-ins of the optimized MySQL files over a period of days but the last 2 nights/early mornings the same supporter moved the optimized files into the “wrong” folder (but “right” folder following your guide). The other supporters known about the real folder for MySQL when using CentOS 7 but this supporter had not and our points stayed at 70 after 2 optimizations.

~/.my.cnf is created, but not used.

Debian 4.19.132-1 (2020-07-24) x86_64 GNU/Linux
mariadb 5.5.5-10.3.23-MariaDB-0+deb10u1

major/MySQLTuner-perl#463 (comment) is worked.

Change the value of the max_connections variable if max_connections is slightly larger or equal to the value of the Max_used_connections status variable.

The configuration file contains new values for the mysql variables. But I want to see old values which we plan to change.

Please add old values as comments for variables.

Так просто нельзя же менять значение innodb_log_file_size. Это может повлечь проблемы с рестартом сервера. Нужны какие-то инструкции и рекомендации как правильно скорректировать это, чтобы не попасть впросак вам как сервису

It could be extremely good if make a WHM plugin so we could do the optimization from the WHM having optimize and rollback buttons there.

Display server name in the servers list.

There is an unresolved bug in MariaDB regarding table_definition_cache:
https://jira.mariadb.org/browse/MDEV-17124

It casues some web applications to throw error:

General error: 1615 Prepared statement needs to be re-prepared

This is z_aiops_mysql.cnf which made the bug appear in my environments:

[mysqld]
query_cache_type = 1 ### Previous value : OFF
query_cache_size = 128M ### Previous value : 0
query_cache_limit = 16M ### Previous value : 4194304
thread_cache_size = 256 ### Previous value : 256
key_buffer_size = 2147483648 ### Previous value : 2147483648
sort_buffer_size = 24M ### Previous value : 2097152
bulk_insert_buffer_size = 2M ### Previous value : 8388608
myisam_sort_buffer_size = 24M ### Previous value : 134216704
innodb_buffer_pool_instances = 1 ### Previous value : 3
innodb_buffer_pool_size = 2013265920 ### Previous value : 3221225472
max_heap_table_size = 256M ### Previous value : 268435456
tmp_table_size = 256M ### Previous value : 268435456
join_buffer_size = 8M ### Previous value : 25165824
max_connections = 256 ### Previous value : 256
interactive_timeout = 1200 ### Previous value : 12800
wait_timeout = 1200 ### Previous value : 12800
table_open_cache = 2048 ### Previous value : 2000
table_definition_cache = 1408 ### Previous value : 2048
innodb_flush_log_at_trx_commit = 2 ### Previous value : 1
innodb_log_file_size = 251658240 ### Previous value : 419430400
innodb_write_io_threads = 4 ### Previous value : 4
innodb_read_io_threads = 4 ### Previous value : 4
innodb_buffer_pool_chunk_size = 134217728 ### Previous value : 134217728`

Changing table_definition_cache to 2048 seems to fix the bug.

Sometimes, if you put the file in /etc/mysql/conf.d/z_aiops_mysql.cnf, then not all settings apply. This happens when the same settings are made in the files from the /etc/mysql/mysql.conf.d/ folder, because files from it are included later.

add to documentation

Need to rename file z_aiops_mysql.conf -> z_aiops_mysql.cnf
After performing the steps from point 4, the new settings are not applied. The reason for this problem is that mysql uses the .cnf file extension.

The data collected is from just before the mysql service is restarting (if your portal is displaying in the time zone CET which it does not say).

The MySQL service was restarted at 01:00 CET but hopefully later today it will update the matrics and we know that we have got accurate stats now.