Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL performance view and configure finishing Daquan     - Ubuntu GCC, G ++ and fortran Version Switch (Linux)

- Linux centralized log server rsyslog (Server)

- Android shutdown (reboot) process (Programming)

- Oracle 12C truncate table cascade (Database)

- Server Security Analysis attack on Linux (Linux)

- Linux (RHEL6 CENTOS6 OLE6) VNC-SERVER Installation and Configuration (Server)

- Java string intern constant pool resolution Introduction (Programming)

- Windows 7 hard disk to install Ubuntu 15.04 (Linux)

- System with Windows Remote Desktop to connect Ubuntu 15.04 (Linux)

- MySQL appears data too long for column 'name' at row 1 to solve (Database)

- Install VLC player on Ubuntu 14.10 (Linux)

- 22 Port weak passwords and SSH connection program of the Linux server (Linux)

- socket busy poll of Linux kernel 3.11 to avoid sleep switch (Linux)

- Installation GitLab appears ruby_block supervise_redis_sleep action run (Linux)

- Reason C ++ program running under Linux a segmentation fault core dumped in (Programming)

- How to become a better Node.js developers in 2016 (Programming)

- Oracle 10g relations with the constraint of column properties NULLABLE (Database)

- How to modify the SQL Server auto-increment value and the corresponding solution (Database)

- Compare Oracle MySQL (ICP) index condition pushdown is described (Database)

- Using IPFilter bridge filter in the FreeBSD system (Linux)

 
         
  MySQL performance view and configure finishing Daquan
     
  Add Date : 2018-11-21      
         
         
         
  MySQL database performance Views:
Show proceelist view the status of the database connection and the total number of connections
show globalstatus like 'Max_used_connections'; the high number of connections to view the largest database of response to the connection, the maximum number of connections less than 10% of the mysql
Show processlist View connection status
 showglobal status like 'Thread%'; see the process usage
show globalstatus like 'qcache%'; view cache usage
show statuslike 'key_read%' proportion key_reads / key_read_requests should be as low as possible, at least 1: 100 or 1: 1000
Table show statuslike "open%" view open database

Currently the database on the production line is generally occupied by a great memory, the main reason is because the database configuration file parameters misconfigured reasons, resulting in a large buffer space to read:
Related parameters can refer to the configuration:
Some important parameters of the database:
[Mysqld]
Skip-external-locking // skip the external lock for multiple processes under myisam data table Locking
Key_buffer_size = 384M // specified index buffer size, 4G memory index is generally 384M or 512M, for this configuration is generally worth using to view the status value key_read_request to check whether a reasonable ratio of 1: 100 and 1: 1000 or so.
table_open_cache = 2048 // mysql database table is going to be opened to read some data to table_open_cachede, and when mysql can not find the data in the database will be read to the database, you can reduce the file opening and closing times, the configuration is reasonable for the configuration of the number of general view of the number of open tables. Use show global status like 'open% _tables' open to view the number of tables making the settings is generally recommended to open_tables / opened_tables> = 0.85.
read_buffer_size = 2M // read size used in the query buffer. This parameter is only valid for myisam table
read_rnd_buffer_size = 8M // for all storage engines are limited
myisam_sort_buffer_size = 64M // rebuild the index of the maximum file size
thread_concurrency = 4 // worth this size is generally double the number of cpu core, this value is an important part of the configuration object 5.5 later this value is invalid
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = / usr
datadir = / var / lib / mysql
tmpdir = / tmp
lc_messages_dir = / usr / share / mysql
lc_messages = en_US
skip-name-resolve
lower_case_table_names // # ignores table name case
connect_timeout = 15 // default like
wait_timeout = 600 // default like
max_allowed_packet = 16M // default is 16M, but when it came to the large field of the message packet is too large to amend the problem, generally increased to 32M
thread_cache_size = 128 // cache to save the number of threads, memory 8G generally set to about 64 or 128, 3G is 16 to 32,2G
sort_buffer_size = 8M // when the reorder buffer 100 is connected consumes 0.8G memory
bulk_insert_buffer_size = 16M // bulk insert default 8M cache
tmp_table_size = 256 M // default to 32M
max_heap_table_size = 256M
query_cache_limit = 0 # do not use data cache, there is a need to use memcache general external cache, the default is 1M
query_cache_size = 0 # mysqlisam generally used for optimization, the proposed closure of this feature
log_bin = / var / log / mysql / mariadb-bin // note space log-bin storage
log_bin_index = /var/log/mysql/mariadb-bin.index
expire_logs_days = 10 // store log-bin period of time
max_binlog_size = 100M // maximum binlog size, not to be set
innodb_buffer_pool_size = 16384M // generally configured as a 50% -80% of system memory
innodb_log_buffer_size = 32M // log file to determine the memory used
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M // determine the size of data files, a large installation can improve performance but also increases recovery time of the failed database needed
 
In innode configuration of two parameters are important and innodb_log_file_size innodb_buffer_pool_size
In official documents mysql's generally recommended to configure innodb_buffer_pool_sixe about 50% to 80% memory
show status like 'Innodb_buffer_pool_%'; View innode performance
 
 
[Mysqldump]
quick // in exporting large table when the need to open, usually after backup 20G, do not use the form mydqldump
     
         
         
         
  More:      
 
- Linux module mechanism of (Programming)
- ARM platform compiler installation Golang (Linux)
- About Hibernate cache, you want the latest data have trouble even session.clear (Database)
- Oracle RAC node on the expulsion of the inspection process on OEL6.3 (Database)
- CentOS 6.5 install Maven and Nexus warehouse agent (Server)
- Ceph single / multi-node installation summary Powered by CentOS 6.x (Server)
- MySQL rollback insert statement causes ibd file grows double (Database)
- Shell script to crawl through AWR SQL Report Problems (Database)
- Docker: Mirroring and container operations (Server)
- grub boot error resolution examples (Linux)
- Which file system is most suitable for your Linux system (Linux)
- Ubuntu install image browser and manager Phototonic 1.6.17 (Linux)
- Digital jQuery scrolling effect (Programming)
- Cool Android realization SVG animation (Programming)
- CentOS7 install and configure Nagios (Server)
- Distributed Hadoop1.2.1 cluster installation (Server)
- Android View event delivery (Programming)
- Ubuntu 64-bit installation Adobe Reader 9.5.5 (Linux)
- Ubuntu 14.04 + xRDP + Xfce implement Windows Remote Desktop Connection (Linux)
- Django Signals from practice to source code analysis (Programming)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.