Running time ./OPL-update takes a very long time:
/opt/webwork/webwork2/bin# time ./OPL-update
Library version is 2.5; using OPLtables!
Mysql database reinitialized.
Reading in textbook data from...
... ... ...
Done.
real 113m46.464s
user 1m46.972s
sys 0m21.008s
Almost 2 hours! This is on 24-core 2.8GHz Opteron, 64GiB of RAM and SSD RAID-10.
Is this something that other Webwork admins are seeing? Or something terribly wrong with our installation.
My guess is that you are using InnoDB not MyISAM. See http://webwork.maa.org/wiki/Installation_Manual_for_2.12_on_Ubuntu_16.04#MySQL_Security_and_Performance_Issues
Many WeBWorK tables are automatically created (if you use defaults) using MyISAM but I'm not sure about library tables.
Arnie
The only table that uses InnoDB is OPL_global_statistics. Can that affect OPL_update? (It is also the only table using UTF8 collation.)
OPL_update does recreate OPL_global_statistics, but it does so after running all of its other tasks. It should be easy to tell if this is the problem based on where things slow down.
The first thing OPL_update does is grab the metadata from the problems. It shows its progress for every 100 problems it indexes, so the first part of the process counts up to something like 30000. How long does this part of the script take? On my server, where the database is on a remote machine and the OPL is on an nfs share (so probably on the slower side wrt disk access), this takes 5-10min. If this part goes quickly, but then it slows down later, then it may be an InnoDB issue. If the count to 30000 is taking a really long time, then the problem is likely somewhere else.
Is everything local to your machine? i.e. is the database housed on localhost, and the OPL files directly on disk?
One way to test your disk access speeds is to click on the "OPL Directory" button in the Library Browser (if you have it enabled). Because our OPL files are on an nfs share, this takes several seconds to load on my server. On a machine with local storage, this typically loads in less than a second.
Also, how is the WeBWorK performance in general on your server? OPL_update doesn't run that much differently than other WeBWorK code, so if OPL_update is really slow, I would expect similarly poor performance in other areas of WeBWorK.
It's the progress to 30000, that takes all the time. Database is on a local disk.
"OPL Directory" in Library browser loads in a second.
WeBWorK in general seems to respond the same, though this is the first term after upgrading OS from 14.04 to 16.04, which includes MySQL upgrade too.
"OPL Directory" in Library browser loads in a second.
WeBWorK in general seems to respond the same, though this is the first term after upgrading OS from 14.04 to 16.04, which includes MySQL upgrade too.
It definitely shouldn't take that long. Even on a VM with significantly less resources it only takes a minute or two.
It appears that other people have been using Ubuntu 16.04 without this issue, so I would guess that the choice of OS is not the problem.
Was this an in-place upgrade of the OS, or was it a fresh install?
An in-place upgrade and the culprit is MySQL binary log (we use it for replication).
Starting with MySQL 5.7 (Ubuntu 16.04) the default is sync_binlog=1, which syncs to disk with each write:
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog
I switched to sync_binlog=0 temporarily, and OPL-update completed in 13 min. compared to 118 min. with sync_binlog=1.
hdparm reports 518.23 MB/sec.
Starting with MySQL 5.7 (Ubuntu 16.04) the default is sync_binlog=1, which syncs to disk with each write:
http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog
I switched to sync_binlog=0 temporarily, and OPL-update completed in 13 min. compared to 118 min. with sync_binlog=1.
hdparm reports 518.23 MB/sec.