Leginon

How many sessions and images/movies have you accumulated so far ?

You should also check how much memory and number of query that the database server is allowed to use. The limit might be so low that it is not using the resource efficiently. I will ask people with more database experience to help you with this.

I agree with Anchi that this is most likely database server issue. Assuming that there is no hardware issue, you can use phpMyAdmin advisor or some other tool listed at https://www.askapache.com/mysql/mysql-performance-tuning/ for MySQL performance tuning.

This is a very back of the envelope calculation (if there is a simple deterministic way of figuring this out; say using the database, I'm all ears), but

We have about 39T of data, at this point. Assuming an average image size of 55M, that would be ... about 700,000 images? Mysql should be able to handle a million records pretty easily, depending on the query/table structure, but maybe this is as simple as allocating more RAM to mysql. One additional quick question: are the myamiweb tables InnoDB, or something else?

MyISAM

Thank you. Let me know if you get any hints from a DB guru. Meanwhile I'll post an update if I find a solution.

Sargis is our database guru.

Patrick, please provide the answer on the number of sessions.

If that number is large. In our case 100,00, the query performed at the loading of the viewer page does slow down. See Issue #6231 and Issue #5810.

Beyond that, do try some of the tuning Sargis linked you to.

Hi Anchi/Sargis -

Do you have a convenient SQL statement I can use to count the number of sessions, or do I need to fire up mysql and scan through the table schemas?

Also, I can't find Issue #5810 in the Issues list. Goes from #5809 to #5814.

Sorry, #5810 is a private issue for our IT.

Here is the quote from relavent part of it from Sargis

So I focused on seeding up queries instead. I noticed that getSessions is called three times when opening Image Viewer. First two times to get getLastSessionId. Each time getSessions is called, it needs to sort AcquisitionImageData which now contains 5968731 rows. I've changed leginon.inc and project.inc in http://emg.nysbc.org/projects/appion/repository/revisions/678cfff9dfd83239d4c5fb1e591d684d61c81f2c so that it will reuse sessions and won't sort AcquisitionImageData when possible.

Regarding counting Session. It is the number of rows in leginondb.SessionData

Hi Patrick,

Issue #5810 is private; only our group members can see it. I recommend installing https://www.phpmyadmin.net. This will give you a nice UI to navigate the database as well as optimize it. To count the number of sessions, you can use phpmyadmin and select leginon database. There you'll find SessionData table and phpmyadmin displays how many rows this table has. That is the number of sessions. In our case, we have around 8k sessions.

Here's the answer to the number of sessions question; appears to be not that many:

mysql> select count(*) from leginondb.SessionData;
+----------+
| count(*) |
+----------+
|      409 |
+----------+
1 row in set (0.00 sec)

I've been doing some reading and experimenting with performance tuning and trying to identify long running queries. If you execute the following SQL commands:

mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET long_query_time = 15;

mysql will log any query taking longer than 15 seconds to /var/log/mysql/localhost-slow.log. I did this and couldn't find any queries which weren't nearly instantaneous (i.e. less than a second) when run from the mysql> command line. I increased key_buffer_size and query_cache_size, but this didn't help myamiweb performance (literally 6 minute delay from when some sessions are clicked and the image displays).

So, I looked into the differences between MyISAM (which I believe is deprecated) and InnoDB. From a performance/utility perspective, MyISAM seems to be a better choice for this application, but I did find this:

When a query runs against a MyISAM table, the entire table in which it is querying will be locked. This means subsequent queries will only be executed after the current one is finished. If you are reading a large table, and/or there are frequent read and write operations, this can mean a huge backlog of queries.

When a query runs against an InnoDB table, only the row(s) which are involved are locked, the rest of the table remains available for CRUD operations. This means queries can run simultaneously on the same table, provided they do not use the same row.

I'm starting to suspect that image collection on the microscope is preventing sessions from loading via myamiweb upstairs because rawtransfer is periodically locking this table with just the right frequency to prevent the table from being accessed for the session load? This might be an argument for switching the table structure to InnoDB (or something which supports row level locking). Understood that the SQL would need to be modified to prevent performance degradation due to built in transaction processing.

Any thoughts?

Patrick,

I do not know more about the tuning than you do. But since you mentioned rawtransfer.py, I want to know if you experience delay in rawtransfer.py as well ? A normal system should leave about two files in the frames directory of K2/K3 PC on average and does not accumulate during data collection.

For a related question, how fast is your data collection ? You can check the time between two image-shifted Exposure image/movie acquisition. You will not have any query during that time from data collection.

Locking table is unlikely your problem since the query is short as you found out. We are using MyISAM and that has not been an issue. You should probably check how many connections you allow and have on the database server.

Hi Patrick,

Try this if you would like to find out more about locks.

https://serverfault.com/questions/36260/how-can-i-show-mysql-locks

Dan Carley's answer seems to give a good explanation about investigating MYSQL locking.

Hi Anchi -

We typically do have some accumulation of images in the transfer folder during image collection. I'm only called in when there's an issue (the 7-zip thing I reported on another ticket, which turns out to be associated with Digital Micrograph), and then there are often several hundred images queued up in the transfer folder.

Hi Shaker -

I monitored the database during one of the slow session load events and ran

mysql> show processlist\G

as recommended in the serverfault link you posted. This showed that exactly one query was running at that time. I copy & pasted that exact query and re-ran it by hand from the mysql> command prompt and the response was instantaneous. This kind of seems to me that the system is stalling because it's waiting for a table lock to be removed when run from the web interface, but I haven't yet tried running show full processlist, so can try that as well.

Is there any chance PHP is causing these delays?

Hi,

If you only see one query and there is still a slowdown then at that point locking is ruled out. If locking had been occurring then "The affected queries will have a status of Locked and the offending query will be sitting out by itself..." as written in the serverfault page.

I would reiterate what Anchi previously wrote "You should probably check how many connections you allow and have on the database server."

Take a look at you MySQL settings. I believe we have the same PHP code and we are not currently experiencing long wait times when using our image viewer.

Hmm -

The default number of max connections is 100 -- there aren't even half that many people in the lab, and as I've mentioned, there are really only a couple of performance-related configuration parameters for MyISAM tables that I've been able to find. I've already increased all those to no avail. I'll keep fiddling with it until I can figure out what is going on, and will report back when I do.

Patrick,

Number of queries is not the same as the number of people using it. All of our database connections are not persistent. It finishes its job and disconnect right away so others can get on.

OK, I've tracked down the source of the slow myamiweb performance, and it doesn't appear to have anything to do with available connections, query caching, or anything like this. In fact, it's a single SQL query:

mysql> use ap7;
Database changed
mysql> select label, count(DEF_id) as `number particle` from ApParticleData where `REF|ApSelectionRunData|selectionrun`=1 and label is not NULL group by `label`;

+--------+-----------------+
| label  | number particle |
+--------+-----------------+
| templ1 |          730731 |
+--------+-----------------+
1 row in set (20 min 47.57 sec)

The issue appears to be the number of particles. Here is the same query run against a different ap database:

mysql> use ap2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select label, count(DEF_id) as `number particle` from ApParticleData where `REF|ApSelectionRunData|selectionrun`=1 and label is not NULL group by `label`;
+--------+-----------------+
| label  | number particle |
+--------+-----------------+
| templ1 |           19811 |
+--------+-----------------+
1 row in set (1.40 sec)

1.4 seconds vs. over 20 minutes to run this one SQL query. Either we need to restrict the number of particles found in any ap database, or maybe this query needs to be optimized? Looking for input from someone more familiar with the DB schemas. Note that this query is run when you click on "Image Viewer" and select a Project from the drop down menu at the top.

You can comment out line 7 in myamiweb/imageviewer.php so that it won't load particles when it opens the viewer as shown in commit:d79d715f.

We had to put it back following #6111

Commenting out $ptcl definitely resolved this issue. Here's a repeat of the query that took 20:47 before:

mysql> use ap7;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select label, count(DEF_id) as `number particle` from ApParticleData where `REF|ApSelectionRunData|selectionrun`=1 and label is not NULL group by `label`;
+--------+-----------------+
| label  | number particle |
+--------+-----------------+
| templ1 |          730731 |
+--------+-----------------+
1 row in set (0.00 sec)

I'm not sure however if the users are going to want to use CTFFind from the web interface. Is there no way to opimize this query, say by adding and index of some kind?

Our appion tables have indexes. For instance:

mysql> SHOW INDEX FROM `ApParticleData`;
+----------------+------------+--------------------------------------------+--------------+--------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name                                   | Seq_in_index | Column_name                                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+--------------------------------------------+--------------+--------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+
| ApParticleData |          0 | PRIMARY                                    |            1 | DEF_id                                     | A         |      493582 |     NULL | NULL   |      | BTREE      |         |
| ApParticleData |          1 | DEF_timestamp                              |            1 | DEF_timestamp                              | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| ApParticleData |          1 | REF|ApSelectionRunData|selectionrun        |            1 | REF|ApSelectionRunData|selectionrun        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| ApParticleData |          1 | REF|leginondata|AcquisitionImageData|image |            1 | REF|leginondata|AcquisitionImageData|image | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| ApParticleData |          1 | REF|ApTemplateImageData|template           |            1 | REF|ApTemplateImageData|template           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+----------------+------------+--------------------------------------------+--------------+--------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Do you have similar indexes in your tables?

Patrick, why would the speed of your direct sql query in mysql commandline interface be affected by your change in the webviewer ? It does not make sense unless it is waiting for a query initiated there to finish before it performs its task. Is that the case ? Can you tell ?

@Sargis: yes:

mysql> use ap7;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW INDEX FROM `ApParticleData`;
+----------------+------------+--------------------------------------------+--------------+--------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name                                   | Seq_in_index | Column_name                                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+--------------------------------------------+--------------+--------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ApParticleData |          0 | PRIMARY                                    |            1 | DEF_id                                     | A         |      785527 |     NULL | NULL   |      | BTREE      |         |               |
| ApParticleData |          1 | DEF_timestamp                              |            1 | DEF_timestamp                              | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| ApParticleData |          1 | REF|ApSelectionRunData|selectionrun        |            1 | REF|ApSelectionRunData|selectionrun        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| ApParticleData |          1 | REF|leginondata|AcquisitionImageData|image |            1 | REF|leginondata|AcquisitionImageData|image | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| ApParticleData |          1 | REF|ApTemplateImageData|template           |            1 | REF|ApTemplateImageData|template           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+----------------+------------+--------------------------------------------+--------------+--------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)