Hi All, My user got this error msg while attempting theri quiz. Digging info from google seems that nothing can be done on db side to fix this problem as it seems need to fix on application part .. the way query was executed by the system. Im using Postgres 8.3 and Moodle 2.3.1. Anyone got the idea how to overcome this problem ? Thank You So Much
I don't think I have seen that one before. (Even though we use Postgres at the OU.)
The most obvious thing to try is to upgrade from 2.3.1 to 2.3.7. Many bugs have been fixed in the last year.
You could also consider upgrading Postgres. 8.3 is ancient, and there have been some nice performance improvements in Postgres 9.x.
Yeah Mr Hunt .. considering that for long term solution, Thank You. Just looking for temp and fast solution for the issue as the lect will redo the quiz again in 3 days time. Downtime only can be done on weekend.
The student number is just 118 person ( this never happen previously in moodle 1.9 and mysql 5 )
Mr Hunt would it better to upgrade to 2.5 from 2.3.1 ?
tq
Well, upgrading 2.3.1 -> 2.3.x should be a minor change that is safe to do at almost any time, and you should plan to do that regularly, after each minor release, to get the latest security and bug fixes.
Upgrading 2.3.x to 2.4.y or 2.5.y is a bigger change. You need to read the release notes, perhaps give some advanced warning to your staff.
So, I would say, a minor upgrade is something you could do at the weekend. For a major upgrade you might consider planning to do those during the schoold vacation.
Since I don't know why you are getting these deadlocks, I cannot guess if it is likely to happen again when the teacher re-runs the quiz. Did it happen a lot, or was it just one or two students?
Dear Tim,
This happen a lot when the student number around 30 person take the exam in same period of time.
Is there any tuning that i can make ?
tq
Dear Tim,
I've digged into postgres log ... got some clue here
2013-05-27 15:01:01 MYT LOG: incomplete startup packet
2013-05-27 15:03:02 MYT LOG: incomplete startup packet
2013-05-27 15:05:04 MYT LOG: incomplete startup packet
2013-05-27 15:07:07 MYT LOG: incomplete startup packet
2013-05-27 15:09:01 MYT LOG: checkpoints are occurring too frequently (28 seconds apart)
2013-05-27 15:09:01 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2013-05-27 15:09:08 MYT LOG: incomplete startup packet
2013-05-27 15:11:11 MYT LOG: incomplete startup packet
2013-05-27 15:13:13 MYT LOG: incomplete startup packet
2013-05-27 15:14:26 MYT LOG: checkpoints are occurring too frequently (29 seconds apart)
2013-05-27 15:14:26 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2013-05-27 15:14:30 MYT ERROR: deadlock detected
2013-05-27 15:14:30 MYT DETAIL: Process 16560 waits for ShareLock on transaction 74671603; blocked by process 16569.
2013-05-27 15:14:30 MYT STATEMENT: UPDATE mdl_quiz_attempts SET currentpage = $1
2013-05-27 15:14:43 MYT LOG: checkpoints are occurring too frequently (17 seconds apart)
2013-05-27 15:14:43 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2013-05-27 15:15:03 MYT LOG: checkpoints are occurring too frequently (20 seconds apart)
2013-05-27 15:15:03 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2013-05-27 15:15:10 MYT ERROR: deadlock detected
2013-05-27 15:15:10 MYT DETAIL: Process 16797 waits for ShareLock on transaction 74672210; blocked by process 16802.
2013-05-27 15:15:10 MYT STATEMENT: UPDATE mdl_quiz_attempts SET currentpage = $1
2013-05-27 15:15:13 MYT ERROR: deadlock detected
2013-05-27 15:15:13 MYT DETAIL: Process 16804 waits for ShareLock on transaction 74672256; blocked by process 16816.
2013-05-27 15:15:13 MYT STATEMENT: UPDATE mdl_quiz_attempts SET currentpage = $1
2013-05-27 15:15:14 MYT LOG: checkpoints are occurring too frequently (11 seconds apart)
2013-05-27 15:15:14 MYT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2013-05-27 15:15:15 MYT LOG: incomplete startup packet
2013-05-27 15:15:16 MYT ERROR: deadlock detected
2013-05-27 15:15:16 MYT DETAIL: Process 16803 waits for ShareLock on transaction 74672256; blocked by process 16816.
2013-05-27 15:15:16 MYT STATEMENT: UPDATE mdl_quiz_attempts SET currentpage = $1
The "Consider increasing the configuration parameter "checkpoint_segments"." and the "checkpoints are occurring too frequently (11 seconds apart)"
I need to increase the checkpoint segment ???
tq
I've set my checkpoint_segment from default value 3 o 10. Will monitor the problem. thank you
In my case i had to update my postgres checkpoint_segment to 64 to overcome the problem. Increased to 10 and 32 still get the deadlock.
Thanxs
Shin,
Can you tell us where you went to change the number in case we want to do the same?
Dear Doug,
You can edit it in postgresql.conf ( mine located in /usr/share/pgsql/postgresql.conf) file. Please read about it in google coz it will consume your server memory if it is set unwisely.
tq
Hi Good Day,
Dear Moodler,
Looked back to my 1st posting up there, in the screenshoot ( moodle debugger ) the sql which is executed was :
UPDATE mdl_quiz_attempts SET currentpage = $1
the sql was too obvious which there is no WHERE condition ... it will update all currentpage field records in mdl_quiz_atempts.
while the mdl_quiz_attempts is as below :
CREATE TABLE "public"."mdl_quiz_attempts" (
"id" Bigint NOT NULL DEFAULT nextval('mdl_quiz_attempts_id_seq'::regclass) PRIMARY KEY,
"quiz" Bigint NOT NULL DEFAULT 0,
"userid" Bigint NOT NULL DEFAULT 0,
"attempt" Integer NOT NULL DEFAULT 0,
"uniqueid" Bigint NOT NULL DEFAULT 0,
"layout" text NOT NULL,
"currentpage" Bigint NOT NULL DEFAULT 0,
"preview" Smallint NOT NULL DEFAULT 0,
"state" Varchar(16) NOT NULL DEFAULT 'inprogress'::character varying,
"timestart" Bigint NOT NULL DEFAULT 0,
"timefinish" Bigint NOT NULL DEFAULT 0,
"timemodified" Bigint NOT NULL DEFAULT 0,
"sumgrades" Numeric(10, 5),
"needsupgradetonewqe" Smallint NOT NULL DEFAULT 0
)
What do you guys think about this ?
Thank You
This is a known bug, that was fixed ages ago. MDL-34451 - fixed in 2.3.2. You really need to upgrade, to 2.3.10 at least, but note that 2.6 is out.
Thanks Tim for the info, with this i really need to upgrade. This deadlock is haunting me although post postgres tuning.
By the way Tim, would you mind sharing your postgres implementation in OU. I knew that OU have large number of students, are you using single instance postgres or cluster postgres to coupe the moodle request.
Currently our moodle handling 30000 users with 20000 of them is very active. Previously I used mysql but due 'locking' massive problem while inserting log record i decided to move to postgres when migrating from 1.9 to 2.3.1
We manage to eliminate the mysql locking problem till we realize this quiz problem.
Thanks in advance.
We have a single Postgres server, with master/slave replication to give us a hot backup in case of problems on the master server.
The server has lots of memory, so that most of the data being used at any time is cached in memory.
Sorry, I am being a bit vague on the details here. It is of the order of 96GB RAM, I think. I think the disc storage is on our SAN. I guess if the need ever arose, we could consider storing the DB on SSDs.
We have 671,000 rows in the mdl_user table, and on a given day typically about 50,000 different users will log in at some time. That load is spread reasonably uniformly between about 8:00am and midnight. (with a bit of a ramp up and ramp down at either and, and mild peaks and lunchtime and early evening.)
Thats clue was really helpful Tim, thanks a lots. If your postgres can handle 50000 active users with replication I think i dont need to do postgres clustering.
Thanx again.
