bmdan / tuning-primer.sh Goto Github PK
View Code? Open in Web Editor NEWMySQL Tuning-Primer.sh, updated and improved
License: GNU General Public License v2.0
MySQL Tuning-Primer.sh, updated and improved
License: GNU General Public License v2.0
This script needs a simple update to handle InnoDB detection for MySQL 5.7 and above. The current logic does not look for 5.7, and therefore sets innodb_enabled to 0 instead of 1.
Hello,
When I run your script on Debian 9 with MariaDB 10.5, I get the following error:
Error: Command line calculator 'bc' not found!
mysqltuner works perfectly.
Regards
Hi - I’m wondering if you meant to call cechon() here instead of where you recursively call cecho()?
function cecho()
{
if [ -z "${1-}" ]; then
cecho "No message passed.\n" "${2-}"
return $?
fi
cechon "$1"$'\n' "${2-}"
return $?
}
I am using MySQL 8.0.30 and apparently tuning-primer thinks that this is outdated and does not support query cache (which I doubt).
If I am right and MySQL 8.0 has a query cache, the warning is wrong. If it doesn't the recommendation does not make much sense and needs to be updated.
QUERY CACHE
You are using MySQL 8.0.30-0ubuntu0.20.04.2, no query cache is supported.
I recommend an upgrade to MySQL 4.1 or better
I don't see a --no-color in --help.
curl -sL https://raw.githubusercontent.com/BMDan/tuning-primer.sh/main/tuning-primer.sh | bash --help
GNU bash, version 3.2.57(1)-release-(x86_64-apple-darwin23)
Usage: bash [GNU long option] [option] ...
bash [GNU long option] [option] script-file ...
GNU long options:
--debug
--debugger
--dump-po-strings
--dump-strings
--help
--init-file
--login
--noediting
--noprofile
--norc
--posix
--protected
--rcfile
--restricted
--verbose
--version
--wordexp
I am running tuning-primer.sh script on cygwin following error occurs.
No valid socket file "" found!
The mysqld process is not running or it is installed in a custom location.
If you are sure mysqld is running, execute script in "prompt" mode or set
the socket= variable at the top of this script
I also run it in prompt mod provide socket file directory path /tmp/mysql.sock still above error is occurring.
any solution?.
Newer systems using iproute package instead of net-tools must change this line:
netstat -ln | awk '/mysql(.*)?\.sock/ { print $9 }' | head -1
to:
ss -ln | awk '/mysql(.*)?\.sock/ { print $5 }' | head -1
Maybe you can check for netstat and fallback to ss or vice versa when one tool is not found.
Are there any plans to support Mysql 8?
Currently it fails with "INITIAL LOGIN ATTEMPT FAILED" (respectively FINAL LOGIN ATTEMPT FAILED)
./tuning-primer.sh: line 1273: *100/: syntax error: operand expected (error token is "*100/")
skip-innodb
in my.cnf
, tuning-primer
is run as root in default mode.The issue seems to be that the script uses the ignore_builtin_innodb
variable to determine whether or not InnoDB is running, this doesn't seem to be reliable for our version at least.
MariaDB [(none)]> SHOW VARIABLES LIKE '%innodb%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| have_innodb | DISABLED |
| ignore_builtin_innodb | OFF |
+-----------------------+----------+
...because it displays the message The slow query log is NOT enabled.
although it is:
MariaDB [mysql]> show variables like 'slow_query%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/mariadb-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.005 sec)
There is no variable like log%queries
as is in the script (maybe it is MySQL specific?).
There is an error when running script with MariaDB 10.9.3 and some earlier versions:
BINARY UPDATE LOG
The binary update log is enabled
./tuning-primer.sh: line 592: [: 2.000000: integer expression expected
Binlog sync is not enabled, you could lose binlog records during a server crash
Databases in the cloud don't use sockets which makes this awesome tool unusable. Azure database for MySQL be created uses the format username@hostname for username, everything else is the same as connecting to a remote server.
Downloading the tuning-primer.sh as instructed.
But failed when trying to run it:
$ sh tuning-primer.sh
The output:
tuning-primer.sh: 40: Syntax error: "(" unexpected
Using:
Distributor ID: Ubuntu
Description: Ubuntu 20.04.4 LTS
Release: 20.04
Codename: focal
What do you suggest?
Hello @BMDan , Thanks for this excellent utility
I've run the primer, but i would benefit from your reading of the following three points, please.
in particular the first one about the memory usage.
Many thanks,
MEMORY USAGE
Max Memory Ever Allocated : 62.17 G
Configured Max Per-thread Buffers : 178.22 G
Configured Max Global Buffers : 38.56 G
Configured Max Memory Limit : 216.78 G
Physical Memory : 47.03 G
Max memory limit exceeds 90% of physical memory
TABLE CACHE
Current table_open_cache = 1024 tables
Current table_definition_cache = 912 tables
You have a total of 669 tables
You have 1024 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
JOINS
Current join_buffer_size = 20.00 M
You have had 42247 queries where a join could not use an index properly
You have had 30012 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
After upgrading to MariaDB 11.0.3 I am getting the following:
Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found potential sockets: /run/mysqld/mysqld.sock
Will use client's default socket (this is normally correct).
I was unable to connect when passing on my password (both CLI and my.cnf). I assumed this was because I have an # or @ in the password. I don't see why it went wrong when I passed the password during CLI, this might be a bug but it might be good to document (if it isn't already) that it's better to enclose passwords with certain characters with quotes (as that seems to do the trick)
Running v 1.99 as below produces an awk error related to existence/location of ~/my.cnf file:
[gamimac 61] ~/working>tuning-primer.sh banner
awk: can't open file /Users/xxx/my.cnf
source line number 1
~/my.cnf doesn’t exist on my system. Should the script be looking for .my.cnf which does?
Hi. I tried more than one hour to get thunig-primer.sh work.
This works well:
_
mysql
mysql -u root -p
_
But all time, ./tuning-primer.sh prompt:
Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
The point:
mysqladmin wasn't found - so tuning-primer.sh throw this error, not because of false credentials, but because it can't find mysqladmin.
Adding the mysql-path to PATH solves this problem.
Maybe there is the possibility to throw: mysqladmin not found, script stopped.
TIA and sorry for my poor english ;-)
Hi,
I've MariaDB on an Ubuntu machine and I create a ~/.my.cnf with user and password (and correct permissions).
When I start the tuning-primer.sh script, the mysqld daemon is not correctly identified as alive, which compromises the execution of the script since alternative access methods are attempted that do not succeed.
I have verified that it is sufficient to simply modify lines 1396-1397 of the login_validation function by specifying the correct name of the commands for MariaDB:
export MYSQL_COMMAND="mariadb"
export MYSQLADMIN_COMMAND="mariadb-admin"
After this fix... all works fine!
primer version:
# Version: 1.99 Released: 2018-06-10 #
MySQL Version:
MySQL Version 5.7.25-log x86_64
Warning message (incorrect?):
The slow query log is NOT enabled. Current long_query_time = 2.000000 sec.
my.cnf:
~$ grep slow /etc/my.cnf slow_query_log=1 slow_query_log_file=/var/log/mysql/slowqueries.log
The code indicates it is reading the my.cnf file and looking for a boolean (ON or OFF). This is incorrect behavior. The correct implementation would use mysqladmin variables
.
Hi,
I am using this tool on both of my local servers as well as some live servers for getting the recommendations for tuning MySQL variables. I would like to suggest the below feature if possible for better usability.
When we run the command ./tuning-primer.sh for the first time, it will prompt to create a ~/.my.cnf file for us and it will store the socket and login credentials. Likewise, will it be possible to create one more file to write all the suggested MySQL variables and its suggested values? It will be really helpful to use the suggested values on the MySQL configuration file.
Hope its clear on what I am trying to suggest and it may helpful for lot of beginners.
Thank you!
作者,您好。非常感谢您对该工具的更新。请教个问题。运行该工具时,一直提示【行210】:Unable to log into socket。排查原因是执行mysqladmin ping命令时,要求需要输入密码导致的,没有直接输出ping的结果。若运行 mysqladmin -u user -p 回车再输入密码是对的。所用mysql5.7.24。请问该问题怎样解决呢?
另外提个反馈:【行:266】[client]\nuser=$user\npassword=$pass\socket=$socket。这里保存密码,\socket应该是\nsocket,要不密码和socket会在一行。
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.