The problem of slow login in Oracle is solved

Time:2021-5-31

Today, I received a feedback from my colleagues that the page access of the project is slow, so we need to check the reason.

The front-end development colleagues looked it up and said that the page response time was relatively long due to the slow database operation.

I log in to the database server and check it with vmstat. I find that the idle of the system is very high, which indicates that the server has no pressure.

I asked the front desk to send me the SQL used by the front desk. After executing it, I found that the query time was very short.

I felt that it should not be a database problem, so I asked the front desk colleagues to add more logs to continue to locate and see if there were other problems.

The front-end colleagues were busy for a while, and finally found the real reason: each connection request was relatively slow in the first operation of the database.

After checking the source code, it is found that every requester needs to connect to the database the first time it operates the database,And our database login usually takes a few seconds

Sometimes a page needs to send several requests. If it is processed sequentially, it will take more than ten seconds to return( Because it is a test environment, the number of concurrent settings is not high, only 4)

I asked my front-end colleagues not to release the database connection, but reuse it in all requests. But the front-end colleagues said that it uses the traditional PHP deployment method. Each request needs to be processed by a new fork process. After the request is processed, all resources will be released. There is no way to do so.

There’s no choice but to go back to the database and seeIs there any way to optimize the speed of database login.

I log in to the database host and execute the following command to check the database connection:

while [ 1 ]; do tnsping orcl; sleep 1; done

It is found that even on the database host, the execution time of tnsping is relatively long. I checked it on the Internet and said it might be caused by too large monitoring log.

Although I can’t figure out why the size of the log will affect the performance of the program, I still tried: I found the listening file of the database and found that it has more than 2G. So the monitoring stopped, and then delete the log file to restart.

At the same time, I also checked the table space of the database and found that the system space was almost full. So I stopped byCleaned up the space

In the process of cleaning up, it is found that the audit result table occupies the largest space. Considering that our database is used in the development environment, there is no need to open an audit, which wastes space and performance, so it is convenient to use itDatabase audit is off

After the above operation, we found that the database connection is much faster, most of the time tnsping is within 1 second.

But it’s strange that once in a while (the probability of occurrence is about 1 / 20) tnsping takes a few seconds to return.

I checked it on the Internet to see if anyone came across itHow fast and how slow is Oracle database loginI didn’t find any related articles. But I found that one person mentioned that the DNS server ID configured by their database server was wrong, which led to the problem of slow database connectionWhen Oracle logs in to the client, it will try to use the client’s machine name to search the DNS server for the corresponding IP address. If the DNS server IP configuration is wrong, it will lead to a long login time.

So I opened the machine’s configuration file / etc / resolv. Conf to see if we also had a wrong configuration.

However, we found that the DNS server configured inside is 114.114.114.114, which is a public DNS provided by Telecom (similar to 8.8.8.8 provided by Google), and can be connected normally on our machine.

DNS server configuration is not wrong, but I think, our machines are intranet, using public DNS LAN machine name is certainly not found.

According to the previous development experience, it will take much longer to not find the data than to find it (because the local cache cannot be found, so you need to access the remote machine).

If you want to find the IP of Intranet machine name through DNS, you need to build a DNS server inside the LAN, which is too troublesome.

Considering that this database server does not need to access the external network except using Yum installation software, the DNS configuration is blocked.

# Generated by NetworkManager
# nameserver 114.114.114.114

Re execute after modifying the configuration

while [ 1 ]; do tnsping orcl; sleep 1; done

It is found that the returned tnsping data is normal, and there will be no occasional time-consuming exception.

Let the front desk colleagues try the operation of the web page, also feedback now the system reaction speed is very fast, the problem has been solved perfectly.

Postscript: it’s not a good practice to connect to the database once a time. Later, I consulted an experienced colleague, who introduced a database connection pool middleware, saying that it can solve the problem of frequent database connection by PHP

https://blog.csdn.net/21aspnet/article/details/50774684
Installation and use of PHP database connection pool SQL relay

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]