--- Log opened miΓ© jun 22 00:00:11 2022 05:41 < fikabot_> πŸ’¬ ah, ok… 05:42 < fikabot_> πŸ’¬ fun fact: the mastodon script works - also with β€žis not 2β€œ πŸ€·β€β™‚ 05:51 < fikabot_> πŸ’¬ It should not, unless it's something which works with python2 05:53 < fikabot_> πŸ’¬ and looks like whatever mastodon api was calling to verify credentials AND to check if user exists works differently than the "verify_credentials" does in friendica. 05:53 < fikabot_> πŸ’¬ on friendica returns 200 if login is correct and 401 if not, either user exists or not. 05:54 < fikabot_> πŸ’¬ (that's why my version uses webfinger to check if user exists) 05:56 < fikabot_> πŸ’¬ and I think there is something also in the "ejabber_in()" function, where from how was coded it was expecting the sys.stdin.buffer.read(2) to be blocking, while in my tests it wasn't so I adapted for it. 06:06 < fikabot_> πŸ’¬ (btw, the "is not 2" is a warning, code works anyway, at least on cpython. The warning is raised by the bytecode complier from python3.8 : see https://bugs.python.org/issue34850 ) 14:27 < fikabot_> πŸ’¬ My instance is getting slammed by the query for calculating the unread post counts across groups 14:27 < fikabot_> πŸ’¬ I'm thinking of trying to do an implementation does in-memory caching of the result with a TTL of 60 seconds or something like that 14:28 < fikabot_> πŸ’¬ If I were to implement something like that would that be something that the team would find interesting to include? 14:28 < fikabot_> πŸ’¬ Starting as a KISS MVP thing so no Redis, persistence to an ETL table or whatever, 16:08 < fikabot_> πŸ’¬ A query like that would get it from the query cache I assume. Unless you get a lot of new messages it should not be a problem imho 16:09 < fikabot_> πŸ’¬ it's thrashing the DB on my machine 16:09 < fikabot_> πŸ’¬ is a query cache setup in the database layer automatically? 16:09 < fikabot_> πŸ’¬ Nopes 16:09 < fikabot_> πŸ’¬ Not in mysql, dunno about mariadb 16:10 < fikabot_> πŸ’¬ I assume the same 16:10 < fikabot_> πŸ’¬ https://mariadb.com/kb/en/query-cache/ 16:10 < fikabot_> πŸ’¬ So you're saying that one should have a query cache in place to fix the problem and there is a standard way of doing that with Friendica but it's not configured by default 16:11 < fikabot_> πŸ’¬ The query cache is something an admin would normally do, not a user 16:11 < fikabot_> πŸ’¬ since it is global for all databases afaik 16:11 < fikabot_> πŸ’¬ yes of course 16:12 < fikabot_> πŸ’¬ I've seen machine's that go bananas on the load and once it was enabled it had a 16:12 < fikabot_> πŸ’¬ normal load 16:14 < fikabot_> πŸ’¬ Yeah I'm going to configure it now to see if that helps 16:14 < fikabot_> πŸ’¬ That one query is like 98% of my DB workload 16:14 < fikabot_> πŸ’¬ Do not set it very high the first time. If queries are invalidated you can always tune it more 16:14 < fikabot_> πŸ’¬ and pegs the machine 16:15 < fikabot_> πŸ’¬ Hmm, did you run explain on the query too? Friendica has limited indexes (good) but it might need one 16:16 < fikabot_> πŸ’¬ nevermind MySQL 8.0 doesn't have the feature anymore 16:16 < fikabot_> πŸ’¬ Ah 16:17 < fikabot_> πŸ’¬ IIRC it is properly indexed the problem is they are very complicated nested queries 16:17 < fikabot_> πŸ’¬ Switch to mariadb :-) 16:17 < fikabot_> πŸ’¬ and they generate requests for every tab that is loaded every time the page refreshes 16:19 < fikabot_> πŸ’¬ For example my current process list (which has essentially been status quo since yesterday evening minus a brief reprieve after I upped it to two CPUs) 16:20 < fikabot_> πŸ’¬ Except the times were drastically longer once it really gets backed up 16:20 < fikabot_> πŸ’¬ ``` 16:20 < fikabot_> πŸ’¬ +-------+-----------------+-----------+-------------+---------+-------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 16:20 < fikabot_> πŸ’¬ | 5 | event_scheduler | localhost | NULL | Daemon | 29293 | Waiting on empty queue | NULL | 16:20 < fikabot_> πŸ’¬ | 10939 | friendica | localhost | friendicadb | Sleep | 1 | | NULL | 16:21 < fikabot_> πŸ’¬ | 10999 | friendica | localhost | friendicadb | Query | 85 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11006 | friendica | localhost | friendicadb | Query | 75 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11009 | friendica | localhost | friendicadb | Query | 68 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11010 | friendica | localhost | friendicadb | Query | 68 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11011 | friendica | localhost | friendicadb | Query | 62 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11020 | friendica | localhost | friendicadb | Query | 24 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11022 | friendica | localhost | friendicadb | Query | 17 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11026 | friendica | localhost | friendicadb | Query | 1 | executing | SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 5 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 5 | 16:21 < fikabot_> πŸ’¬ | 11027 | friendica | localhost | friendicadb | Sleep | 0 | | NULL | 16:21 < fikabot_> πŸ’¬ | 11028 | friendica | localhost | friendicadb | Query | 0 | statistics | SELECT * FROM `apcontact` WHERE (`addr` = 'https://blabber.lu-rp.net/users/negocio_898') LIMIT 1 | 16:22 < fikabot_> πŸ’¬ | 11029 | root | localhost | NULL | Query | 0 | init | SHOW FULL PROCESSLIST | 16:22 < fikabot_> πŸ’¬ +-------+-----------------+-----------+-------------+---------+-------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 16:22 < fikabot_> πŸ’¬ 16:22 < fikabot_> πŸ’¬ ``` 16:22 < fikabot_> πŸ’¬ well opening a tab with ajax enabled is something to think about 16:22 < fikabot_> πŸ’¬ huh? 16:22 < fikabot_> πŸ’¬ In the browser 16:22 < fikabot_> πŸ’¬ ajax is making those calls 16:22 < fikabot_> πŸ’¬ I assume 16:22 < fikabot_> πŸ’¬ oup`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 16:23 < fikabot_> πŸ’¬ Ouch, count is an sql keyword 16:23 < fikabot_> πŸ’¬ Yes they are calls to build the flags for the Groups box: 16:23 < fikabot_> πŸ’¬ https://matrix.org/_matrix/media/v1/download/myportal.social/BFIyJSlOSCffYQERiMVzggfF 16:24 < fikabot_> πŸ’¬ Hmmm 16:24 < fikabot_> πŸ’¬ When you have a lot of groups and lots of users in the groups it gets very expensive 16:25 < fikabot_> πŸ’¬ which is why caching it in the app was my first idea 16:25 < fikabot_> πŸ’¬ unless there was a caching layer already in place that allows for that 16:26 < fikabot_> πŸ’¬ I'm messing around with docker compose a lot more for other things and hvae thought about redoing my entire setup around docker compose which would also be a chance to swap out DBs, add redis, etc. 16:26 < fikabot_> πŸ’¬ I never used docker 16:28 < fikabot_> πŸ’¬ I think the default is it redoes the query every 10 seconds but I have it set to 20 seconds on my user (it is user configurable) 16:28 < fikabot_> πŸ’¬ that is per browser tab too 16:30 < fikabot_> πŸ’¬ That does sound like an Ajax (asynchroon) process. Are those running if you close the tabs? 16:30 < fikabot_> πŸ’¬ not that I'm aware of 16:31 < fikabot_> πŸ’¬ they are totally client side initiated to refresh the stats 16:31 < fikabot_> πŸ’¬ Jups, ajax 16:31 < fikabot_> πŸ’¬ I don't think that was in question 16:32 < fikabot_> πŸ’¬ Well, I was slightly confused due to those groups 16:35 < fikabot_> πŸ’¬ Yeah terminology can get a bit wonky 16:36 < fikabot_> πŸ’¬ The only thing I could then think of is seeing if any indexes are used in the queries 16:36 < fikabot_> πŸ’¬ But that would only partly solve it 16:36 < fikabot_> πŸ’¬ Yeah since it is convoluted 16:36 < fikabot_> πŸ’¬ hence why I wanted to cache the result 16:37 < fikabot_> πŸ’¬ by user 16:37 < fikabot_> πŸ’¬ Especially with concurrent tabs up there is no reason why it needs to get recomputed on every call 16:37 < fikabot_> πŸ’¬ And since placing an index might slow down the process if a lot of writes are on those tables 16:37 < fikabot_> πŸ’¬ they are busy tables too 16:38 < fikabot_> πŸ’¬ it's essentially the /ping command endpoint 16:40 < fikabot_> πŸ’¬ I do wonder if MariaDB query caching makes it not show up for others though... 16:41 < fikabot_> πŸ’¬ It was, if I remember correctly, part of Innodb, a different engine 16:41 < fikabot_> πŸ’¬ Yep it was 16:41 < fikabot_> πŸ’¬ depracated in 5.7 and removed in 8.0 16:41 < fikabot_> πŸ’¬ MySQL 16:42 < fikabot_> πŸ’¬ https://dev.mysql.com/doc/refman/5.7/en/query-cache.html 16:43 < fikabot_> πŸ’¬ Weird, no mention of any alternative. 16:44 < fikabot_> πŸ’¬ Nope they explain why 16:44 < fikabot_> πŸ’¬ somewhere else let me see if I can find it 16:44 < fikabot_> πŸ’¬ https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/ 16:47 < fikabot_> πŸ’¬ Yeah, well, I stll like the querycache. Moving it closer to the client is simply outsourcing it :-) 16:52 < fikabot_> πŸ’¬ I suppose the first thing I should do is determine what these configurations do (I'm using the defaults): 16:52 < fikabot_> πŸ’¬ ``` 16:52 < fikabot_> πŸ’¬ // cache_driver (database|memcache|memcached|redis|apcu) 16:52 < fikabot_> πŸ’¬ // Whether to use Memcache, Memcached, Redis or APCu to store temporary cache. 16:53 < fikabot_> πŸ’¬ 'cache_driver' => 'database', 16:53 < fikabot_> πŸ’¬ 16:53 < fikabot_> πŸ’¬ // distributed_cache_driver (database|memcache|memcached|redis) 16:53 < fikabot_> πŸ’¬ // Whether to use database, Memcache, Memcached or Redis as a distributed cache. 16:53 < fikabot_> πŸ’¬ 'distributed_cache_driver' => 'database', 16:53 < fikabot_> πŸ’¬ 16:53 < fikabot_> πŸ’¬ ``` 16:54 < fikabot_> πŸ’¬ I assume the second one might be for a master / slave setup of the database 16:57 < fikabot_> πŸ’¬ Maybe 17:39 < fikabot_> πŸ’¬ FYI this is the compose script I was thinking of trying: https://github.com/friendica/docker/blob/stable/.examples/docker-compose/with-traefik-proxy/mariadb-cron-smtp/apache/docker-compose.yml --- Log closed jue jun 23 00:00:12 2022