![首頁 Logo Keith ]p !!](/themes/mytheme/logo.png){kind=logo}
最後更新: 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
- Shrink ibdata1 file
- Upgrade
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.
Shrink ibdata1 file
ls -h /var/lib/mysql/ib*
-rw-rw---- 1 mysql mysql 4.8G Aug 16 23:00 /var/lib/mysql/ibdata1
...mysql -e 'show databases' # 如果有其他 DB, 那再 dump 出來
mysqldump -R DBNAME > DBNAME.sql
mysql -e 'DROP DATABASE DBNAME' # 建議 backup 好再 DROP
service mysqld stop
rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile*
service mysqld start
mysql -e 'CREATE DATABASE DBNAME CHARACTER SET utf8'
mysql DBNAME < DBNAME.sql
Upgrade
# MySQL 5.1 -> 5.7
sed -i 's/ROW_FORMAT=FIXED//g' db.sql
