最後更新: 2018-05-18

目錄

• Check Version
• Mysql slow to connect | root@localhost login 失敗
• max_connect_errors
• max_allowed_packet
• Aborted_clients & Aborted_connects
• Find duplicate records in MySQL
• Performance
• tmp DB
• LOG
• Ignoring the lost+found Directory in your Datadir
• MySQL - Too many ... (max_connections)
• Mysql Script
• remove tail zero
• Never want your MySQL to cause the operating system to swap
• MySQL with huge page
• sleep
• Check tables size in DB
• Queries vs Questions

 

---

Check Version

 

#A - version()

select version();

+-----------+
| version() |
+-----------+
| 4.1.22    |
+-----------+

OR

#B - Server System Variables

SHOW VARIABLES LIKE "%version%";

+-------------------------+-------------------------------------------------------------+
| Variable_name           | Value                                                       |
+-------------------------+-------------------------------------------------------------+
| protocol_version        | 10                                                          |
| version                 | 4.1.22                                                      |
| version_bdb             | Sleepycat Software: Berkeley DB 4.1.24: (November  3, 2006) |
| version_comment         | Source distribution                                         |
| version_compile_machine | i686                                                        |
| version_compile_os      | redhat-linux-gnu                                            |
+-------------------------+-------------------------------------------------------------+

 

---

Mysql slow to connect | root@localhost login 失敗

 

Mysql slow to connect | root@localhost login 失敗

skip-name-resolve <-- disable DNS host name lookups

# HOST_CACHE_SIZE define (default value: 128)  <-- --skip-host-cache

FLUSH HOSTS statement

後果:

root@localhost 會 login 唔到

因為 privileges table  上的 hostnames 會無效左 (root@localhost)

Checking

mysql> SELECT user, host FROM mysql.user;

 

---

max_connect_errors

 

Host '*' is blocked because of many connection errors. 

Unblock with 'mysqladmin flush-hosts'

Block until you flush-hosts / restart service

Default

# Ver < MySQL 5.6.6 => 10

# Ver > MySQL 5.6.6 => 100

Setting

max_connect_errors=10

 * first verify that there is nothing wrong with TCP/IP connections

 

---

max_allowed_packet

 

The packet message buffer is initialized to net_buffer_length bytes,
    but can grow up to max_allowed_packet bytes when needed.

It should be as big as the largest BLOB you want to use.

 

The maximum size of a BLOB or TEXT object is determined by its type,
    but the largest value you actually can transmit between the client and server is determined by
    the amount of available memory and the size of thecommunications buffers.

Error:

MySQL Error: 1153 (Got a packet bigger than 'max_allowed_packet' bytes)
Session halted.

查看:

# unit: byte

SHOW VARIABLES like "max_allowed_packet";

Output

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 8387584 |
+--------------------+---------+

 

/etc/my.cnf

max_allowed_packet = 1M   // default

 

---

Aborted_clients & Aborted_connects

 

log_warnings=1

... 717781 [Note] Aborted connection 717781 to db: 'MyDB' user: 'MyDB' host: 'localhost' (Got an error reading communication packets)

... 715754 [Note] Aborted connection 715754 to db: 'MyDB' user: 'MyDB' host: 'localhost' (Got an error writing communication packets)

A communication error occurs it increments the status counter for either "Aborted_clients" or "Aborted_connects"

Aborted_clients

The number of connections that were aborted because the client died without closing the connection properly.
The client slept for longer than the defined "wait_timeout" or "interactive_timeout" seconds
(the connection gets forcibly closed by the MySQL server)
The client terminated abnormally or exceeded the max_allowed_packet for queries

wait_timeout=28800                     # Default

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

interactive_timeout=28800            # Default

The number of seconds the server waits for activity on an interactive connection before closing it.
An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect().

connect_timeout=10

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

Aborted_connects

The number of failed attempts to connect to the MySQL server.

For additional connection-related information, check the Connection_errors_xxx status variables and the host_cache table.

Notes

PHP does not disconnect from the server after every request served,
but keeps the connections for future requests.
When not used, these connections are in a "sleep" state, as can be seen with "mysqladmin processlist".

Usually, the mysql server will kill such processes if their "idle time" exceeds the value of wait_timeout.
(The "Time" value in the processlist = idle time)

/etc/my.cnf:

[mysqld]
...
# Default: 28800
wait_timeout=180

systemctl restart mariadb

Checking

show variables like 'wait_timeout'

 

---

Find duplicate records in MySQL

 

SELECT COUNT(*), column1, column2 FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;

i.e.

SELECT COUNT(*), domain FROM control_transports
GROUP BY domain
HAVING COUNT(*)>1;

 

---

Performance

 

Disable DNS host name lookups by starting mysqld with the

--skip-name-resolve

HOST_CACHE_SIZE (default value: 128)

 

key_buffer_size / table_cache

 

buffer

All threads share the MyISAM key buffer;

its size is determined by the key_buffer_size variable.

check:

shell> mysqladmin variables
shell> mysqladmin extended-status

table_cache

The number of open tables for all threads.

Increasing this value increases the number of file descriptors that mysqld requires.

當 Server Status Variables 看到 Opened_tables 過大時就要增加 table_cache 了

key_buffer_size

Index blocks for MyISAM and ISAM tables are buffered and are shared by all threads.

table_cache = 1600
key_buffer_size = 1500M

 

---

tmp DB

 

# Show Setting

show global status like 'Created_tmp%';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 54    |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 212   |
+-------------------------+-------+

# Setting

tmp_table_size = 256M

max_tmp_tables = 32

 

---

LOG

 

# query logging

log=/var/log/mysql.log

# error logging

log-error=/var/log/mysql.err

# Flush all logs

mysqladmin flush-logs

# log-slow-queries

# more than 3 seconds

long_query_time=3

# slow.log in the MySQL data directory.

log-slow-queries=log-slow-queries.log

 

---

Ignoring the lost+found Directory in your Datadir

 

--ignore-db-dir      # ignore the given directory name for purposes of the SHOW DATABASES statement or INFORMATION_SCHEMA tables

# 查看 DB 是否支持 "--ignore-db-dir"

# MySQL verion > 5.6.3 才用到

# MariaDB version > 5.5.28

mysql --version

AND

show global variables like 'ignore_db_dirs';

 

Setting

[mysqld]
# omit multiple directories
ignore-db-dir=lost+found
ignore-db-dir=_backup

 

---

Mysql Script

 

mysql -u root -pPASSWORD DB_NAME -e "select loginid,password from user where active='Y'" > /tmp/user.tmp

 

---

Remove tail zero

 

i.e.

SELECT email, quota/1024/1024 as 'Quota(MB)' FROM `mail_user` WHERE 1

user@domain 300.00000000

解決

SELECT ROUND(2/50)

or

SELECT CEILING(2/50)

 

---

Never want your MySQL to cause the operating system to swap

 

[1] Whole system

# echo "vm.swappiness=1" >> /etc/sysctl.conf

# Checking: sysctl vm.swappiness

echo 1 > /proc/sys/vm/swappiness

[2] MySQL Only

 * works on systems that support the mlockall() system call

 * need to run mysql as “root” user

my.cnf

[mysqld]
memlock

 

---

MySQL with huge page

 

優點

•  * In MySQL, large pages can be used by InnoDB (innodb_buffer_pool_size)
      (MyISAM storage engine uses normal memory allocation.)
•  * reduces the overhead of Translation Lookaside Buffer (TLB) lookups
•  * Lock memory to reduce the performance degradation caused by memory release and consumption

# To check whether supports

grep -i huge /proc/meminfo

/etc/sysctl.conf

# Set the number of pages to be used.
# Each page is normally 2MB
vm.nr_hugepages = N1

# Set the group number that is permitted to access this memory
# Get: id mysql
vm.hugetlb_shm_group= gid

# Increase the amount of shmem permitted per segment ()
kernel.shmmax = N2

# Increase total amount of shared memory (number of pages(4KB))
kernel.shmall = N3

N1: Calculate MySQL Maximum Memory Usage(vm.nr_hugepages)

• show variables like 'query_cache_size';
• show variables like 'table_open_cache';
• show variables like 'innodb_buffer_pool_size';
• show variables like 'innodb_log_file_size';

 

/etc/security/limits.conf

mysql hard memlock unlimited
mysql soft memlock unlimited

my.cnf

[mysqld]
large-pages

 

---

Sleep

 

# This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0

• mysql_pconnect (persistent connections)
• miss mysql_close

/etc/my.cnf

[mysqld]
# Default: 28800 (8 hrs)
wait_timeout=180

 

---

ERROR 2013

 

Msg

ERROR 2013 (HY000) at line 3447: Lost connection to MySQL server during query

Fix

connect_timeout = 10
max_allowed_packet = 10M
wait_timeout = 3600
interactive_timeout = 3600

 

---

Check tables size in DB

 

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "DB_NAME"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

 

---

Queries vs Questions

 

Queries

This variable includes statements executed within stored programs

Questions

This includes only statements sent to the server by clients and
 no longer includes statements executed within stored programs, unlike the Queries variable.