Shulou(Shulou.com)06/01 Report--

MySQL is a very difficult problem for many Linux practitioners, most of which are due to lack of clarity about the situation and handling of database problems.

Before optimizing MySQL, you must understand the query process of MySQL. A lot of query optimization work is actually to follow some principles so that MySQL optimizer can run in a reasonable way.

MySQL query process

Philosophy of Optimization

Note: Optimization is risky, and modifications should be cautious.

Possible optimization problems:

Optimization is not always done for a pure environment, but is likely to be a complex system already in production.

Optimization is inherently risky, but you just don't have the ability to realize and foresee it.

Any technology can solve a problem, but there is always a risk of creating one.

For optimization to solve the problem caused by the problem, control within the acceptable range is fruitful.

Maintaining the status quo or worse is failure.

Optimized requirements:

Stability and business sustainability are often more important than performance.

Optimization inevitably involves change, and change carries risks.

Optimization makes performance better, maintenance and deterioration are equally probable events.

Remember that optimization should be the work of all departments working together and participating together. No single department can optimize the database.

So optimization work is driven by business needs!

Who participates in optimization? Database optimization should involve database administrators, business representatives, application architects, application designers, application developers, hardware and systems administrators, storage administrators, and business stakeholders.

optimization ideas

Optimize what?

There are two main aspects to database optimization:

Security: Data sustainability.

Performance: High-performance access to data.

What is the scope of optimization

Storage, host and operating system aspects:

Host architecture stability

I/O planning and configuration

Swap partition

OS kernel parameters and network issues

Application aspects:

application stability

SQL statement performance

serial access resource

Poor performance Session management

This application is not suitable for MySQL.

Database optimization:

memory

Database structure (physical & logical)

instance configuration

Note: Whether it is designing a system, positioning a problem, or optimizing, it can be performed in this order.

optimization dimensions

There are four database optimization dimensions:

hardware

system configuration

database table structure

SQL and Indexing

Optimization options:

Optimization cost: Hardware> System configuration> Database table structure>SQL and index.

Optimization effect: hardware application> database> architecture (high availability, read-write separation, database and table separation).

Treatment direction: clear optimization goals, performance and safety compromise, prevention.

hardware optimization

① Host side

According to database type, host CPU selection, memory capacity selection, disk selection:

Balance memory and disk resources

Random and sequential I/O

The BBU (Battery Backup Unit) of the host RAID card is turned off

②CPU selection

CPU two key factors: core number, frequency. Choose according to different business types:

CPU-intensive: calculation is more, OLTP frequency is very high CPU, core number is more.

IO-intensive: query comparison, OLAP audit to more, not necessarily high frequency.

③ Memory selection

OLAP type database, requiring more memory, and data acquisition magnitude related. OLTP type data typically has 2 to 4 times the number of CPU cores in memory, and there are no best practices.

④ Storage aspects

Select different storage devices according to different types of stored data, and configure reasonable RAID levels (raid5, raid10, hot spare disk).

For the operating system, do not need too special choice, it is best to do redundancy (raid1)(ssd, sas, sata).

Host RAID card selection:

Redundancy of operating system disks (raid1)

Balance memory and disk resources

Random and sequential I/O

The BBU (Battery Backup Unit) of the host RAID card should be closed

Network equipment

Use network devices with higher traffic support (switches, routers, network cables, network cards, HBA cards). Note: These plans should be considered in the initial design of the system.

Server hardware optimization

Server hardware optimization key points:

Physical Status Light

Self-contained management equipment: remote control card (FENCE equipment: ipmi ilo idarc), switch machine, hardware monitoring.

Third-party monitoring software and equipment (snmp, agent) monitor physical facilities.

Storage equipment: self-contained monitoring platform. EMC2 (acquired by HP), Hitachi (HDS), IBM low-end OEM HDS, high-end storage is its own technology, Huawei storage.

system optimization

CPU: Basically no adjustment is required, and efforts can be made in hardware selection.

Memory: basically do not need to adjust, in terms of hardware selection efforts can be.

SWAP: MySQL tries to avoid using Swap. The default swap in Alibaba Cloud's server is 0.

IO: raid, no lvm, ext4 or xfs, ssd, IO scheduling policy.

Swap adjustment (no swap partition):

The contents of/proc/sys/vm/happiness are changed to 0 (temporary), and/etc/sysctl. Add vm. happiness =0 (permanent) to conf

This parameter determines whether Linux favors Swap or frees file system caches. The lower the number, the more likely it is to free the file system Cache in memory tight situations.

Of course, this parameter only reduces the probability of using Swap, but does not prevent Linux from using Swap.

Modify MySQL's configuration parameter innodb_flush_ method to enable O_DIRECT mode.

In this case, InnoDB's buffer pool bypasses the file system Cache directly to access the disk, but redo log still uses the file system Cache.

It is worth noting that Redo log is overwritten mode, even if the file system cache is used, it will not take up too much.

IO scheduling policy:

#echo deadline>/sys/block/sda/queue/scheduler Temporarily modified to deadline

Permanently modified:

vi /boot/grub/grub.conf

Change to the following:

kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

system parameter adjustment

Linux kernel parameter optimization:

vim/etc/sysctl.conf

net.ipv4.ip_local_port_range = 1024 65535:#user port range

net.ipv4.tcp_max_syn_backlog = 4096

net.ipv4.tcp_fin_timeout = 30

fs.file-max=65535:#System maximum file handle, controls the maximum number of files that can be opened

User limit parameters (MySQL may not set the following configuration):

vim/etc/security/limits.conf

* soft nproc 65535

* hard nproc 65535

* soft nofile 65535

* hard nofile 65535

application optimization

Business applications and database applications are independent.

Firewall: iptables, selinux and other useless services (closed):

chkconfig --level 23456 acpid off

chkconfig --level 23456 anacron off

chkconfig --level 23456 autofs off

chkconfig --level 23456 avahi-daemon off

chkconfig --level 23456 bluetooth off

chkconfig --level 23456 cups off

chkconfig --level 23456 firstboot off

chkconfig --level 23456 haldaemon off

chkconfig --level 23456 hplip off

chkconfig --level 23456 ip6tables off

chkconfig --level 23456 iptables off

chkconfig --level 23456 isdn off

chkconfig --level 23456 pcscd off

chkconfig --level 23456 sendmail off

chkconfig --level 23456 yum-updatesd off

Install GUI server Do not start GUI runlevel 3.

Also, think about whether our business will really need MySQL in the future, or whether we will use other kinds of databases. The highest level of database use is to not use the database.

database optimization

SQL optimization direction:

plan of implementation

index

SQL rewrite

Architecture optimization direction:

high-availability architecture

high-performance architecture

depots and tables

Database parameter optimization

① Adjustment

Example Overall (Advanced Optimization, Extension):

thread_concurrency: #Number of concurrent threads

sort_buffer_size: #sort buffer

read_buffer_size: #sequential read buffer

read_rnd_buffer_size: #random read buffer

key_buffer_size: #index buffer

thread_cache_size：# (1G->8, 2G->16, 3G->32, >3G->64)

② Connection layer (foundation optimization)

Set up reasonable connection customers and connection methods:

max_connections #Maximum number of connections, see the number of transactions set

max_connect_errors #Maximum number of false connections, as large as possible

connect_timeout #Connection timeout

max_user_connections #Maximum number of user connections

skip-name-resolve #Skip domain name resolution

wait_timeout #Wait for timeout

back_log #Number of connections that can be in the stack

③SQL layer (basic optimization)

query_cache_size：default-storage-engine

innodb_buffer_pool_size #No fixed size, 50% test value, see the situation and fine-tune. Try not to exceed 70% of physical memory.

innodb_file_per_table=(1,0)

innodb_flush_log_at_trx_commit=(0,1,2) # 1 is the safest, 0 is the highest performance, 2 is a compromise

binlog_sync

Innodb_flush_method=(O_DIRECT, fdatasync)

innodb_log_buffer_size # 100M or less

innodb_log_file_size # 100M or less

innodb_log_files_in_group #Less than 5 members, generally 2-3 are enough (ilogfile 0-N)

innodb_max_dirty_pages_pct #Flush dirty pages to disk 75 percent of the time.

log_bin

max_binlog_cache_size #may not be set

max_binlog_size #may not be set

innodb_additional_mem_pool_size #For machines with less than 2GB of RAM, the recommended value is 20MB. 32 gigabytes of memory above 100 megabytes

Reference article:

https://www.cnblogs.com/zishengY/p/6892345.html

https://www.jianshu.com/p/d7665192aaaf

Source: www.cnblogs.com/clsn/p/8214048.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.