In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.