Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Graph display is very slow and uses a lot of memory #1887

Open
makinog3 opened this issue May 2, 2024 · 18 comments
Open

Graph display is very slow and uses a lot of memory #1887

makinog3 opened this issue May 2, 2024 · 18 comments
Labels
fix merged A PR has been created and merged to help or address this issue

Comments

@makinog3
Copy link
Contributor

makinog3 commented May 2, 2024 •
edited
Loading

When there are many transfer histories, the graph display on the upload screen is very slow and uses a lot of memory on the MySQL database.
In our site's case, it is normally over 20 seconds.

Unfortunately, our site went down because it ran out of memory when multiple users displayed the upload screen simultaneously.
So, we don't show the graph now.

  • Number of rows in Transfers table;
    about 40,000

  • Number of rows in AuditLogs table;
    about 800,000

  • Number of rows in StatLogs table;
    13,843,255

  • SQL

SELECT days.date, speed.speed, speed.enspeed FROM (SELECT (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY date
FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) d,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m
WHERE (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY <= (select date(now()))
ORDER BY a + b) as days LEFT  JOIN (SELECT DATE(created) as date,
AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":false%' THEN size/time_taken ELSE null END) as speed,
AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":true%' THEN size/time_taken ELSE null END) as enspeed,
AVG(case WHEN additional_attributes LIKE '%\"encryption\":false%' THEN id ELSE null END) as count,
AVG(case WHEN additional_attributes LIKE '%\"encryption\":true%' THEN id ELSE null END) as encount
from StatLogs WHERE event='file_uploaded' AND created>NOW() - INTERVAL '31' DAY AND size > 1073741824
GROUP BY Date) as speed on days.date=speed.date  ORDER BY days.date;
@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

Could you please post the output of an EXPLAIN on the query. I may be able to improve things without it but knowing what the database is thinking might also help to reason about an index which might help.

@makinog3
Copy link
Contributor Author

makinog3 commented May 3, 2024

Thank you for your response.
We use MariaDB 5.5

MariaDB [filesender]> EXPLAIN SELECT days.date, speed.speed, speed.enspeed FROM (SELECT (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY date         FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4                      UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) d,              (SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m         WHERE (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY <= (select date(now()))         ORDER BY a + b) as days LEFT  JOIN (SELECT DATE(created) as date,    AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":false%' THEN size/time_taken ELSE null END) as speed,    AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":true%' THEN size/time_taken ELSE null END) as enspeed,    AVG(case WHEN additional_attributes LIKE '%\"encryption\":false%' THEN id ELSE null END) as count,    AVG(case WHEN additional_attributes LIKE '%\"encryption\":true%' THEN id ELSE null END) as encount        from StatLogs       WHERE event='file_uploaded'             AND created>NOW() - INTERVAL '31' DAY             AND size > 1073741824       GROUP BY Date) as speed on days.date=speed.date  ORDER BY days.date;
+------+--------------+--------------------------------+------+------------------------------------+-------------------+---------+-------+---------+---------------------------------------------------------------------+
| id   | select_type  | table                          | type | possible_keys                      | key               | key_len | ref   | rows    | Extra                                                               |
+------+--------------+--------------------------------+------+------------------------------------+-------------------+---------+-------+---------+---------------------------------------------------------------------+
|    1 | PRIMARY      | <derived4>                     | ALL  | NULL                               | NULL              | NULL    | NULL  |       2 | Using temporary; Using filesort                                     |
|    1 | PRIMARY      | <derived14>                    | ALL  | NULL                               | NULL              | NULL    | NULL  |       2 | Using where; Using join buffer (flat, BNL join)                     |
|    1 | PRIMARY      | <derived21>                    | ref  | key0                               | key0              | 4       | func  |      10 | Using where                                                         |
|   21 | DERIVED      | StatLogs                       | ref  | StatLogs_created,StatLogs_event_tt | StatLogs_event_tt | 130     | const | 1899450 | Using index condition; Using where; Using temporary; Using filesort |
|   14 | DERIVED      | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   15 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   16 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   17 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   18 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
| NULL | UNION RESULT | <union14,15,16,17,18>          | ALL  | NULL                               | NULL              | NULL    | NULL  |    NULL |                                                                     |
|    4 | DERIVED      | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|    5 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|    6 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|    7 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|    8 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|    9 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   10 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   11 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   12 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
|   13 | UNION        | NULL                           | NULL | NULL                               | NULL              | NULL    | NULL  |    NULL | No tables used                                                      |
| NULL | UNION RESULT | <union4,5,6,7,8,9,10,11,12,13> | ALL  | NULL                               | NULL              | NULL    | NULL  |    NULL |                                                                     |
+------+--------------+--------------------------------+------+------------------------------------+-------------------+---------+-------+---------+---------------------------------------------------------------------+
21 rows in set, 3 warnings (0.03 sec)

@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

This is also targeting the statlogs table. How many tuples do you have there...

select count(*) from StatLogs;

@makinog3
Copy link
Contributor Author

makinog3 commented May 3, 2024 

MariaDB [filesender]> select count(*) from StatLogs;
+----------+
| count(*) |
+----------+
| 13843255 |
+----------+
1 row in set (7.55 sec)

@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

@madsi1m I am wondering if you also have a StatLogs with roughly this many tuples?

@madsi1m
Copy link
Contributor

madsi1m commented May 3, 2024

@monkeyiq

MariaDB [filesender]> EXPLAIN SELECT days.date, speed.speed, speed.enspeed FROM (SELECT (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY date         FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4                      UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) d,              (SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m         WHERE (SELECT Date(NOW() - INTERVAL '30' DAY)) + INTERVAL a+b DAY <= (select date(now()))         ORDER BY a + b) as days LEFT  JOIN (SELECT DATE(created) as date,    AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":false%' THEN size/time_taken ELSE null END) as speed,    AVG(case WHEN time_taken > 0 AND additional_attributes LIKE '%\"encryption\":true%' THEN size/time_taken ELSE null END) as enspeed,    AVG(case WHEN additional_attributes LIKE '%\"encryption\":false%' THEN
id ELSE null END) as count,    AVG(case WHEN additional_attributes LIKE '%\"encryption\":true%' THEN id ELSE null END) as encount        from StatLogs       WHERE event='file_uploaded'             AND created>NOW() - INTERVAL '31' DAY             AND size > 1073741824       GROUP BY Date) as speed on days.date=speed.date  ORDER BY days.date;
+------+--------------+--------------------------------+-------+------------------------------------+------------------+---------+------+--------+---------------------------------------------------------------------+
| id   | select_type  | table                          | type  | possible_keys                      | key              | key_len | ref  | rows   | Extra                                                               |
+------+--------------+--------------------------------+-------+------------------------------------+------------------+---------+------+--------+---------------------------------------------------------------------+
|    1 | PRIMARY      | <derived4>                     | ALL   | NULL                               | NULL             | NULL    | NULL | 2      | Using temporary; Using filesort                                     |
|    1 | PRIMARY      | <derived14>                    | ALL   | NULL                               | NULL             | NULL    | NULL | 2      | Using where; Using join buffer (flat, BNL join)                     |
|    1 | PRIMARY      | <derived21>                    | ref   | key0                               | key0             | 4       | func | 10     | Using where                                                         |
|   21 | DERIVED      | StatLogs                       | range | StatLogs_created,StatLogs_event_tt | StatLogs_created | 5       | NULL | 733048 | Using index condition; Using where; Using temporary; Using filesort |
|   14 | DERIVED      | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   15 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   16 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   17 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   18 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
| NULL | UNION RESULT | <union14,15,16,17,18>          | ALL   | NULL                               | NULL             | NULL    | NULL | NULL   |                                                                     |
|    4 | DERIVED      | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|    5 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|    6 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|    7 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|    8 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|    9 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   10 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   11 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   12 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
|   13 | UNION        | NULL                           | NULL  | NULL                               | NULL             | NULL    | NULL | NULL   | No tables used                                                      |
| NULL | UNION RESULT | <union4,5,6,7,8,9,10,11,12,13> | ALL   | NULL                               | NULL             | NULL    | NULL | NULL   |                                                                     |
+------+--------------+--------------------------------+-------+------------------------------------+------------------+---------+------+--------+---------------------------------------------------------------------+
21 rows in set, 3 warnings (0.001 sec)

MariaDB [filesender]> select count(*) from StatLogs;
+----------+
| count(*) |
+----------+
| 21303210 |
+----------+
1 row in set (6.288 sec)

@madsi1m
Copy link
Contributor

madsi1m commented May 3, 2024 •
edited
Loading

EDIT: re-reading above looks like the upload screen

Which graph are we talking about here?
The one in the upload page (loads fine for us)
Or the ones in the admin stats page (slow to load for us)

@madsi1m
Copy link
Contributor

madsi1m commented May 3, 2024

We also set this in config.php

$config['upload_graph_bulk_min_file_size_to_consider'] = 1024*1024*1024;

@makinog3
Copy link
Contributor Author

makinog3 commented May 3, 2024

We also set this in config.php
$config['upload_graph_bulk_min_file_size_to_consider'] = 102410241024;

We don't set it, but it's a default value, isn't it?
https://github.com/filesender/filesender/blob/development/docs/v2.0/admin/configuration/index.md#upload_graph_bulk_min_file_size_to_consider

@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

yes, it is the default

filesender/includes/ConfigDefaults.php

Line 213 in bae2e54

'upload_graph_bulk_min_file_size_to_consider' => 1024*1024*1024,

@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

The thing that is jumping out at me in the original explain is the id=21 row is using the StatLogs_event_tt index with a possible 2 million tuples. Though @madsi1m is showing 0.7 million in a similar explain row.

The below queries will give an idea of what the real stats are for the event type, recent events, recent-events of the right type, and the whole query. I imagine that there are a lot of recent events and so the 31 days is not selective enough for that to be the preferred index condition. I have left the results out of my data below as it is only a one man development machine so the counts that I have are not interesting.

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded';

MariaDB [filesender]> select count(*) from StatLogs where created>NOW() - INTERVAL '31' DAY ;

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded' and created>NOW() - INTERVAL '31' DAY ;

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded' and created>NOW() - INTERVAL '31' DAY and size > 1073741824;

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded' and size > 1073741824;

Given that for me there is a reasonable difference between just the event filter and the event filter AND the size filter (the first and last count in the above queries) maybe the below index will also help? If it is useful then I can add it and we can drop the tmp1 index in favour of a more permanent one.

create index StateLogs_tmp1 on StatLogs(event,size);
analyze table StatLogs;

@madsi1m
Copy link
Contributor

madsi1m commented May 3, 2024

above (without adding/changing db)

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded';
+----------+
| count(*) |
+----------+
|  2695630 |
+----------+
1 row in set (1.238 sec)

MariaDB [filesender]> select count(*) from StatLogs where created>NOW() - INTERVAL '31' DAY ;
+----------+
| count(*) |
+----------+
|   346204 |
+----------+
1 row in set (0.044 sec)

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded' and created>NOW() - INTERVAL '31' DAY ;
+----------+
| count(*) |
+----------+
|    63495 |
+----------+
1 row in set (0.615 sec)

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded' and created>NOW() - INTERVAL '31' DAY and size > 1073741824;
+----------+
| count(*) |
+----------+
|     3700 |
+----------+
1 row in set (0.375 sec)

MariaDB [filesender]> select count(*) from StatLogs where event='file_uploaded' and size > 1073741824;
+----------+
| count(*) |
+----------+
|   127581 |
+----------+
1 row in set (14.956 sec)

@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

Another reasonable candidate which I can get my local db to accept and use is the following. If we can go from around a million tuples from the index to in the thousands we are off to a good start.

create index StatLogs_tmp4 on StatLogs(event,created,size);
analyze table StatLogs;

@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

You may also have to drop the tmp1 index to force mysql to consider only the new tmp4 one.

@monkeyiq
Copy link
Contributor

monkeyiq commented May 3, 2024

From a college... "It may be an idea to just have table with summary info in it that's generated each day with these calculated values, wouldn't take much space and wouldn't take long to make, so then these queries would be instant"

@madsi1m
Copy link
Contributor

madsi1m commented May 3, 2024

our users like to see there high speed link bump the graph, as our cron is every 15min maybe have cron generate the uploadGraph.php data?

Or implement a cache for uploadGraph.php's data?

I've done such things in other projects using redis but you can use whatever really. Basically in uploadGraph.php do something like:
if (now>TTL) or (force update from cron) {
generate the data
} else {
load data from cache
}

@madsi1m
Copy link
Contributor

madsi1m commented May 3, 2024

can probably also make a config value for the TTL

@madsi1m
Copy link
Contributor

madsi1m commented May 3, 2024

uploadGraph.php looks like something i wrote.....

@monkeyiq monkeyiq mentioned this issue May 5, 2024
Merged
@monkeyiq monkeyiq added the fix merged A PR has been created and merged to help or address this issue label May 5, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
fix merged A PR has been created and merged to help or address this issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@monkeyiq @makinog3 @madsi1m