How to optimize Mysql or MariaDB

How to optimize Mysql or MariaDB

Posted on

Our article will show you know How to optimize Mysql or MariaDB? Please read step by step.

What is Mysql

What is Mysql?

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.

The MySQL website (http://www.mysql.com/) provides the latest information about MySQL software.

  • MySQL is a database management system.A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications.
  • MySQL databases are relational.A relational database stores data in separate tables rather than putting all the data in one big storeroom. The database structures are organized into physical files optimized for speed. The logical model, with objects such as databases, tables, views, rows, and columns, offers a flexible programming environment. You set up rules governing the relationships between different data fields, such as one-to-one, one-to-many, unique, required or optional, and “pointers” between different tables. The database enforces these rules, so that with a well-designed database, your application never sees inconsistent, duplicate, orphan, out-of-date, or missing data.The SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common standardized language used to access databases. Depending on your programming environment, you might enter SQL directly (for example, to generate reports), embed SQL statements into code written in another language, or use a language-specific API that hides the SQL syntax.SQL is defined by the ANSI/ISO SQL Standard. The SQL standard has been evolving since 1986 and several versions exist. In this manual, “SQL-92” refers to the standard released in 1992, “SQL:1999” refers to the standard released in 1999, and “SQL:2003” refers to the current version of the standard. We use the phrase “the SQL standard” to mean the current version of the SQL Standard at any time.
  • MySQL software is Open Source.Open Source means that it is possible for anyone to use and modify the software. Anybody can download the MySQL software from the Internet and use it without paying anything. If you wish, you may study the source code and change it to suit your needs. The MySQL software uses the GPL (GNU General Public License), http://www.fsf.org/licenses/, to define what you may and may not do with the software in different situations. If you feel uncomfortable with the GPL or need to embed MySQL code into a commercial application, you can buy a commercially licensed version from us. See the MySQL Licensing Overview for more information (http://www.mysql.com/company/legal/licensing/).
  • The MySQL Database Server is very fast, reliable, scalable, and easy to use.If that is what you are looking for, you should give it a try. MySQL Server can run comfortably on a desktop or laptop, alongside your other applications, web servers, and so on, requiring little or no attention. If you dedicate an entire machine to MySQL, you can adjust the settings to take advantage of all the memory, CPU power, and I/O capacity available. MySQL can also scale up to clusters of machines, networked together.MySQL Server was originally developed to handle large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Although under constant development, MySQL Server today offers a rich and useful set of functions. Its connectivity, speed, and security make MySQL Server highly suited for accessing databases on the Internet.
  • MySQL Server works in client/server or embedded systems.The MySQL Database Software is a client/server system that consists of a multithreaded SQL server that supports different back ends, several different client programs and libraries, administrative tools, and a wide range of application programming interfaces (APIs).We also provide MySQL Server as an embedded multithreaded library that you can link into your application to get a smaller, faster, easier-to-manage standalone product.
  • A large amount of contributed MySQL software is available.MySQL Server has a practical set of features developed in close cooperation with our users. It is very likely that your favorite application or language supports the MySQL Database Server.

The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.

What is MariaDB?

What is MariaDB?

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation in 2009.

MariaDB intended to maintain high compatibility with MySQL, ensuring a drop-in replacement capability with library binary parity and exact matching with MySQL APIs and commands. However, new features diverge more. It includes new storage engines like Aria, ColumnStore, and MyRocks.

Its lead developer/CTO is Michael “Monty” Widenius, one of the founders of MySQL AB and the founder of Monty Program AB. On 16 January 2008, MySQL AB announced that it had agreed to be acquired by Sun Microsystems for approximately $1 billion. The acquisition completed on 26 February 2008. Sun was then bought the following year by Oracle Corporation. MariaDB is named after Monty’s younger daughter, Maria. (MySQL is named after his other daughter, My.)

How to optimize Mysql or MariaDB

How to optimize Mysql (MariaDB)?

To optimize Mysql, you need to make changes to the Mysql configuration files. So I recommend that you create a backup file before making any changes!

Where is the mysql configuration file?

There are many ways to find out the location of mysql configuration file, here are some things to try:

Default location is:/etc/my.cnf

If it isn’t exist on your server, you can try use ccommand line to find:

Linux:

$ ps aux |grep mysqld

Solaris:

$ /usr/ucb/ps -alxwww | grep mysqld

Windows:

Command line arguments are observed in Task Manager when column ‘Command line’ is enabled. (View->’Select Columns’ menu option).

How to backup mysql configuration file?

sudo cp /etc/my.cnf /my.cnf.bak

How to optimize Mysql by mysqltuner?

What is MySQLTuner-perl?

MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.

MySQLTuner supports ~300 indicators for MySQL/MariaDB/Percona Server in this last version.

MySQLTuner is maintained and indicator collect is increasing week after week supporting a lot of configuration such as Galera Cluster, TokuDB, Performance schema, Linux OS metrics, InnoDB, MyISAM, Aria, …

Download/Installation

Choose one of these methods:

  1. Script direct download (the simplest and shortest method):
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
  1. You can download the entire repository by using git clone or git clone --depth 1 -b master followed by the cloning URL above.

Optional Sysschema installation for MySQL 5.6

Sysschema is installed by default under MySQL 5.7 and MySQL 8 from Oracle. By default, on MySQL 5.6/5.7/8, performance schema is enabled by default. For previous 5.6 version, you can follow this command to create a new database sys containing very useful view on Performance schema:

curl "https://codeload.github.com/mysql/mysql-sys/zip/master" > sysschema.zip
# check zip file
unzip -l sysschema.zip
unzip sysschema.zip
cd mysql-sys-master
mysql -uroot -p < sys_56.sql

Optional Performance schema and Sysschema installation for MariaDB 10.x

Sysschema is not installed by default under MariaDB 10.x. By default, on MariaDB, performance schema is disabled by default. consider activating performance schema across your my.cnf configuration file:

[mysqld]
performance_schema = on

You can follow this command to create a new database sys containing very useful view on Performance schema:

curl "https://codeload.github.com/FromDual/mariadb-sys/zip/master" > mariadb-sys.zip
# check zip file
unzip -l mariadb-sys.zip
unzip mariadb-sys.zip
cd mariadb-sys-master/
mysql -u root -p < ./sys_10.sql

Errors & solutions for performance schema installation

 ERROR at line 21: Failed to open file './tables/sys_config_data_10.sql -- ported', error: 2
 Have a look at #452 solution given by @ericx

Performance tips

Metadata statistic updates can impact strongly performance of database servers and MySQLTuner. Be sure that innodb_stats_on_metadata is disabled.

set global innodb_stats_on_metadata = 0;

Specific usage

Usage: Minimal usage locally

perl mysqltuner.pl --host 127.0.0.1

Of course, you can add the execute bit (chmod +x mysqltuner.pl) so you can execute it without calling perl directly.

Usage: Minimal usage remotely

perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password

Usage: Enable maximum output information around MySQL/MariaDb without debugging

perl mysqltuner.pl --verbose
perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat

Usage: Enable CVE vulnerabilities check for your MariaDB or MySQL version

perl mysqltuner.pl --cvefile=vulnerabilities.csv

Usage: Write your result in a file with information displayed

perl mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt

Usage: Write your result in a file without outputting information

perl mysqltuner.pl --silent --outputfile /tmp/result_mysqltuner.txt

Usage: Using template model to customize your reporting file based on Text::Template syntax.

perl mysqltuner.pl --silent --reportfile /tmp/result_mysqltuner.txt --template=/tmp/mymodel.tmpl

Usage: Enable debugging information

perl mysqltuner.pl --debug

Usage: Update MySQLTuner and data files (password and cve) if needed

perl mysqltuner.pl --checkversion --updateversion

FAQ

Question: Will MySQLTuner fix my slow MySQL server?

No. MySQLTuner is a read only script. It won’t write to any configuration files, change the status of any daemons, or call your mother to wish her a happy birthday. It will give you an overview of your server’s performance and make some basic recommendations for improvements that you can make after it completes. Make sure you read the warning above prior to following any recommendations.

Question: Can I fire my DBA now?

MySQLTuner will not replace your DBA in any form or fashion. If your DBA constantly takes your parking spot and steals your lunch from the fridge, then you may want to consider it – but that’s your call.

Question: Why does MySQLTuner keep asking me the login credentials for MySQL over and over?

The script will try its best to log in via any means possible. It will check for ~/.my.cnf files, Plesk password files, and empty password root logins. If none of those are available, then you’ll be prompted for a password. If you’d like the script to run in an automated fashion without user intervention, then create a .my.cnf file in your home directory which contains:

[client]
user=someusername
password=thatuserspassword

Once you create it, make sure it’s owned by your user and the mode on the file is 0600. This should prevent the prying eyes from getting your database login credentials under normal conditions. If a T-1000 shows up in a LAPD uniform and demands your database credentials, you won’t have much of an option.

Question: Is there another way to secure credentials on latest MySQL and MariaDB distributions ?

You could use mysql_config_editor utilities.

	$ mysql_config_editor set --login-path=client --user=someusername --password --host=localhost
	Enter password: ********

After which, ~/.mylogin.cnf will be created with the appropriate access.

To get information about stored credentials, use the following command:

$mysql_config_editor print

[client]

user = someusername password = ***** host = localhost

Question: What’s minimum privileges needed by a specific mysqltuner user in database ?

    mysql>GRANT SELECT, PROCESS,EXECUTE, REPLICATION CLIENT,SHOW DATABASES,SHOW VIEW ON *.* TO 'mysqltuner'@'localhost' identified by pwd1234;

Question: It’s not working on my OS! What gives?!

These kinds of things are bound to happen. Here are the details I need from you in order to research the problem thoroughly:

  • OS and OS version
  • Architecture (x86, x86_64, IA64, Commodore 64)
  • Exact MySQL version
  • Where you obtained your MySQL version (OS package, source, etc)
  • The full text of the error
  • A copy of SHOW VARIABLES and SHOW GLOBAL STATUS output (if possible)

Question: How to perform CVE vulnerability checks?

  • Download vulnerabilities.csv from this repository.
  • use option –cvefile to perform CVE checks

Question: How to use mysqltuner from a remote host? Thanks to @rolandomysqldba

  • You will still have to connect like a mysql client:

Connection and Authentication

--host <hostname> Connect to a remote host to perform tests (default: localhost)
--socket <socket> Use a different socket for a local connection
--port <port>     Port to use for connection (default: 3306)
--user <username> Username to use for authentication
--pass <password> Password to use for authentication
--defaults-file <path> defaults file for credentials

Since you are using a remote host, use parameters to supply values from the OS

--forcemem <size>  Amount of RAM installed in megabytes
--forceswap <size> Amount of swap memory configured in megabytes
  • You may have to contact your remote SysAdmin to ask how much RAM and swap you have

If the database has too many tables, or very large table, use this:

--skipsize           Don't enumerate tables and their types/sizes (default: on)
                     (Recommended for servers with many tables)

Question: Can I install this project using homebrew on Apple Macintosh?

Yes! brew install mysqltuner can be used to install this application using homebrew on Apple Macintosh.

MySQLTuner and Vagrant

MySQLTuner contains following Vagrant configurations:

  • Fedora Core 30 / Docker

Vagrant File is stored in Vagrant subdirectory.

  • Follow following step after vagrant installation: $ vagrant up

MySQLTuner contains a Vagrant configurations for test purpose and development

setup test environments

$ sh build/createTestEnvs.sh

$ source build/bashrc
$ mysql_percona80 sakila
sakila> ...

$ docker images
mariadb                  10.1                fc612450e1f1        12 days ago         352MB
mariadb                  10.2                027b7c57b8c6        12 days ago         340MB
mariadb                  10.3                47dff68107c4        12 days ago         343MB
mariadb                  10.4                92495405fc36        12 days ago         356MB
mysql                    5.6                 95e0fc47b096        2 weeks ago         257MB
mysql                    5.7                 383867b75fd2        2 weeks ago         373MB
mysql                    8.0                 b8fd9553f1f0        2 weeks ago         445MB
percona/percona-server   5.7                 ddd245ed3496        5 weeks ago         585MB
percona/percona-server   5.6                 ed0a36e0cf1b        6 weeks ago         421MB
percona/percona-server   8.0                 390ae97d57c6        6 weeks ago         697MB
mariadb                  5.5                 c7bf316a4325        4 months ago        352MB
mariadb                  10.0                d1bde56970c6        4 months ago        353MB
mysql                    5.5                 d404d78aa797        4 months ago        205MB

$ docker ps
CONTAINER ID        IMAGE                        COMMAND                  CREATED             STATUS              PORTS                               NAMES
da2be9b050c9        mariadb:5.5                  "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:5311->3306/tcp              mariadb55
5deca25d5ac8        mariadb:10.0                 "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:5310->3306/tcp              mariadb100
73aaeb37e2c2        mariadb:10.1                 "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:5309->3306/tcp              mariadb101
72ffa77e01ec        mariadb:10.2                 "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:5308->3306/tcp              mariadb102
f5996f2041df        mariadb:10.3                 "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:5307->3306/tcp              mariadb103
4890c52372bb        mariadb:10.4                 "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:5306->3306/tcp              mariadb104
6b9dc078e921        percona/percona-server:5.6   "/docker-entrypoint.…"   7 hours ago         Up 7 hours          0.0.0.0:4308->3306/tcp              percona56
3a4c7c826d4c        percona/percona-server:5.7   "/docker-entrypoint.…"   7 hours ago         Up 7 hours          0.0.0.0:4307->3306/tcp              percona57
3dda408c91b0        percona/percona-server:8.0   "/docker-entrypoint.…"   7 hours ago         Up 7 hours          33060/tcp, 0.0.0.0:4306->3306/tcp   percona80
600a4e7e9dcd        mysql:5.5                    "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:3309->3306/tcp              mysql55
4bbe54342e5d        mysql:5.6                    "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:3308->3306/tcp              mysql56
a49783249a11        mysql:5.7                    "docker-entrypoint.s…"   7 hours ago         Up 7 hours          33060/tcp, 0.0.0.0:3307->3306/tcp   mysql57
d985820667c2        mysql:8.0                    "docker-entrypoint.s…"   7 hours ago         Up 7 hours          0.0.0.0:3306->3306/tcp, 33060/tcp   mysql 8    0

Leave a Reply

Your email address will not be published. Required fields are marked *