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

Preface

Optimization is risky, so you need to be careful when you get involved!

1. What are the possible problems caused by optimization?

Optimization is not always done for a simple environment, but is likely to be a complex system that has been put into production.

Optimization methods are inherently risky, but we may not be able to realize and foresee them!

Any technology can solve a problem, but there must be a risk of bringing a problem!

For optimization, it is fruitful to control the problems brought about by solving problems within an acceptable range, and it is a failure to maintain the status quo or worse.

2. the stability of optimized requirements and the persistence of the business are usually more important than performance. Optimization inevitably involves change, and change is risky. Optimize to improve performance, maintain and change equal probability events. Keep in mind that optimization should be the work of various departments, and no single department can optimize the database. All optimization work is driven by business needs. 3. Who will participate in the optimization?

DBA, business unit representatives, application designers, application developers, operators and other relevant personnel should participate in database optimization.

4. Optimization ideas

There are two main aspects in database optimization: security and performance.

Security: data sustainability. Performance: high-performance access to data. 5. What is the scope of optimization?

In terms of storage, host, and operating system:

Host architecture stability; Swap O planning and configuration; Swap switching partition; OS kernel parameters and network problems; application aspect; application stability; SQL statement performance; serial access resources; poor performance session management; whether this application is suitable for MySQL

Database optimization:

Memory; database structure (physical & logical); instance configuration

Note: whether in the design of the system, positioning problems or optimization, can be carried out in the above order.

6. Optimize the dimension

There are four database optimization dimensions:

Hardware; system configuration; database table structure; SQL and index

Optimize the selection:

Optimization cost: hardware > system configuration > database table structure > SQL and index optimization effect: hardware application-> database-- > architecture (high availability, read-write separation, sub-database and sub-table)

Processing direction

Clear optimization objectives, performance and safety of the compromise, prevention.

Hardware optimization

Host:

Depending on the database type, host CPU selection, memory capacity selection, disk selection, balanced memory and disk resources random I CPU O and sequential I hand O host raid card BBU (Battery Backup Unit) off

Choice of cpu:

Two key factors of cpu: kernel number and dominant frequency

Choose according to different types of business:

Cpu-intensive: more computing, high OLTP frequency of cpu, more cores IO-intensive: query comparison, OLAP core number is more, the main frequency is not necessarily high

Choice of memory:

OLAP type database, which requires more memory, is related to the order of magnitude of data acquisition. OLTP type data is generally 2 to 4 times the number of cpu cores in memory, and there are no best practices.

Storage:

Select different storage devices to configure a reasonable RAID level (raid5, raid10, hot spare) according to the type of data stored

For the operating system, there is no need for too special choices, it is best to do a good job of raid1 (ssd, sas, sata)

Raid card: host raid card selection:

Achieving operating system disk redundancy (raid1) balancing memory and disk resources Random Icano and Sequential Ipicot O host raid card BBU (Battery Backup Unit) to be turned off.

For network equipment:

Use traffic to support higher network devices (switches, routers, network cables, network cards, HBA cards)

Note: the above plans should be considered in the initial design of the system.

Server hardware optimization

1. Physical status lamp:

2. Built-in management equipment: remote control card (FENCE device: ipmi ilo idarc), switch, hardware monitoring.

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

4. Storage device: built-in 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 do not need to adjust, just work on the selection of hardware.

Memory:

Basically do not need to adjust, just work on the selection of hardware.

SWAP:

MySQL tries to avoid using swap. The default swap in Aliyun's server is 0

IO:

Scheduling policies for raid, no lvm, ext4 or xfs, ssd, IO

Do not use swap partitions

Change the content of / proc/sys/vm/swappiness to 0 (temporary)

Add vm.swappiness=0 to / etc/sysctl.conf (permanent)

This parameter determines whether Linux prefers to use swap or release the filesystem cache. When memory is tight, the lower the value, the more likely it is to free the file system cache. Of course, this parameter only reduces the probability of using swap, but does not prevent Linux from using swap.

Modify the configuration parameter innodb_flush_method of MySQL to enable O_DIRECT mode. In this case, InnoDB's buffer pool directly bypasses the file system cache to access the disk, but redo log still uses the file system cache. It is worth noting that Redo log is in override mode and does not take up much even if the file system's cache is used.

IO scheduling strategy

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

The permanent modifications are as follows:

Change $vi / boot/grub/grub.conf# to the following: kernel / boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet11, system parameter adjustment

Kernel parameter optimization of Linux system:

$vim / etc/sysctl.confnet.ipv4.ip_local_port_range = 10246553user port range net.ipv4.tcp_max_syn_backlog = 4096net.ipv4.tcp_fin_timeout = 30fs.file-max=65535# system maximum file handle, which controls the maximum number of files that can be opened

User limit parameters (mysql can not set the following configuration)

$vim/etc/security/limits.conf* soft nproc65535* hard nproc65535* soft nofile65535* hard nofile6553512, application optimization

Business applications and database applications are independent, firewall: iptables, selinux and other useless services (shut down):

$chkconfig-- level 23456 acpid off$ chkconfig-- level 23456 anacronoff$ chkconfig-- level 23456 autofsoff$ chkconfig-- level 23456 avahi-daemonoff$ chkconfig-- level 23456 bluetoothoff$ chkconfig-- level 23456 cupsoff$ chkconfig-- level 23456 firstbootoff$ chkconfig-- level 23456 haldaemonoff$ chkconfig-- level 23456 hplipoff$ chkconfig-- level 23456 ip6tablesoff$ chkconfig-level 23456 iptablesoff$ chkconfig-level 23456 isdnoff$ chkconfig-level 23456 pcscdoff$ chkconfig-level 23456 sendmailoff$ chkconfig-level 23456 yum-updatesdoff

The server that installs the graphical interface should not start runlevel 3. In addition, whether our business will really need MySQL or use other kinds of databases in the future. The highest level of using a database is not using a database.

13. Database optimization

SQL optimization direction:

Execute plan, index, SQL rewrite

Direction of architecture optimization:

High availability architecture, high performance architecture, sub-database and sub-table

14. Optimization of database parameters

Adjust:

Instance overall (Advanced Optimization, extension) thread_concurrency # number of concurrent threads sort_buffer_size # sort cache read_buffer_size # Sequential read cache read_rnd_buffer_size # Random read cache key_buffer_size # Index cache thread_cache_size # Thread cache (1G-> 8,2G-> 16, 3G > 32m 3G-> 64)

Connection layer (Foundation Optimization)

Set up a reasonable way to connect customers and connections:

Max_connections # maximum number of connections, depending on the number of transactions set max_connect_errors # maximum number of error connections, large 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 # the number of connections that can be in the stack

SQL layer (Foundation Optimization)

Query_cache_size: query cache

OLAP type database, need to focus on increasing this memory cache.

But it usually does not exceed GB.

For data that is often modified, the cache is invalidated immediately.

We can use memory database (redis, memecache) to replace its function.

15. Storage engine layer (innodb basic optimization parameters) default-storage-engineinnodb_buffer_pool_size # has no fixed size and 50% test value. Fine-tune it depending on the situation. But try not to exceed the physical memory 70% innodb memory filewise perpendicular table = (1mem0) innodb_flush_log_at_trx_commit= (0mem1d2) # 1 is the safest, 0 is the highest performance 2 compromise binlog_syncInnodb_flush_method= (O_DIRECT, fdatasync) innodb_log_buffer_size # 100m below innodb_log_file_size # 100m innodb_log_files_in_group # less than 5 members, generally 2-3 enough (iblogfile0-N) innodb_max_dirty_pages_pct # to write memory dirty pages to disk when it reaches 75%. Max_binlog_cache_size # can not set up max_binlog_size # or set machines with innodb_additional_mem_pool_size # less than 2GB of memory. The recommended value is 20m. 32G memory over 100m

Note: in a production environment, the parameters of the database are changed according to the requirements, but it is not necessary to restart the database in order to change a configuration parameter, but we can first set the configuration item that needs to be changed to the global environment variable to take effect, and then write it to the configuration file. As long as the database is not restarted, the set environment variable will not be invalid. Once restarted, the configuration file will take effect.

The configuration file can be in the following format:

$cat / etc/ my.cnf [mysqld] basedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3306server_id=1 slow_query_log = 1slow_query_log_file = / usr/local/mysql/data/slow-query.loglong_query_time = 1log-queries-not-using-indexesmax_connections = 1024back_log = 128wait_timeout = 60interactive_timeout = 7200key_buffer_size=256Mquery_cache_size = 256Mquery_cache_type=1query_cache_limit=50Mmax_connect_errors=20sort_buffer_size = 2Mmax_allowed_packet=32Mjoin_buffer _ size=2Mthread_cache_size=200innodb_buffer_pool_size = 2048Minnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size=32Minnodb_log_file_size=128Minnodb_log_files_in_group=3log-bin=mysql-binbinlog_cache_size=2Mmax_binlog_cache_size=8Mmax_binlog_size=512Mexpire_logs_days=7read_buffer_size=1Mread_rnd_buffer_size=16Mbulk_insert_buffer_size=64Mlog-error = / usr/local/mysql/data/mysqld.err

-this is the end of this article. Thank you for reading-

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.