job manager takes a long time to load

[Alex-Jordan]

On my production server (now on develop with a few additional uncommitted changes) when I am in a course and try to go to the Job Manager, it takes a very long time to load. On the order of one minute. Eventually it loads, and for one course, it only had a few things to show.

On my regular development server on develop, it does not take long at all to load this page.

Is anyone else using a production server where the Job Manager is available? I can't see why my local uncommitted changes would affect this, so I am naturally wondering if there is something about the scale of everything in production could explain the slowdown.

[drgrice1]

You could try installing sqlite (or really sqlite3), then execute sqlite3 /opt/webwork/webwork2/DATA/webwork2_job_queue.db, and then run select COUNT(*) from minion_jobs. How many jobs does is show?

[drgrice1]

By the way type .exit to exit sqlite3.

[drgrice1]

It seems on Oracle Linux there is a package for sqlite 3. It is called sqlite, and the executable is also sqlite. Ubuntu has sqlite and sqlite3 packages, and the first has the executable sqlite and the second has sqlite3.

[Alex-Jordan]

OK, the package was sqlite but the executable was sqlite3. I got 10768 jobs.

[drgrice1]

Yeah, that seems to be a lot. It may be that you will need to use one of the other database backends for Minion. I suspect that sqlite is not good enough to handle that kind of scale. There are mysql and postgres backends. Most likely you will want mysql since you are already using that for webwork2. For that install Minion::Backend::mysql from cpan. As to the rest of the setup, I will need to figure that out again. It has been a while. I know you have to create a database for Minion to use. You can just use the usual webworkWrite user (or whatever you are already using for webwork2). You have to set the permissions for that user for the database. But I don't remember the rest.

[drgrice1]

So here are more detailed steps on setting up the webwork job queue to use the mysql backend.

• Install Minion::Backend::mysql from cpan (as I already mentioned).
• Enter mysql as root with sudo mysql -u root (this may be different for you on Oracle, but I assume you know how to do this).
• Create the database in mysql with create database webwork2_job_queue (you can use a different database name if you like).
• Grant the needed privileges to your database user with GRANT ALL PRIVILEGES ON webwork2_job_queue.* TO 'webworkWrite'@'localhost'. Not all privileges are needed, but I am not sure which ones. I know all of the privileges we use for the usual webwork2 database are needed as well as the TRIGGER privilege, but there is something else too. At one point I had it narrowed down, but apparently I didn't document it anywhere.
• In site.conf set $job_queue{backend} = 'mysql' and $job_queue{database_dsn} = 'mysql://webworkWrite:yourdbpasswd@localhost/webwork2_job_queue'.

At this point I recommend that you test the webwork2 job queue from the command line (not the systemctl service). For this run sudo -u www-data bin/webwork2 minion worker from the /opt/webwork/webwork2 directory. If all goes well the job queue will start and all tables in the database will be created. You can check this with show tables in the database, and you should see several minion_... tables.

There is an issue though. The Minion::Backend::mysql package uses an experimental feature of the Mojo::mysql package. Whenever the job queue starts or anytime a job is enqueued you will see the warning "Use of Mojo::mysql::PubSub is highly EXPERIMENTAL and should be considered an experiment at /usr/local/share/perl/5.34.0/Mojo/mysql.pm line 39". So you will need to add Environment="MOJO_PUBSUB_EXPERIMENTAL=1" to the conf/webwork2.service file to disable that warning. That will disable the warning when a job is enqueued by the webwork2 app at least. That is the bad one because it shows up in the UI. If you want to disable the warning for the minion worker also, then add that to the conf/webwork2-job-queue.service file. Although that warning is only in the console, and you probably won't even see it when run via the systemd service.

[Alex-Jordan]

So, I'm not able to get Minion::Backend::mysql because it depends on DBD::mysql, which I can't get. (I have DBD::MariaDB.) If I try to install DBD::mysql, it's like:

alex.jordan@vmwebworkprodw02:/opt/webwork/webwork2 $ sudo cpanm DBD::mysql
--> Working on DBD::mysql
Fetching http://www.cpan.org/authors/id/D/DV/DVEEDEN/DBD-mysql-5.004.tar.gz ... OK
Configuring DBD-mysql-5.004 ... N/A
! Configure failed for DBD-mysql-5.004. See /root/.cpanm/work/1711493774.325911/build.log for details.

In that file that it says to look into:

DBD::mysql requires MySQL 8.x for building. Version reported by mysql_config --version: 10.5.5 at Makefile.PL line 451, <PIPE> line 3.

So I think I'm stuck.

[drgrice1]

Yeah, it does use DBD::mysql and can't be switched to use DBD::mariadb. Although, you should be able to install DBD::mysql. You just need to install the development header files for mysql. Those are probably in a package for Oracle.

[drgrice1]

Alternately, you could use postgres. That is easier to get working, assuming it is not hard to install postgres. I would also have to remember how to do that!

[drgrice1]

It seems that DBD::mysql is available in a package for Oracle. It is perl-DBD-mysql. Would that work?

[Alex-Jordan]

That may have worked. I got to the point of running sudo -u apache bin/webwork2 minion worker but I can't tell if I should ctrl-c now to end it, or if it is doing something I shouldn't interrupt:

alex.jordan@vmwebworkprodw02:/opt/webwork/webwork2 $ sudo -u apache bin/webwork2 minion worker
[2024-03-26 16:37:42.47755] [329310] [info] WeBWorK server is starting
[2024-03-26 16:37:42.47764] [329310] [info] WeBWorK root directory set to /opt/webwork/webwork2
[2024-03-26 16:37:42.47767] [329310] [info] PG root directory set to /opt/webwork/pg
[2024-03-26 16:37:42.47769] [329310] [info] The webwork url on this site is https://webwork.pcc.edu/webwork2
[2024-03-26 16:37:42.47772] [329310] [info] webwork_server_admin_email for reporting bugs has been set to webwork-group@pcc.edu in site.conf
[2024-03-26 16:37:42.53050] [329310] [info] Worker 329310 started
Use of Mojo::mysql::PubSub is highly EXPERIMENTAL and should be considered an experiment at /usr/local/share/perl5/Mojo/mysql.pm line 39.

That's where it is right now.

[drgrice1]

It looks like it worked. You can use ctrl-c to kill it now. If it were running jobs you would see output in the terminal.

Check that the tables were created in the database, but that should be it.

[Alex-Jordan]

OK, and all running now. Of course in this new database there are no jobs, so the page loads very fast :)

[drgrice1]

Yeah, so you could try to dump the sqlite database and import that into the mysql database. To dump the sqlite database run sqlite /opt/webwork/webwork2/DATA/webwork2_job_queue.db. Then in sqlite execute

.output queue.sql
.dump
.exit

You should then see the file queue.sql in the directory you are in. Then run mysql webwork2_job_queue < queue.sql (adding all of the usual parameters you use to access mysql).

Either that or just wait, and let time tell.

[Alex-Jordan]

I dumped it OK, but having trouble importing it. There are grammar/syntax differences between sqlite and mysql.