This is a large production system
System accumulates a large number of user processes when problems arise
User requests do not respond in a timely manner, and new processes continue to attempt to establish a connection
The number of connections is quickly exhausted
The following error message is logged, stating that there is a problem with the disk asynchronous IO:
Warning:aiowait timed out 2 times
Tue Aug 26 15:33:32 2003
Warning:aiowait timed out 2 times
Tue Aug 26 15:33:34 2003
Warning:aiowait timed out 2 times
Tue Aug 26 15:33:36 2003
Warning:aiowait timed out 2 times
Tue Aug 26 15:33:38 2003
Warning:aiowait timed out 2 times
Tue Aug 26 15:33:43 2003
Warning:aiowait timed out 1 times
Tue Aug 26 15:33:46 2003
Warning:aiowait timed out 1 times
Tue Aug 26 15:33:49 2003
Warning:aiowait timed out 1 times
Tue Aug 26 15:33:51 2003
Warning:aiowait timed out 1 times
Tue Aug 26 15:33:52 2003
Warning:aiowait timed out 1 times
Tue Aug 26 15:33:53 2003
Warning:aiowait timed out 1 times
.............
We know there are problems with asynchronous IO on some versions of sun
and asynchronous IO is open by default
Sql> Show Parameter Disk_a
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Disk_asynch_io Boolean TRUE
For this issue, we deactivated the asynchronous IO write for the database.
2. Shared memory problem
The following error message is also logged in the alert file:
Tue Aug 26 21:37:40 2003
Warning:einval creating segment of size 0x0000000190400000
Fix SHM parameters In/etc/system or equivalent
This information indicates that the kernel parameter setting is too small or does not match the SGA
We check the system configuration file
$ cat/etc/system
.......................
Set shmsys:shminfo_shmmax=4096000000
Set Shmsys:shminfo_shmmin=1
Set shmsys:shminfo_shmmni=200
Set shmsys:shminfo_shmseg=200
Set semsys:seminfo_semmap=1024
Set semsys:seminfo_semmni=2048
Set semsys:seminfo_semmns=2048
Set semsys:seminfo_semmnu=2048
Set semsys:seminfo_semume=200
Set semsys:seminfo_semmsl=2048
We found that the maximum shared memory setting was only 4G
3. Check the SGA settings
Sql*plus:release 9.2.0.3.0-production on Tuesday August 26 21:46:35 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0-64bit Production
With the partitioning, OLAP and Oracle Data Mining options
Jserver Release 9.2.0.3.0-production
Sql> Show SGA
Total System Global area 6695660272 bytes
Fixed Size 740080 bytes
Variable Size 2399141888 bytes
Database buffers 4294967296 bytes
Redo buffers 811008 bytes
We found that the SGA setting was close to 7G, which is why the error prompt occurred in step 2
4. Exchange Zone Issues
We use top tool to check system health
#/usr/local/bin/top
Last pid:16899; Load averages:0.82, 0.81, 0.83 21:49:05
1230 processes:1228 sleeping, 1 running, 1 on CPU
CPU states:50.1% Idle, 7.4% user, 8.6% kernel, 33.9% iowait, 0.0% swap
memory:8192m Real, 118M free, 12G swap with use, 11G swap free
We found that the system had only 8G RAM and that only 118M of physical memory was available
The swap area now uses 12G.
We have made the following preliminary judgment:
The SGA is set too large (nearly 7G) resulting in a large exchange at runtime
Large swap swap to raise disk problems
This should be the first step we see
Warning:aiowait timed out 1 times
The reason
Massive exchange results in a sharp drop in database performance
In turn, the user requests are not quickly responded to, blocked, accumulated, until the database is out of response
5. The solution
This problem is mainly due to improper setting of SGA, we narrowed the SGA settings immediately:
Sql> Show SGA
Total System Global area 3591870848 bytes
Fixed Size 735616 bytes
Variable Size 1442840576 bytes
Database buffers 2147483648 bytes
Redo buffers 811008 bytes
At this time, the database reduces the exchange, achieves the stable operation, the user request can obtain the quick response.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
