Not able to shutdown the DB instance using mysqld_multi from MySQL 5.6.27 due to "Access denied" issue

Problem Statement : Not able to shutdown the DB instance using mysqld_multi from MySQL 5.6.27 due to "Access denied" issue

[ mysql@localhost ~]$  /opt/product/mysql/mysql5627/bin/mysqld_multi --defaults-file=/mysqldata/configfiles/localhost_3307.cnf stop 3307

Stopping MySQL servers

[ mysql@localhost ~]$ Warning: Using a password on the command line interface can be insecure.

/opt/product/mysql/mysql5627/bin/mysqladmin: connect to server at 'localhost' failed

error: 'Access denied for user 'multi_admin'@'localhost' (using password: YES)'

Note: Shutdown userid is created with right credentials , and entered correctly in extra configuration file , but not able to shutdown the instance with error "Access Denied" for the shutdown userid.

mysql> select user,host,password,password('multipass') from mysql.user where user = 'multi_admin'\G

                 user: multi_admin

                 host: localhost

             password       : *4FD92416C02EC3BD04A97613DB2CB33717E99D0F

password('multipass'): *4FD92416C02EC3BD04A97613DB2CB33717E99D0F

mysql> show grants for multi_admin@localhost;

| GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY PASSWORD '*4FD92416C02EC3BD04A97613DB2CB33717E99D0F' |

[mysqld_multi]

mysqld             =/opt/product/mysql/mysql5627/bin/mysqld_safe

mysqladmin    =/opt/product/mysql/mysql5627/bin/mysqladmin

user                = multi_admin

password       = multipass

Solution : This is just a suggestion to circumvent the problem until there is a permanent fix .

Obviously everyone will start debugging mysqld_multi which is a perl script . 

When you do a diff between previous version, say 5.6.16 and current version 5.6.27 , you wont find any difference in this open text perl script.

Problem lies with the binary file my_print_defaults.

If you use the previous version [ 5.6.16 ] and run the "my_print_defaults" command on the extra configuration file, it prints the options you have entered correctly .

[ mysql@localhost ~]$ /opt/product/mysql/mysql5616/bin/my_print_defaults --defaults-file=/mysqldata/configfiles/localhost_3307.cnf mysqld_multi

--mysqld=/opt/product/mysql/mysql5627/bin/mysqld_safe

--mysqladmin=/opt/product/mysql/mysql5627/bin/mysqladmin

--password=multipass

--user=multi_admin

If you use the newer version [ 5.6.27 ] and run the "my_print_defaults" command on the extra configuration file, it prints the options you have entered correctly except for password where it masks any password you have given to "*****",  therein lies the problem which breaks the mysqld_multi.

[ mysql@localhost ~]$ /opt/product/mysql/mysql5627/bin/my_print_defaults --defaults-file=/mysqldata/configfiles/localhost_3307.cnf mysqld_multi

--mysqld=/opt/product/mysql/mysql5627/bin/mysqld_safe

--mysqladmin=/opt/product/mysql/mysql5627/bin/mysqladmin

--password=*****

--user=multi_admin

Fortunately my_print_defaults has an option -s, --show          Show passwords in plain text.

[ mysql@localhost ~]$ /opt/product/mysql/mysql5627/bin/my_print_defaults -s --defaults-file=/mysqldata/configfiles/localhost_3307.cnf mysqld_multi

--mysqld=/opt/product/mysql/mysql5627/bin/mysqld_safe

--mysqladmin=/opt/product/mysql/mysql5627/bin/mysqladmin

--password=multipass

--user=multi_admin

Option #1 : Pass the password in command line itself of mysqld_multi manually 

Option #2 : Alter the mysqld_multi where it calls my_print_defaults . 

Since mysqld_multi executes my_print_defaults internally and stores all the values passed in extra configuration file, depending on the option "-s" , 

password is passed to calling script either in plain text [ if "-s" option is used" or password is passed as masked "*****"  [if "-s" option is not used ]

Open "/opt/product/mysql/mysql5627/bin/mysqld_multi" , and make the following change in line #216

Before:

    Line # 216 :  my $com= join ' ', 'my_print_defaults', @defaults_options, $group;

After :

    Line # 216 :  my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

Solved :

[ mysql@localhost ~]$ /opt/product/mysql/mysql5627/bin/mysqld_multi --defaults-file=/mysqldata/configfiles/localhost_3307.cnf stop 3307

Stopping MySQL servers

Warning: Using a password on the command line interface can be insecure.

151213 09:03:17 mysqld_safe mysqld from pid file /zyme_db1/mysql/3307/var/lib/mysql/mysql_3307.pid ended

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers

Calculating average memory utilisation of a MySQL DB Instance

MySQL DB Instance Memory Usage Calculations : The following parameters to be considered to calculate average memory utilisation of a MySQL Database instance.

Global Parameters : These are the fixed sized pre allocated buffers created while bringing up the DB instance.

Performance_schema.memory
innodb_buffer_pool_size
innodb_additional_mem_pool_size ( depreciated since MySQL 5.6)
key_buffer_size
query_cache_size

Connection Based Parameters : The following are the connection specific buffers few of them gets created when the new connection to the DB created, and few of them will get created as per the requirement while processing the queries.

binlog_cache_size
binlog_stmt_cache_size
bulk_insert_buffer_size
innodb_log_buffer_size
join_buffer_size
max_allowed_packet
net_buffer_length
preload_buffer_size
query_prealloc_size
range_alloc_block_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
thread_stack
transaction_prealloc_size

A) By Using the following query you can get the average memory usage of a running MySQL DB instance.

Pre requisites :

1) get the value of “performance_schema.memory” from the performance_schema status and replace the value with “performance_schema.memory” in the following query.
2) get the max value of (Threads_cached,Threads_connected) from the status and replace it with max(Threads_cached,Threads_connected).

Query :

select (( performance_schema.memory + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@key_buffer_size + @@query_cache_size ) + max(Threads_cached,Threads_connected) * (@@binlog_cache_size + @@binlog_stmt_cache_size + @@bulk_insert_buffer_size + @@innodb_log_buffer_size + @@join_buffer_size + @@max_allowed_packet + @@net_buffer_length + @@preload_buffer_size + @@query_prealloc_size + @@range_alloc_block_size + @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@transaction_prealloc_size)) as Total_Memory;

NOTE : The out put of above command resulting the value between "VSZ" and "RSS" from "ps auuwwx" command. 

 
B) By Using the following query you can get the maximum memory can be used by the MySQL DB instance while it is dealing with the maximum no of connections with most of the memory allocations.

Pre requisites :

1) get the value of “performance_schema.memory” from the performance_schema status and replace the value with “performance_schema.memory” in the following query.

Query :

select (( performance_schema.memory + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@key_buffer_size + @@query_cache_size ) + @@max_connections * (@@binlog_cache_size + @@binlog_stmt_cache_size + @@bulk_insert_buffer_size + @@innodb_log_buffer_size + @@join_buffer_size + @@max_allowed_packet + @@net_buffer_length + @@preload_buffer_size + @@query_prealloc_size + @@range_alloc_block_size + @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@transaction_prealloc_size)) as Total_Memory;

NOTE : The out put of above command resulting the value between "VSZ" and "RSS" from "ps auuwwx" command. 

 
NOTE : You might not get the exact memory utilisation of a DB instance, these queries are only to give you the average utilisation. You might need to find out couple of other parameters and ways to find out the exact utilisation of a DB instance.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers

List of background operations performed by MySQL InnoDB Main thread.

The main InnoDB thread is a special thread that just runs in a loop to do various kinds of work. You can get the state of main thread from the output of show engine InnoDB status, under “ROW OPERATIONS” section.

As per MySQL 5.6.24 source code the following are the list of background operations performed by InnoDB main thread.

checking free log space
doing background drop tables
doing insert buffer merge
enforcing dict cache limit
flushing log
making checkpoint
sleeping
suspending
waiting for server activity

Let us try to understand more about each of these states and what will happen internally in the background during each of these states.

checking free log space : During this state MySQL checks the redo logs usage and makes sure that redo logs having enough space to flush the committed transactions.

doing background drop tables : This state is still not clear but, from the source code we can see the following comment.

/* ALTER TABLE in MySQL requires on Unix that the table handler can drop tables lazily after there no longer are SELECT queries to them. */

If you drop a table where there still queries are running, then InnoDB delays the drop table until the queries have ended, and only after that drops the table.
When dropping the table, InnoDB has to remove also any locks there on the table.

So my understanding is that during this state, it is performing some background operations related to either drop table or alter table. I will have to dig more on this and probable write another following up post on this, Any help on this is appreciable.

More Info : 1) http://gtowey.blogspot.in/2012/09/whats-InnoDB-main-thread-really-doing.html
           2) http://grokbase.com/t/MySQL/MySQL/02a3004wpa/InnoDB-hard-lockup

doing insert buffer merge : First let us understand what is insert buffer and which kind of data it consists. Actually when ever inserts happens to the table which is having secondary indexes created, those inserts data first will be stored in the insert buffer, since these are new rows and has to be stored in the disk in the indexed order because index has been created on one of the columns.

Let us consider an example, suppose if we have data in the indexed column A,B,C,F,G,H etc. and all this data is present in the disk. Now a new record with indexed column value “D” has been inserted, initially this row will be inserted into the insert buffer in the memory, and later when ever the data A,B,C etc.. has been accessed, that time these newly inserted data will be merged with the actual data, and will be placed together in the same page or sequence of pages based on the indexed column order and changes will be flush to the disk later accordingly.

Here the process of merging the pages data from insert buffer and the actual pages in the buffer pool (retrieved from the disk) to form the new pages with the indexed column order is called the insert buffer merge.


enforcing dict cache limit : During this state MySQL will check for the usage of dictionary cache memory usage and cleans up the dictionary cache if required.
Keeping track / calculation of dictionary cache size will be depends on number of tables and file handlers opened and what is the value of table_cache and other parameters.
For more information go through https://www.percona.com/blog/2009/02/11/limiting-InnoDB-data-dictionary/

flushing log : During this state MySQL flushes the log file content from the log buffer to the log files. you can check the following comment from the source code.

/* The master thread is tasked to ensure that flush of log file happens once every second in the background. This is to ensure that not more than one second of trxs are lost in case of crash when InnoDB_flush_logs_at_trx_commit != 1 */

making checkpoint : Checkpointing is the concept associated with the flushing the in memory changes to the disk which includes flushing dirty pages from the buffer pool to disk. each time when a flush is happened it has to keep track of all the changes flushed and to which LSN Log sequence number the changes have been flushed and repeat the same process to flush other non committed changes, this process is called checkpoint.
  For more information go through https://www.percona.com/blog/2006/05/10/InnoDB-fuzzy-checkpointing-woes/

sleeping :  During this state MySQL is ideal and nothing much is happening in the background.

suspending : This is the state before sleeping, during this state nothing will happen in the background.

waiting for server activity : I didn’t understand much from the source code about this state, but the following is the comment from “Miguel Angel Nieto” on this.

When master thread has no tasks to do, it enters the "sleeping" mode. On that mode it waits one second and then checks again if there is something to do. Then, go back to "sleeping". I guess from that code that you won't see "waiting for server activity". from https://www.percona.com/forums/questions-discussions/MySQL-and-percona-server/14260-can-t-get-percona-5-6-to-go-into-waiting-for-server-activity-state

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers

MySQL Performance Tuning Tips And Techniques.

As every one knows that, Performance tuning is the prominent task of DBA’s. There could be different ways to do performance tuning,and you could see lot of blogs talking about couple of memory related variables to be tuned, and suggests that memory allocation should be in some % based on available memory.

 I actually don’t believe that if we tune and allocate memory based on some % of selected variables is called the performance tuning or we can expert the performance as the docs says, its all depends on the kind of work load and the type of application and the system on which mysql is running.

Performance tuning is not just a task that you can do it in some time, actually it is a continuous process.  As mentioned above there are different ways to do performance tuning, which comprises the following at a high level

1) Operating system tuning
2) Storage Tuning
3) MySQL Server Tuning

When it comes to Operating system and Storage side there could be different parameters that should consider, like the Operating system, server configuration (CPU,Memory,Disk .etc), type of storage (Local,SAN,NAS .etc) and type of application and workload etc. At this point of time I would like to focus on MySQL Server Tuning rather that Operating System and Storage Tuning, and very soon will write one post to cover those 2 topics with respect to MySQL.

Coming to the MySQL performance tuning, as discussed above performance tuning means allocating the right memory to the right parameters. We could see lot of blogs talking about performance tuning mainly focus on few MySQL parameters and % of memory to be allocate to each one of them in general. Just by allocating the memory to those parameters we can’t say that performance tuning is done or the DB server configured to with the best configuration, because that is just predicted configuration based on the type of application and the work load. Though the initial configuration performs better for your DB server while the data size growing the performance tuning issues will come for sure, thats the reason why I mention “Performance Tuning is a continuous process”.

You might be having a question that, then how to find out the bottleneck of MySQL server and do actual MySQL Performance Tuning. That was my question when I was started learning and now I have the answer and would like to share with you.

MySQL Server Performance Tuning : The best / right way of understanding performance tuning bottleneck of MySQL is to check the global status of MySQL Server and try to understand the status variables and the relationship between them, and start tuning corresponding variables.

Lets look at couple of MySQL status variables and understand some performance tuning tips.

InnoDB Buffer Pool Size : To decide whether the innodb buffer pool size is optimal or not for InnoDB workloads , look at the global status of these variables Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests calculate the % of reads that are going to disk and adjust the buffer pool size accordingly.
          NOTE : Initially all the data has to read from the disk as part of warmup process you should consider that before calculate the % of disk reads, and you can’t provide as much memory as the DB size is the optimal to be selected based on the available resources and application workload. If 70-80% of reads served from the memory then that is good enough for a bigger Databases.
 
Key Buffer Size : To check whether key buffer size is optimal or not for MyISAM workload, look at the global status of Key_reads / Key_read_requests. As explained above if 70-80% of reads servers from the memory then it will be good for the bigger Databases.

InnoDB Log Buffer Size :  To tune the optimal value for innodb log buffer, check the global status of Innodb_log_writes / Innodb_log_write_requests, calculate the % and adjust the log buffer size accordingly. Consider the limitation on configuring bigger innodb log buffers and tune accordingly.

Tmp Table Size / Max Heap Table Size : To get the optimal value for temporary table, check the global status of Created_tmp_disk_tables / Created_tmp_tables, calculate the % of tables gets created on the disk and tune this variable accordingly.

Thread Cache Size : To get the optimum value for the the thread cache, check the global status of Threads_created and adjust the threads_cache accordingly.

Table Open Cache : To get the optimum value for table open cache, check the global status of Open_tables / Opened_tables , calculate the % and adjust the Table Open Cache accordingly.

The same process applies to all other major variables, Make sure that you are completely understand how it works and the consequence of these changes then only touch the production systems.

MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers