Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What ideas should be taken to optimize mysql?

2025-03-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

The following mainly brings you what ideas to optimize mysql, hope that these contents can bring you practical use, this is also my editor to optimize mysql from which ideas to start with the main purpose of this article. All right, don't talk too much nonsense, let's just read the following.

General ideas for emergency tuning:

In view of the sudden business to deal with stutter, can not carry out normal business processing! The scene that needs to be solved immediately!

1. Show processlist2, explain select id,name from stu where name='clsn'; # ALL id name age sexselect id,name from stu where id=2-1 function result set > 30 position show index from table;3, judge by execution plan, index problem (whether it is reasonable or not) or statement itself problem 4. Show status like'% lock%'; # query lock status kill SESSION_ID; # kill the problematic session

Conventional tuning ideas:

For business cyclical Catton, for example, business is particularly slow at 10-11:00 every day, but it can still be used, and it will be fine after this period of time.

1. Check the slowlog, analyze the slowlog, and analyze the statements that are slow to query.

2. Check all slow sentences one by one according to a certain priority.

3. Analyze top sql, debug explain, and check the execution time of the statement.

4. Adjust the index or the statement itself.

System level

For cpu:

Vmstat 、 sar top 、 htop 、 nmon 、 mpstat

Memory:

Free, ps-aux,

IO devices (disk, network):

Iostat 、 ss 、 netstat 、 iptraf 、 iftop 、 lsof 、

The vmstat command describes:

Procs:r shows how many processes are waiting for CPU time. B displays the number of processes in uninterruptible hibernation. Waiting for Icano

Memory:swpd shows the number of blocks swapped to disk. Unused blocks, user buffered blocks, number of blocks used for the operating system

Swap: the number of blocks that the operating system swaps from disk to memory and from memory to disk per second. S1 and S0 preferably 0

Io: the number of blocks written to device b0 that are read from b1 from the device per second. It reflects the disk Imax O

System: shows the number of interrupts per second (in) and the number of context exchanges (cs)

Cpu: displays the CPU time used to run user code, system code, idle, and wait for Icano

Iostat command description

Example command: iostat-dk 1 5

Iostat-d-k-x 5 (view device usage (% util) and response time (await))

Tps: the number of transmissions per second for this device. "one transfer" means "one Ipicuro request". Multiple logical requests may be merged into "one Ipicuro request".

Iops: when the hardware is shipped from the factory, the manufacturer defines a maximum number of IO per second, and the size of the "once transfer" request is unknown.

KB_read/s: the amount of data read from the device (drive expressed) per second

KB_wrtn/s: the amount of data written to the device (drive expressed) per second

KB_read: total amount of data read

KB_wrtn: the total amount of data written; these units are Kilobytes.

Solutions to problems at the system level

Do you think it is better to have high load or low load?

In actual production, it is generally believed that there is nothing wrong with cpu as long as it does not exceed 90%.

Of course, the following special cases are not ruled out:

Problem 1: high cpu load and low IO load

There is not enough memory

Poor disk performance

SQL problems-> go to the database layer to further troubleshoot sql problems

There is something wrong with IO (critical disk, poor raid design, raid downgrade, lock, tps too high per unit time)

Tps is too high: a large number of small data IO, a large number of full table scans

Problem 2: high IO load and low cpu load

A large number of small IO writes:

Autocommit, which produces a large number of small IO

A fixed value of IO/PS disk, the maximum number of IO per second defined by the manufacturer when the hardware is shipped from the factory.

A large number of large IO writes

The probability of SQL problem is high.

Problem 3: both IO and cpu have a high load

Insufficient hardware or problem with SQL

V. Foundation optimization

Optimization idea

Locate the problem point:

Hardware-- > system-- > Application-- > Database-- > Architecture (high availability, read-write separation, sub-database and sub-table)

Processing direction:

Make clear the compromise and prevention of optimization objectives, performance and security.

Hardware optimization

Host:

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

Balance memory and disk resources

Random I _ Pot O and Sequential I _ Pot O

BBU (Battery Backup Unit) shutdown of the host RAID card

Choice of cpu:

Two key factors of cpu: kernel number and dominant frequency

Choose according to different types of business:

Cpu-intensive: more computing, more cpu with high OLTP frequency and more cores

IO-intensive: query comparison, more OLAP cores, 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 according to the type of data stored

Reasonably configured RAID level (raid 5, raid 10, hot spare)

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:

Achieve operating system disk redundancy (raid1)

Balance memory and disk resources

Random I _ Pot O and Sequential I _ Pot O

The BBU (Battery Backup Unit) of the host RAID card is to be shut down

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

Swap tuning (without using swap partitions)

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 too much even if the cache of the file system is used.

IO scheduling policy:

System parameter adjustment

Kernel parameter optimization of Linux system:

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

Application optimization

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

Do not start runlevel 3 on the server that installs the graphical interface. In addition, think about 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.

VI. 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

Database parameter optimization

Adjust:

Instance as a whole (advanced optimization, extension)

Connection layer (Foundation Optimization)

Set up a reasonable way to connect customers and connections

For the above optimization of mysql from which ideas to start, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report