О завершении поддержки предыдущей версии протокола Zot
Владельцам хабов, которые до сих пор не обновились до последней актуальной версии настоятельно рекомендуется это сделать ввиду потенциальной потери коннективности со всей сетью Zot.#russian #lang_ru #hubmin


¿doppelt?







Mario Vavti
in reply to Hubzilla Support Forum • • •Mario Vavti
in reply to Hubzilla Support Forum • • •Max Kostikov
in reply to Mario Vavti • • •Mario Vavti
in reply to Hubzilla Support Forum • • •Mark Nowiasz
in reply to Mario Vavti • • •Mario Vavti
in reply to Mark Nowiasz • • •Mark Nowiasz
in reply to Mario Vavti • • •Max Kostikov
in reply to Mark Nowiasz • • •@Mark Nowiasz
About the same (60Gb x 3 = 180Gb).
Mario Vavti
in reply to Hubzilla Support Forum • • •update workerq set workerq_reservationid = null where workerq_reservationid is not null and workerq_processtimeout < UTC_TIMESTAMP()Is this a long running query?
Mark Nowiasz
in reply to Mario Vavti • • •@Mario Vavti Currently no, but when the connection table was full there were lots of very long running queries of this type - upt to 4000 seconds. I think it is/was because of the sheer amount of parallel updates which do basically the same and block each other, so none of these queries ever finished.
I've incresed now the "Assume workers dead after" value to 1200, I was thinking that maybe these huge amounts of updates are the results of updates not being finished, but the worker was being killed... just a theory
Mario Vavti
in reply to Mark Nowiasz • • •@Mark Nowiasz
Did this improve the situation?
Mark Nowiasz
in reply to Mario Vavti • • •Max Kostikov likes this.
Max Kostikov
in reply to Mark Nowiasz • • •@Mark Nowiasz
I think you have a known problem with the scalability of the mySQL database, when its size exceeds the capacity of RAM, as a result, the performance of the disk subsystem becomes a bottleneck.
Mark Nowiasz
in reply to Max Kostikov • • •Max Kostikov likes this.
Max Kostikov
in reply to Mark Nowiasz • • •@Zotum runs on the latest mySQL 8.0 and PHP 8.2 under FreeBSD 13.1.
Mark Nowiasz
in reply to Max Kostikov • • •Mario Vavti
in reply to Hubzilla Support Forum • • •Could be the two SQL backends handle things differently or different configuration...
@Mark Nowiasz i suspect the update query you posted is waiting for locks to free up. I can imagine this could be a problem. At least that would explain why they are piling up...
I will send you a patch where the locked rows will be skipped or at leas the update query will not wait for the locks and abort instead.
Would be great if you could test it...
Max Kostikov likes this.
Mark Nowiasz
in reply to Mario Vavti • • •Mario Vavti
in reply to Hubzilla Support Forum • • •Let's see...
Mark Nowiasz
in reply to Mario Vavti • • •like this
Mario Vavti and Max Kostikov like this.
Mario Vavti
in reply to Hubzilla Support Forum • • •Mark Nowiasz
in reply to Mario Vavti • • •Mario Vavti
in reply to Hubzilla Support Forum • • •Mario Vavti
in reply to Mario Vavti • • •Are there any long running queries?
Mark Nowiasz
in reply to Mario Vavti • • •@Mario Vavti No, they are just sleeping and - if I remember correctly - there were no long running queries at the time, I've simply restarted MySQL to get rid of the idle connections. I might adjust the timeout so MySQL will kill those idle connections earlier.
I was just assuming that these used to be queueworkers, like in the past. I'll take a look the next time something similar happens.
Hans
in reply to Hubzilla Support Forum • • •Mario Vavti
in reply to Hubzilla Support Forum • • •Mark Nowiasz
in reply to Hubzilla Support Forum • • •@Mario Vavti The patch doesn't really work, instead of [code}update[/code] these queries were piling up the connection table:
| 173307 | hubzilla_netzgemeinde | localhost | hub_netzgemeinde_eu | Query | 447 | Creating sort index | SELECT workerq_id, workerq_cmd FROM workerq WHERE workerq_reservationid IS NULL ORDER BY workerq_priority DESC, workerq_id ASC LIMIT 1 FOR UPDATE447 Seconds and counting - it was only one of hundreds queries.
I did reset queueworker's values to 1200/1000000 which improved the situation. I also set
max_statement_time(MariaDB, similar tomax_execution_timeon MySQL) to 600 which also improved the situation a little bit. Nevertheless I'm encountering Zabbix warnings about Disk I/O overloads,Number of internal temporary tables created per second is high (over 30 for 5m)and occasionally warnings aboutMySQL: Server has slow queries (over 3 for 5m).I think Scott is right - 8.0 is overloading the database (at least MariaDB) where 7.x did work without any problems. I'm not sure i'ts the queueworkers to blame. As soon as I recovered more from the cataract operation I'd gladly be willing to help pinpoint the problem, for example by enabling slow query log and so on.
Mario Vavti
in reply to Mark Nowiasz • • •Mark Nowiasz
in reply to Mario Vavti • • •Mario Vavti
in reply to Hubzilla Support Forum • • •I can not send you a patch right now... You can try to change
$work = dbq("SELECT workerq_id, workerq_cmd FROM workerq WHERE workerq_reservationid IS NULL ORDER BY workerq_priority DESC, workerq_id ASC LIMIT 1 FOR UPDATE");to
$work = dbq("SELECT workerq_id, workerq_cmd FROM workerq WHERE workerq_reservationid IS NULL ORDER BY workerq_priority DESC, workerq_id ASC LIMIT 1 FOR UPDATE SKIP LOCKED");hereGitLab: https://framagit.org/hubzilla/core/-/blob/dev/Zotlabs/Lib/QueueWorker.php#L181
build community websites that can interact with one another
to possibly fix this query too.
Zotlabs/Lib/QueueWorker.php · dev · hubzilla / core · GitLab
GitLabMario Vavti
in reply to Hubzilla Support Forum • • •Zotlabs/Lib/QueueWorker.php · master · hubzilla / core · GitLab
GitLabMark Nowiasz
in reply to Mario Vavti • • •Mario Vavti likes this.
Mario Vavti
in reply to Hubzilla Support Forum • • •Mark Nowiasz
in reply to Mario Vavti • • •Number of internal temporary tables created per second is high (over 30 for 5m, but far less than before. I'm not sure if it's the patch or settingmax_statement_timeto 600, as soon as I've got some time for debugging I'm getting into the slow queries. For now the site is much more stable than before.Mario Vavti likes this.
Mark Nowiasz
in reply to Hubzilla Support Forum • • •@Mario Vavti I had a look and unfortunately the problem remains - although not as strong as before, howerver I did had to restart the MySQL three times in the last two weeks because the queueworker ate up all connections,;
(Just two of the >200 statements piling up).
Mark Nowiasz
in reply to Hubzilla Support Forum • • •@Mario Vavti I had a look and unfortunately the problem remains - although not as strong as before, howerver I did had to restart the MySQL three times in the last two weeks because the queueworker ate up all connections,;
(Just two of the >200 statements piling up).
Mario Vavti
in reply to Hubzilla Support Forum • • •Mark Nowiasz
in reply to Mario Vavti • • •