In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces "how to solve the problem of slowing down the database". In the daily operation, I believe that many people have doubts about how to solve the problem of slowing down the database. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to solve the problem of database slowing down"! Next, please follow the editor to study!
Chapter 1 check the status of the system
1.1 use sar to check the operating system for IO problems
1.2 focus on memory vmstat
1.3 find the session that uses the resource-intensive Oracle and the statements it executes
1.4 find the first ten sql statements with poor performance
Chapter II check session state
How should we start when the database slows down?
When the application administrator announces that the application is slow and the database is slow, when the Select where Oracle DBA does several examples on the database also finds the same problem, sometimes it will be impossible to start, because DBA believes that the various birth rates of the database meet the recommendations of Oracle documents. In fact, today's optimization has been transformed to optimization waiting (waits). In practice, the most fundamental point of performance optimization is also concentrated in Ibank O, which is the most important aspect that affects performance. It is a better way to find the deficiency in Oracle library and unreasonable utilization of some resources in the operating system by waiting in the system. Here are some practical experiences to share with you, this article focuses on the Unix environment.
Chapter 1 check the status of the system
Check the status of the system through some tools of the operating system, such as CPU, memory, swap, and disk utilization. Based on experience or compared to the normal state of the system, sometimes the system appears to be idle, which may not be a normal state, because cpu may be waiting for the completion of IO. In addition, you should also pay attention to those processes that take up system resources (cpu, memory).
1.1 use sar to check the operating system for IO problems
# sar-u 210-that is, inspections are conducted every 2 seconds for a total of 20 times.
Example of the result:
Note: under redhat,% system is called% wio.
Linux 2.4.21-20.ELsmp (YY075) 05Unix 19max 2005
10:36:07 AM CPU user nice system idle
10:36:09 AM all 0.00 0.00 0.13 99.87
10:36:11 AM all 0.00 0.00 0.00 100.00
10:36:13 AM all 0.25 0.00 0.25 99.49
10:36:15 AM all 0.13 0.00 0.13 99.75
10:36:17 AM all 0.00 0.00 0.00 100.00
Where:
% usr refers to the percentage of cpu resources used by the user process
% sys refers to the percentage of system resources using cpu resources
% wio refers to the percentage of people waiting for io to complete, which is a noteworthy item
% idle is the percentage of idle.
If the value of the wio column is large, such as more than 35%, there is a bottleneck in the IO of the system, and CPU spends a lot of time waiting for the completion of CPU O. The small Idle indicates that the system CPU is busy. As in the example above, you can see that the average wio is 11, indicating that there is no particular problem with cpu O, while the idle value is zero, indicating that the IDL is running at full capacity.
When there is an IO problem in the system, it can be solved from the following aspects:
Contact the technical support of the corresponding operating system to optimize this aspect, such as the striping of hp-ux in delineating volume groups.
Find unreasonable sql statements in Oracle and optimize them
The tables with frequent visits in Oracle are not only indexed reasonably, but also the table spaces are stored in order to avoid hot spots on access, and the tables are partitioned reasonably.
1.2 focus on memory
The common tool is vmstat, and for hp-unix, you can use glance. For Aix, you can use topas. When it is found that the pi column in vmstat is non-zero, the value of free column in memory is very small, and the utilization of memory in glance and topas is more than 80%, it means that the memory should be adjusted. Generally speaking, the methods are as follows:
The amount of memory allocated to Oracle should not exceed 1 / 2 of the system memory, which is generally kept at 40% of the system memory.
Add memory to the system
If you have a lot of connections, you can use MTS
Full patch is applied to prevent memory vulnerabilities.
1.3 find the session that uses the resource-intensive Oracle and the statements it executes
Hp-unix can use glance or top. IBM AIX can use topas. In addition, you can use ps's command.
Through these programs, you can find the process numbers of these processes that use a lot of system resources, and you can find out which sql the pid is executing through the following sql statement. This sql is best executed in pl/sql developer, toad and other software:
SELECT a.username, a.machine, a.program, a.sid, a.serial#, a.status
C.piece, c.sql_text
FROM v$session a, v$process b, v$sqltext c
WHERE b.spid = 'ORCL'
AND b.addr = a.paddr
AND a.sql_address = c.address (+)
ORDER BY c.piece
You can analyze the sql you get to see if its execution plan is indexed. It is optimized to avoid full table scans to reduce IO waits, thus speeding up the execution of statements.
Tip: when optimizing sql, you often encounter statements that use in, so you must replace it with exists, because Oracle does it the way Or does when dealing with In, even if you use the index. For example:
SELECT col1, col2, col3 FROM table1 a
WHERE a.col1 NOT IN (SELECT col1 FROM table2)
It can be replaced by:
SELECT col1, col2, col3 FROM table1 a
WHERE NOT EXISTS
(SELECT'x 'FROM table2 b WHERE a.col1=b.col1)
1.4 find the first ten sql statements with poor performance
SELECT * FROM (SELECT parsing_user_id, executions, sorts, command_type
Disk_reads, sql_text FROM v$sqlarea
ORDER BY disk_reads DESC)
WHERE ROWNUM6
AND st.wait_time=0
AND st.event NOT LIKE'% SQL%'
ORDER BY physical_reads DESC
Some notes on the retrieved results:
1. The above is in the order of the physical reads that have occurred for each waiting session, because it is related to the actual Icano.
2. You can take a look at what these waiting processes are up to, and whether the statements are reasonable?
SELECT sql_address FROM v$session WHERE sid=
SELECT * FROM v$sqltext WHERE address=
Execute the above two statements to get the statement of this session.
Also kill the session with alter system kill session 'sid, serial#';.
3. You should take a look at the event column, which is the key column for tuning. Here is a brief description of the common event:
1) the identification of these two parameters of buffer busy waits,free buffer waits is a question of whether dbwr is sufficient, which is closely related to IO. When the entry of free buffer wait in v$session_wait is very small or not, it means that the dbwr process of the system is absolutely adequate and does not need to be adjusted. If there are many entries in free buffer wait, the system must feel very slow. This means that dbwr is no longer enough, and the wio generated by it has become a bottleneck in database performance. The solution is as follows:
Add the write process and adjust the db_block_lru_latches parameter:
Example: modify or add the following two parameters
Db_writer_processes=4
Db_block_lru_latches=8
Turn on asynchronous IO. IBM is much simpler, while hp is a bit more troublesome. You can contact Hp engineers.
2) db file sequential read refers to sequential reading, that is, full table scan, which should be reduced as much as possible. The solution is to use index and sql tuning, while increasing the parameter db_file_multiblock_read_count.
3) the db file scattered read parameter refers to reading through the index, and the performance can also be improved by adding the parameter db_file_multiblock_read_count.
4) latch free is related to suppository and needs special regulation.
5) other parameters can be noted without special attention.
At this point, the study on "how to solve the problem of database slowing down" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
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.