Variables to be TUNE for MySQL Optimization


Here, I gave the list of variables to be tune for MySQL optimization. And give the value for each variable belongs to your system configuration.

The MySQL server

#mysqld
max_connections
max_user_connections
key_buffer_size
myisam_sort_buffer_size
bulk_insert_buffer_size
join_buffer_size
read_buffer_size
sort_buffer_size
table_cache
table_open_cache
thread_cache_size
max_heap_table_size
tmp_table_size  
interactive_timeout
wait_timeout
connect_timeout
max_allowed_packet
max_connect_errors
query_cache_limit
query_cache_size
query_cache_type
log_slow_queries
long_query_time 

#InnoDB tables

innodb_log_buffer_size
innodb_buffer_pool_size
innodb_additional_mem_pool_size

#mysqldump
max_allowed_packet

#myisamchk
key_buffer_size
sort_buffer_size
read_buffer_size


Comments

Post a Comment

Popular posts from this blog

PostgreSQL Database Version 13.4 To MySQL Database Version 8.0.20 Migration by using SQLines Tool

 PostgreSQL to MySQL  ------------------------ This Document will give details about PostgreSQL to MySQL Database . Environment Details :- --------------------------- Server 1 == Source Database == PostgreSQL Database Version == 13.4 Server 2 == Target Database == MySQL Database Version == 8.0.20 Server 3 == Jump host == Where you will install Sqline and connect to Source DB and Target DB. I have consider 3 EC2 Machines from AWS [ RHEL 8.x ]  postgres-source-server == 172.31.30.141 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 mysql-target-server    == 172.31.17.136 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 jump-host              == 172.31.27.241 [ Private Ipaddress ] = EC2 Machine = RHEL 8.4 Step - 1  Install PostgreSQL Database Version in Source Server  Step - 2  Install MySQL Database Verion in Target Server  Step - 3  Install / Setup sqline s/w in Jump host server  Step - 4  Loading sample data in Source Database i.e PostgreSQL Database  Step - 5  Create Super User / Remote u
Read more

RDS MySQL / MariaDB SSL Connection by using Workbench and command line

Image
In this blog , i am going to explain how to connect RDS MySQL With SSL Connections from MySQL Workbench and MySQL / MariaDB client utility by using command line  --> Make sure RDS MySQL / MariaDB instance is ready for usage. Connection Established from MySQL / MariaDB client by using cmd line C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u admin -p -h ssl-test-server.cz8z9jo4bowe.us-east-1.rds.amazonaws.com Enter password: ************* Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 25.Server version: 8.0.23 Source distribution.Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%ssl%'; +-------------------------------------+----------------
Read more

Install Mydumper and Myloader Software for Backup of MySQL / MariaDB Databases and Usage of commands

Mydumper and Myloader Installation  ---------------------------------- Comparison of MySQL backup tools: 1. mysqldump: belongs to logical backup, there will be lock tables, but considering that the amount of data is relatively large, the time to lock the tables will be longer, business is not allowed... 2. xtrabackup: It is a physical backup, and there is no lock table, but considering that the two DBs use shared table spaces, and when the database of business B is restored, one is that the time is relatively long, and the other is that the data is definitely incorrect.... 3. mydumper: belongs to logical backup. It is a multi-threaded, high-performance data logical backup and recovery tool, and the lock table time is very short (40G data, within 10 minutes), and it will record binlog file and position at the same time.... Mydumper & Myloader installation in RHEL for MariaDB / MySQL Backups utilities - Third party Tools ---------------------------------------------------------------
Read more