Hi every one.
I am an administrator on a University moodle, i use PHP with NGINX and mysql.
My problem is that in just one course it gives gateway timeout when you try to save changes of the calcification
I active the slow query log for the mysql.
my.cnf:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
prompt = \u@\h [\d]>\_
no_auto_rehash
[mysqld]
sql-mode = ""
user = mysql
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
bind-address = 0.0.0.0
basedir = /usr/local
datadir = /var/db/mysql
tmpdir = /var/db/mysql_tmpdir
slave-load-tmpdir = /var/db/mysql_tmpdir
secure-file-priv = /var/db/mysql_secure
log-bin = mysql-bin
log-output = TABLE
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay-log-recovery = 1
slow-query-log = 1
server-id = 1
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
expire_logs_days = 30
default_password_lifetime = 0
enforce-gtid-consistency = 1
gtid-mode = ON
safe-user-create = 1
lower_case_table_names = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options = BACKUP,FORCE
open_files_limit = 32768
table_open_cache = 16384
table_definition_cache = 8192
net_retry_count = 16384
key_buffer_size = 256M
max_allowed_packet = 64M
query_cache_type = 0
query_cache_size = 0
long_query_time = 0.5
key_buffer_size = 2G
max_allowed_packet = 100M
max-connect-errors = 1000000
skip-name-resolve
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
innodb_buffer_pool_size = 21G # Buffer de lectura de la innoDB
innodb_buffer_pool_instances = 16
innodb_thread_concurrency = 0
innodb-flush-method = O_DIRECT # Si bien no tiene raid por hardware con bateria tiene zfs siempre coinsistente
innodb-log-files-in-group = 2
innodb-log-file-size = 4G #Tamano de log para hacer el rollback en caso de error, mejora escrituras
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb_fast_shutdown = 0
innodb_doublewrite = 0
performance_schema = 0
tmp-table-size = 32M
max-heap-table-size = 32M
table-definition-cache = 1024
table-open-cache = 4096
thread_stack = 192K
thread_cache_size = 50
open-files-limit = 65535
myisam-recover-options = BACKUP
max_connections = 15000
query_cache_limit = 1M
query_cache_size = 256M
query-cache-type = 1
log_error = /var/log/mysql-error.log
log_error_verbosity = 3
log_output = FILE
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
expire_logs_days = 10
max_binlog_size = 100M
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_log_buffer_size = 16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_autoinc_lock_mode = 2
skip-symbolic-links
[mysqldump]
max_allowed_packet = 256M
quote_names
quick
And the slow query log, just a piece of it:
# Time: 2016-12-12T14:49:14.412849Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184120
# Query_time: 1.854011 Lock_time: 0.000348 Rows_sent: 138 Rows_examined: 1235031
SET timestamp=1481554154;
SELECT gi.id, COUNT(DISTINCT u.id) AS count
FROM mdl_grade_items gi
CROSS JOIN mdl_user u
JOIN (SELECT DISTINCT eu8_u.id
FROM mdl_user eu8_u
JOIN mdl_user_enrolments eu8_ue ON eu8_ue.userid = eu8_u.id
JOIN mdl_enrol eu8_e ON (eu8_e.id = eu8_ue.enrolid AND eu8_e.courseid = '74')
WHERE eu8_u.deleted = 0 AND eu8_u.id <> '1' AND eu8_ue.status = '0' AND eu8_e.status = '0' AND eu8_ue.timestart < 1481553600 AND (eu8_ue.timeend = 0 OR eu8_ue.timeend > 1481553600)) je
ON je.id = u.id
JOIN mdl_role_assignments ra
ON ra.userid = u.id
LEFT OUTER JOIN mdl_grade_grades g
ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
WHERE gi.courseid = '74'
AND ra.roleid = '5'
AND ra.contextid IN ('2707','148','66','27','21','1')
AND u.deleted = 0
AND g.id IS NULL
GROUP BY gi.id;
# Time: 2016-12-12T14:50:20.751032Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184291
# Query_time: 0.888975 Lock_time: 0.000209 Rows_sent: 12972 Rows_examined: 272022
SET timestamp=1481554220;
SELECT g.*
FROM mdl_grade_items gi,
mdl_grade_grades g
WHERE g.itemid = gi.id AND gi.courseid = '74' AND g.userid IN ('3704','7250','1767','10211','10212','5622','12334','9484','3622','10215','9485','10216','5842','2738','10217','5369','4422','1467','10222','6423','10223','8571','2204','5787','1063','5704','10228','4946','9216','9489','9490','1867','10230','2214','4176','6978','1360','9493','4999','5146','14015','1731','10235','10237','3193','9497','2401','6989','5467','9499','14016','14017','1148','10243','5948','12345','3070','12731','4577','3090','2938','9500','9502','3234','4302','5798','2823','10251','9503','10253','4991','10254','6101','4907','10256','10257','10258','9506','9507','10261','4971','4548','10263','2824','9508','10264','14020','14021','14022','3541','3020','5524','2306','2335','2922','9512','700','12351','3137','10276');
# Time: 2016-12-12T14:50:22.837488Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184291
# Query_time: 1.049179 Lock_time: 0.000318 Rows_sent: 114 Rows_examined: 487106
SET timestamp=1481554222;
SELECT g.itemid, SUM(g.finalgrade) AS sum
FROM mdl_grade_items gi
JOIN mdl_grade_grades g ON g.itemid = gi.id
JOIN mdl_user u ON u.id = g.userid
JOIN (SELECT DISTINCT eu8_u.id
FROM mdl_user eu8_u
JOIN mdl_user_enrolments eu8_ue ON eu8_ue.userid = eu8_u.id
JOIN mdl_enrol eu8_e ON (eu8_e.id = eu8_ue.enrolid AND eu8_e.courseid = '74')
WHERE eu8_u.deleted = 0 AND eu8_u.id <> '1' AND eu8_ue.status = '0' AND eu8_e.status = '0' AND eu8_ue.timestart < 1481553700 AND (eu8_ue.timeend = 0 OR eu8_ue.timeend > 1481553700)) je ON je.id = u.id
JOIN (
SELECT DISTINCT ra.userid
FROM mdl_role_assignments ra
WHERE ra.roleid = '5'
AND ra.contextid IN ('2707','148','66','27','21','1')
) rainner ON rainner.userid = u.id
WHERE gi.courseid = '74'
AND u.deleted = 0
AND g.finalgrade IS NOT NULL
GROUP BY g.itemid;
# Time: 2016-12-12T14:50:24.688878Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184291
# Query_time: 1.850747 Lock_time: 0.000352 Rows_sent: 138 Rows_examined: 1235031
SET timestamp=1481554224;
SELECT gi.id, COUNT(DISTINCT u.id) AS count
FROM mdl_grade_items gi
CROSS JOIN mdl_user u
JOIN (SELECT DISTINCT eu8_u.id
FROM mdl_user eu8_u
JOIN mdl_user_enrolments eu8_ue ON eu8_ue.userid = eu8_u.id
JOIN mdl_enrol eu8_e ON (eu8_e.id = eu8_ue.enrolid AND eu8_e.courseid = '74')
WHERE eu8_u.deleted = 0 AND eu8_u.id <> '1' AND eu8_ue.status = '0' AND eu8_e.status = '0' AND eu8_ue.timestart < 1481553700 AND (eu8_ue.timeend = 0 OR eu8_ue.timeend > 1481553700)) je
ON je.id = u.id
JOIN mdl_role_assignments ra
ON ra.userid = u.id
LEFT OUTER JOIN mdl_grade_grades g
ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
WHERE gi.courseid = '74'
AND ra.roleid = '5'
AND ra.contextid IN ('2707','148','66','27','21','1')
AND u.deleted = 0
AND g.id IS NULL
GROUP BY gi.id;
# Time: 2016-12-12T14:50:24.790449Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184296
# Query_time: 4.639898 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1481554224;
SELECT GET_LOCK('moodle2-mdl_-session-4150485', 120);
# Time: 2016-12-12T14:51:02.699499Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184363
# Query_time: 1.052570 Lock_time: 0.000375 Rows_sent: 114 Rows_examined: 487106
SET timestamp=1481554262;
SELECT g.itemid, SUM(g.finalgrade) AS sum
FROM mdl_grade_items gi
JOIN mdl_grade_grades g ON g.itemid = gi.id
JOIN mdl_user u ON u.id = g.userid
JOIN (SELECT DISTINCT eu8_u.id
FROM mdl_user eu8_u
JOIN mdl_user_enrolments eu8_ue ON eu8_ue.userid = eu8_u.id
JOIN mdl_enrol eu8_e ON (eu8_e.id = eu8_ue.enrolid AND eu8_e.courseid = '74')
WHERE eu8_u.deleted = 0 AND eu8_u.id <> '1' AND eu8_ue.status = '0' AND eu8_e.status = '0' AND eu8_ue.timestart < 1481553800 AND (eu8_ue.timeend = 0 OR eu8_ue.timeend > 1481553800)) je ON je.id = u.id
JOIN (
SELECT DISTINCT ra.userid
FROM mdl_role_assignments ra
WHERE ra.roleid = '5'
AND ra.contextid IN ('2707','148','66','27','21','1')
) rainner ON rainner.userid = u.id
WHERE gi.courseid = '74'
AND u.deleted = 0
AND g.finalgrade IS NOT NULL
GROUP BY g.itemid;
# Time: 2016-12-12T14:51:04.551553Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184363
# Query_time: 1.851554 Lock_time: 0.000354 Rows_sent: 138 Rows_examined: 1235031
SET timestamp=1481554264;
SELECT gi.id, COUNT(DISTINCT u.id) AS count
FROM mdl_grade_items gi
CROSS JOIN mdl_user u
JOIN (SELECT DISTINCT eu8_u.id
FROM mdl_user eu8_u
JOIN mdl_user_enrolments eu8_ue ON eu8_ue.userid = eu8_u.id
JOIN mdl_enrol eu8_e ON (eu8_e.id = eu8_ue.enrolid AND eu8_e.courseid = '74')
WHERE eu8_u.deleted = 0 AND eu8_u.id <> '1' AND eu8_ue.status = '0' AND eu8_e.status = '0' AND eu8_ue.timestart < 1481553800 AND (eu8_ue.timeend = 0 OR eu8_ue.timeend > 1481553800)) je
ON je.id = u.id
JOIN mdl_role_assignments ra
ON ra.userid = u.id
LEFT OUTER JOIN mdl_grade_grades g
ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
WHERE gi.courseid = '74'
AND ra.roleid = '5'
AND ra.contextid IN ('2707','148','66','27','21','1')
AND u.deleted = 0
AND g.id IS NULL
GROUP BY gi.id;
# Time: 2016-12-12T14:51:24.634185Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184400
# Query_time: 1.051550 Lock_time: 0.000375 Rows_sent: 114 Rows_examined: 487106
SET timestamp=1481554284;
SELECT g.itemid, SUM(g.finalgrade) AS sum
FROM mdl_grade_items gi
JOIN mdl_grade_grades g ON g.itemid = gi.id
JOIN mdl_user u ON u.id = g.userid
JOIN (SELECT DISTINCT eu8_u.id
FROM mdl_user eu8_u
JOIN mdl_user_enrolments eu8_ue ON eu8_ue.userid = eu8_u.id
JOIN mdl_enrol eu8_e ON (eu8_e.id = eu8_ue.enrolid AND eu8_e.courseid = '74')
WHERE eu8_u.deleted = 0 AND eu8_u.id <> '1' AND eu8_ue.status = '0' AND eu8_e.status = '0' AND eu8_ue.timestart < 1481553800 AND (eu8_ue.timeend = 0 OR eu8_ue.timeend > 1481553800)) je ON je.id = u.id
JOIN (
SELECT DISTINCT ra.userid
FROM mdl_role_assignments ra
WHERE ra.roleid = '5'
AND ra.contextid IN ('2707','148','66','27','21','1')
) rainner ON rainner.userid = u.id
WHERE gi.courseid = '74'
AND u.deleted = 0
AND g.finalgrade IS NOT NULL
GROUP BY g.itemid;
# Time: 2016-12-12T14:51:26.491022Z
# User@Host: lev2[lev2] @ [192.168.3.107] Id: 184400
# Query_time: 1.856295 Lock_time: 0.000350 Rows_sent: 138 Rows_examined: 1235031
SET timestamp=1481554286;
SELECT gi.id, COUNT(DISTINCT u.id) AS count
FROM mdl_grade_items gi
CROSS JOIN mdl_user u
JOIN (SELECT DISTINCT eu8_u.id
FROM mdl_user eu8_u
JOIN mdl_user_enrolments eu8_ue ON eu8_ue.userid = eu8_u.id
JOIN mdl_enrol eu8_e ON (eu8_e.id = eu8_ue.enrolid AND eu8_e.courseid = '74')
WHERE eu8_u.deleted = 0 AND eu8_u.id <> '1' AND eu8_ue.status = '0' AND eu8_e.status = '0' AND eu8_ue.timestart < 1481553800 AND (eu8_ue.timeend = 0 OR eu8_ue.timeend > 1481553800)) je
ON je.id = u.id
JOIN mdl_role_assignments ra
ON ra.userid = u.id
LEFT OUTER JOIN mdl_grade_grades g
ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
WHERE gi.courseid = '74'
AND ra.roleid = '5'
AND ra.contextid IN ('2707','148','66','27','21','1')
AND u.deleted = 0
AND g.id IS NULL
GROUP BY gi.id;