I am currently using apache 2.2, postgresql 8.3.3, and PHP 5.3.
The symptoms are as described in the heading, but the following actions are listed:
·Pg_dump has been issued to back up the database in order to make new changes to the system.
·There was no problem at first, but after about 15 minutes, it was confirmed that the system was not connected.
·Ctrl+C interrupted pg_dump.
·As I checked with the top command, load average was about 50, cpu consumption was about 70 and swap space was used a lot.
·When I checked with the ps command, there was a large amount of select process.
·After a while, the server stopped accepting responses.
·Rebooted, but a new select process occurred and immediately the server became inoperable.
·After restarting again and stopping postgresql, it returned to an operational state.
·After that, when I rebooted again, the system was running without any problems.
The above happened, but it was not reproduced in the subsequent investigation and the cause has not been determined.
At this time, the system was up and running, and I think there were some users.
Also, when I checked the postgresql log, I found that there were several large data updates and selections before and after that (large data per case, 50,000 characters).
By the way, pg_dump runs every day at a time when there are few users, and there are no errors.
The environment is as follows:
Apache 2.2
Postgresql 8.3.3 (database size is tens of G when dumped)
PHP 5.3
16GB of memory
Can someone help me?
Thank you for your cooperation.
Generally speaking, if you dump the DB in operation, it will be like that.
First, what the DBMS does when the dump runs is to stop updating to the db body to ensure the integrity of the stored data.
"However, since I am in the middle of dump, I have no choice but to answer the query, so I will write down the updated data one after another in the file ""I plan to update the main body later."""
As for what happens when SELECT arrives in this state, we have to return the data that should have been updated, so we will search for the area where we plan to update the main body in addition to the main body.However, this area is not indexed, so you have to load everything and it takes time to search.
Based on this logic, I guess the SELECT has been stuck and the load has increased to the point where it cannot be operated.However, I think there's still something to be overlooked as this won't happen if I rewrite 50 kbyte a few times.
If you look at the lock mode, you may get a good idea.
pg_dump gets a table-level lock in ACCESS SHARE mode.
ACCESS SHARE mode conflicts when:
ALTER TABLEDROP TABLEVACUUMExplicitly Obtained Lock in ACCESS EXCLUSIVE ModeACCESS EXCLUSIVE mode)By the way
The pd_dump documentation is not good (I think it is), but pg_dump does not block other users from accessing (reading and writing) the database, so it looks like it can be retrieved without any impact even during operation.
© 2024 OneMinuteCode. All rights reserved.