Innodb Performance Variables

1.innodb_buffer_pool_size: InnoDB depend heavily on the buffer pool and should be set correctly. Typically a good value is 70%-80% of available memory. More precisely, if you have RAM bigger than your dataset setting it bit larger should be appropriate with that keep in account of your database growth and re-adjust innodb buffer pool size accordingly. 

2.innodb_buffer_pool_instances: Multiple innodb buffer pools introduced in InnoDB 1.1 and MySQL 5.5. In MySQL 5.5 the default value for it was 1 which is changed to 8 as new default value in MySQL 5.6. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum). Enabling innodb_buffer_pool_instances is useful in highly concurrent workload as it may reduce contention of the global mutexes.

3.Dump/Restore Buffer Pool: This feature speed up restarts by saving and restoring the contents of the buffer pool. This feature is first introduced in Percona Server 5.5. Oracle MySQL also introduced it in version 5.6, To automatically dump the database at startup and shutdown set innodb_buffer_pool_dump_at_shutdown&innodb_buffer_pool_load_at_startup parameters to ON

4.innodb_log_file_size: Large enough InnoDB transaction logs are crucial for good, stable write performance. But also larger log files means that recovery process will slower in case of crash. However this is not such big issue since great improvements in 5.5. Default value has been changed in MySQL 5.6 to 50 MB from 5 MB (old default), but it’s still too small size for many workloads. Also, in MySQL 5.6, if innodb_log_file_size is changed between restarts then MySQL will automatically resize the logs to match the new desired size during the startup process. Combined log file size is increased to almost 512 GB in MySQL 5.6 from 4 GB. 

5.innodb_log_buffer_size: Innodb writes changed data record into lt’s log buffer, which kept in memory and it saves disk I/O for large transactions as it not need to write the log of changes to disk before transaction commit. 4 MB – 8 MB is good start unless you write a lot of huge blobs.

6.innodb_flush_log_at_trx_commit: When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact. Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improve performance if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes. 

7.innodb_thread_concurrency: It is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero). If you see concurrency issues, you can tune this variable. A recommended value is 2 times the number of CPUs plus the number of disks. It’s dynamic variable means it can set without restarting MySQL server.

8.innodb_flush_method: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it set to O_DIRECT avoids double buffering with buffer pool and filesystem cache. Given that you have hardware RAID controller and battery-backed write cache.

9.innodb_file_per_table: innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. 

Read More

Covered Index optimization using Explain format=json

   Let's start with an example

select City.name as city, Country.name as country, 

group_concat(Language)

from City join CountryLanguage using(CountryCode)

join Country

where City.CountryCode=Country.Code and Continent = 'North America' 

and District='St George'

group by City.name, Country.Name;

Using traditional Explain Keyword show us

mysql> explain select City.name as city, Country.name as country, 

group_concat(Language) from City join CountryLanguage 

using(CountryCode) join Country 

where City.CountryCode=Country.Code 

and Continent = 'North America' 

and District='St George' group by City.name, Country.NameG

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: Country partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 239 filtered: 14.29 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: City partitions: NULL type: ref possible_keys: CountryCode key: CountryCode key_len: 3 ref: world.Country.Code rows: 18 filtered: 10.00 Extra: Using where *************************** 3. row *************************** id: 1 select_type: SIMPLE table: CountryLanguage partitions: NULL type: ref possible_keys: PRIMARY,CountryCode key: CountryCode key_len: 3 ref: world.Country.Code rows: 4 filtered: 100.00 Extra: Using index 3 rows in set, 1 warning (0.00 sec)

EXPLAIN FORMAT=JSON will tell us to which columns we should add covered index

mysql> explain format=json select City.name as city, Country.name as country, group_concat(Language) from City join CountryLanguage using(CountryCode) join Country where City.CountryCode=Country.Code and Continent = 'North America' and District='St George' group by City.name, Country.NameG

*************************** 1. row ***************************

EXPLAIN: {

  "query_block": {

    "select_id": 1,

    "cost_info": {

      "query_cost": "927.92"

    },

        {

          "table": {

            "table_name": "City",

            "access_type": "ref",

            "possible_keys": [

              "CountryCode"

            ],

            "key": "CountryCode",

            "used_key_parts": [

              "CountryCode"

            ],

            "key_length": "3",

            "ref": [

              "world.Country.Code"

            ],

            "rows_examined_per_scan": 18,

            "rows_produced_per_join": 63,

            "filtered": "10.00",

            "cost_info": {

              "read_cost": "630.74",

              "eval_cost": "12.61",

              "prefix_cost": "810.68",

              "data_read_per_join": "4K"

            },

            "used_columns": [

              "ID",

              "Name",

              "CountryCode",

              "District"

            ],

            "attached_condition": "(`world`.`City`.`District` = 'St George')"

          }

        },

Watch the used columns values.Will use this on covered Index.

mysql> alter table City add index cov(CountryCode, District, Name);

Query OK, 0 rows affected (2.74 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> explain format=json select City.name as city, 

Country.name as country, 

group_concat(Language) 

from City join CountryLanguage 

using(CountryCode) join 

Country where City.CountryCode=Country.Code 

and Continent = 'North America' 

and District='St George' group by City.name, Country.NameG

*************************** 1. row ***************************

EXPLAIN: {   "query_block": {     "select_id": 1,     "cost_info": {       "query_cost": "296.28"     },             {           "table": {             "table_name": "City",             "access_type": "ref",             "possible_keys": [               "CountryCode",               "cov"             ],             "key": "cov",             "used_key_parts": [               "CountryCode",               "District"             ],             "key_length": "23",             "ref": [               "world.Country.Code",               "const"             ],             "rows_examined_per_scan": 2,             "rows_produced_per_join": 100,             "filtered": "100.00",             "using_index": true,             "cost_info": {               "read_cost": "34.65",               "eval_cost": "20.19",               "prefix_cost": "108.64",               "data_read_per_join": "7K"             },             "used_columns": [               "ID",               "Name",               "CountryCode",               "District"             ]           }         },

     Exammine the metrics:

• query_cost – 296.28 for the indexed table against 927.92 (smaller is better)
• rows_examined_per_scan – 2 versus 18 (smaller is better)
• filtered – 100 versus 10 (bigger is better)
• cost_info – read_cost  and prefix_cost  for the indexed table are smaller than when not indexed, which is better. However,  eval_cost  and  data_read_per_join  are bigger. But since we read nine times less rows overall, the cost is still better.

Read More

Mysql Buffer Variables

connect_timeout

The number of seconds the mysqld server is waiting for a connect packet before responding with Bad handshake.

join_buffer

The size of the buffer that is used for full joins (joins that do not use indexes). The buffer is allocated one time for each full join between two tables. Increase this value to get a faster full join when adding indexes is not possible. (Normally the best way to get fast joins is to add indexes.)

key_buffer

Index blocks are buffered and are shared by all threads. key_buffer is the size of the buffer used for index blocks. You might want to increase this value when doing many DELETE or INSERT operations on a table with lots of indexes. 

max_allowed_packet

The maximum size of one packet. The message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use.

max_connections

The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires. 

max_connect_errors

If there is more than this number of interrupted connections from a host this host will be blocked for further connections. You can unblock a host with the command FLUSH HOSTS.

max_join_size

Joins that are probably going to read more than max_join_size records return an error. Set this value if your users tend to perform joins without a WHERE clause that take a long time and return millions of rows.

max_sort_length

The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored).

net_buffer_length

The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory, you can set it to the expected size of a query. (That is, the expected length of SQL statements sent by clients. If statements exceed this length, the buffer is automatically enlarged, up to max_allowed_packet bytes.)

record_buffer

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value.

sort_buffer

Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. 

table_cache

The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. MySQL needs two file descriptors for each unique open table. See below for comments on file descriptor limits. 

tmp_table_size

If a temporary table exceeds this size, MySQL generates an error of the form The table tbl_name is full. Increase the value of tmp_table_size if you do many advanced GROUP BY queries.

thread_stack

The stack size for each thread. Many of the limits detected by the crash-me test are dependent on this value. The default is normally large enough.

wait_timeout

The number of seconds the server waits for activity on a connection before closing it.

Read More

Mysql Administration

Create new users using CREATE USER statement

create a new user that connects from local host  with the password.

CREATE USER admin@localhost IDENTIFIED BY 'admin';

create a new user that connects from any host with the password.

CREATE USER admin@'%'IDENTIFIED BY 'admin';

create a new user by insert statement

INSERT INTO user (host,user,password) VALUES('localhost','admin',PASSWORD('admin'));

MySQL changing password using UPDATE statement

USE mysql;

UPDATE user

SET password = PASSWORD('admin')

WHERE user = 'amin' AND host = 'localhost';

Granting Permissions in Mysql

If you want to create a super account that can do anything including being able to grant 

privileges to other users, you can use the following statements:

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';

GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

We can grant multiple privileges using a single grant statement. 
For example, we can create a user that can execute the SELECT, INSERT and UPDATE 

statements using the following statements

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'localhost';

GRANT SELECT,UPDATE,DELETE ON db_name.tbl_name TO 'admin'@'localhost';

Revoking or Terminating user permissions

REVOKE UPDATE, DELETE ON *.*  FROM 'admin'@'localhost';

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'localhost';

*.* resembles -> dbname.tblname here

Droping a user in Mysql

drop user username;

drop user username@'hostname';

Read More