Query optimizations
Hi,
Since I'm running one of the larger nodes (opensocial.at, friendica.me), I'm constantly looking for bottlenecks and improvements.
I log all slow queries for later analysis. Would it be helpful to post it anywhere with some insights of the database (how much data is in it, how many entries post-user or contact do have, ...)
For example this query:
SELECT `circle`.`id`, `circle`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 1137 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` AS `circle_member` WHERE `circle_member`.`gid` = `circle`.`id`) ) AS `count` FROM `group`
AS `circle` WHERE `circle`.`uid` = 1137
takes around 17 seconds and is constantly logged in the slow-query logs.
Shall I post it here, is it even useful? shall I open an issue for such cases? Is it just a problem for the node, so I post it at Friendica Admins for help?
@Michael Vogel what do you think :)
Friendica Developers reshared this.
Slow /photo/preview performance
Hi all,
Because of the ongoing performance issues, I moved my whole Friendica database of opensocial.at and friendica.me to a dedicated root-server just for mariadb with 128GB RAM.
At first, it seemed like it helped and opensocial.at is faster than before (the performance of friendica.me is still a mess, but I will analyze it later), but the /photo/preview route is now going crazy:
It seems like the RAM I/O issue I had (which is now gone) is now replaced by an Network throughput issue - but at least the network I/O seems fine:
Do you have any idea what I can do now?
One thing would be to add a "StorageClass" for the Photo to load them (directly) from a filedirectory/block storage instead of the database.
Friendica Admins reshared this.
/tmp folder? Here on my server it ran full with magick-XXXXX files.
But there are just two zero-byte magick files at
/tmp :)Friendica Admins reshared this.
CI currenrly not usable
Hi, I recently tried to increase the woodpecker / ci performance. Unfortunately it dropped the repositories after migrating from sqlite to MySQL as backend. @Tobias re-added it again (thanks 
) but now some settings are still missing.
So currently, the CI isn't working 
I'll be back home at Sunday evening, but I think I will fix it not before Monday/Tuesday..
So if you need to merge new PRs, keep in mind that the files under files.friendi.ca aren't updating ...
Sorry and I'll give an update when it's working again..
like this
Friendica Developers reshared this.
Geplante Downtime / Server Migration
!Opensocial.at
-- English below
Liebe opensocial.at BenutzerInnen,
Ich starte nun eine Migration von opensocial.at auf eine stรคrkere server-hardware. Dadurch entstehen temporรคre Downtimes in den kommenden Stunden.
Ich mรถchte mich fรผr die Unannehmlichkeiten entschuldigen, aber ich denke, dass dadurch die gesamte Performance von opensocial.at wieder besser werden sollte :).
Lg,
Philipp
----
Dear opensocial.at user,
I'm planning to migrate my server infrastructure onto a more powerful environment.
The goal is to increase the performance, as, I think, you all currently experience a downgrading performance.
I hope it will help :)
It will take some hours, where opensocial.at will be temporary unavailable from time to time.
Regards,
Philipp
Opensocial.at reshared this.
Leider hat die Migration nicht geklappt und ich musste wieder zurรผckrollen.
--> Ich probiere es im August noch einmal
---
Unfortunately, the migration didn't work so I had to rollback.
--> I will retry again in august
Norz Tekh likes this.
Opensocial.at reshared this.
Planned Downtime / Migration
Dear friendica.me user,
I'm planning to migrate my server infrastructure onto a more powerful environment.
The goal is to increase the performance, as, I think, you all currently experience a downgrading performance.
I hope it will help :)
It will take some hours, where friendica.me will be temporary unavailable from time to time.
Regards,
Philipp
friendica.me reshared this.
--> I will retry again in august
friendica.me reshared this.
#FediBlock*.mastotroll.netz.orgโฒ Admin (of friendica.utzer.de) - 2023-05-03 18:16:56 GMT
New troll domain with many subsomains. This is a strongly recommended block for all #Fediverse services for *.mastotroll.netz.org.Please share this post!
Slow photo requests / inbox requests
Hi !Friendica Admins ,
I'm currently tracing down performance issues, I' currently suffering from on my nodes opensocial.at and friendica.me .
It seems like the main performance problems are
- delivering photos
- [code]/inbox/code] requests
You can see some peeks and some long runners here:
The long runners (10sec) are the /inbox requests, the peaks are delivering photos after opening conversations, network pane, ...
Do you have any hints how I could improve these two types of requests?
The database is currently using ~50GB RAM, I set them as high as possible.
Friendica Admins reshared this.
17000 from 59000 finished ;)Friendica Admins reshared this.
avatar/, there are still a lot of long running requests, see the image above. Do you have any idea? The overall CPU/RAM is on a normal state.
Friendica Admins reshared this.
Friendica 2023.04 released
We are very happy to announce the availability of the new stable release of Friendica โGiant Rhubarbโ 2023.04. The highlights of this release are
- The Mastodon compatible API of Friendica was improved,
- Drag and Drop functionality was added to the Frio theme for image attachments, and
- Christian Pรถschl from usd AG reported a XSS fulnurability in a javascript dependency in the gallery of the Frio theme that was fixed.
For details, please the CHANGELOG file in the repository.
What is Friendica
Friendica is a decentralized communications platform, you can use to host your own social media server that integrates with independent social networking platforms (like the Fediverse or Diaspora*) but also some commercial ones like Tumblr.
How to Update
Updating from old Friendica versions
If you are updating from an older version than the 2022.12 release, please first update your Friendica instance to that version as it contained some breaking changes.
Pre-Update Procedures
Ensure that the last backup of your Friendica installation was done recently.
Using Git
Updating from the git repositories should only involve a pull from the Friendica core repository and addons repository, regardless of the branch (stable or develop) you are using. Remember to update the dependencies with composer as well. So, assuming that you are on the stable branch, the commands to update your installation to the 2023.04 release would be
cd friendica
git pull
bin/composer.phar install --no-dev
cd addon
git pull
If you want to use a different branch than the stable one, you need to fetch and checkout the branch before your perform the git pull.
Pulling in the dependencies with composer will show some deprecation warning, we will be working on that in the upcoming release.
Using the Archive Files
If you had downloaded the source files in an archive file (tar.gz) please download the current version of the archive from friendica-full-2023.04.tar.gz (sha256) and friendica-addons 2023.04.tar.gz (sha256)) and unpack it on your local computer.
As many files got deleted or moved around, please upload the unpacked files to a new directory on your server (say friendica_new) and copy over your existing configuration (config/local.config.php and config/addon.config.php) and .htaccess files. Afterwards rename your current Friendica directory (e.g. friendica) to friendica_old and friendica_new to friendica.
The files of the dependencies are included in the archive (make sure you are using the friendica-full-2023.04 archive), so you donโt have to worry about them.
Post Update Tasks
The database update should be applied automatically, but sometimes it gets stuck. If you encounter this, please initiate the DB update manually from the command line by running the script
bin/console dbstructure update
from the base of your Friendica installation. If the output contains any error message, please let us know using the channels mentioned below.
Please note, that some of the changes to the database structure will take some time to be applied, depending on the size of your Friendica database.
Known Issues
During the release candidate phase of this release, we had reports that changes to the Tumblr addon combined with PHP version 7.4 might lead a WSoD situation on some pages and connection problems with Tumblr (see 12979 and 13021). These problems could be solved for this release. The Tumblr addon will receive an overhaul in the upcoming release where these issues will be addressed as well.
How to Contribute
If you want to contribute to the project, you donโt need to have coding experience. There are a number of tasks listed in the issue tracker with the label โJunior Jobsโ we think are good for new contributors. But you are by no means limited to these โ if you find a solution to a problem (even a new one) please make a pull request at github or let us know in the development forum.
Contribution to Friendica is also not limited to coding. Any contribution to the documentation, the translation or advertisement materials is welcome or reporting a problem. You donโt need to deal with Git(Hub) or Transifex if you donโt like to. Just get in touch with us and we will get the materials to the appropriate places.
Thanks everyone who helped making this release possible and have fun!
like this
reshared this
rel="me" attribute and points to your user profile on Friendica. Then add a link to your homepage in your profile. wiki.friendi.ca/docs/verify_hoโฆ
Update JSON LD
@Hypolite Petovan already fixed a current JSON-LD issue with git.friendi.ca/friendica/php-jโฆ
Do we need a 1.1.2 release to upgrade it in the Friendica composer.lock file as well?
The logs of my instances are flooded with this error, that's why I'm asking *g*
Add support for local files for unsecured document loading
- $http_response_header isn't set in the local file read case Address https://github.com/friendica/friendica/issues/12728#issuecomment-1407623586Friendica
Torsten Torsten likes this.
Friendica Developers reshared this.
Hypolite Petovan likes this.
Friendica Developers reshared this.
BaseURL components (hostname, SSL policy, urlpath)
I'm currently reducing the whole BaseUrl.php code massively.
Do we really need the ssl_policy, urlpath and hostname separate from the system.url?
I will use for the BaseUrl.php, based on the system.url a "real" UriInterface as $this->url, so we don't need saving the scheme, urlpath and hostname separately anymore. They are just useful for the install process but must not be changed afterwards.
The only thing, which I'm unsure is the ssl_policy, because if someone changes it afterwards in the admin site, all URL in all contacts and photos will get updated. But the question is => is this even allowed? I think this could brick the access over federation because the base-url of each entry isn't right anymore. And it isn't supported when the policy is changed by console.
So I would drop it as well and merge all config entries into the system.url.
Additionally, I will replace the Exception with a "CRITICAL" log entry to avoid a WSOD.
Friendica Developers reshared this.
Friendica Developers reshared this.
https and there is no check at all in the code that would fail here. It works fine Friendica Developers reshared this.
Friendica.me | Opensocial.at Short Downtime because of DB adjustements
!friendica.me , !Opensocial.at
I do have to restart the Database instances because they are too resource hungry.
There's a short downtime possible.
reshared this
Friendica 2023.01 released
Christian Pรถschl from usd AG has found another XSS vulnerability in Friendica which is close with this hotfix release of Friendica.
In addition some other bugfixes for the distribution of forum postings and improvements to the update process of node information are included in this release.
For details, please the CHANGELOG file in the repository.
What is Friendica
Friendica is a decentralized communications platform, you can use to host your own social media server that integrates with independent social networking platforms (like the Fediverse or Diaspora*) but also some commercial ones like Twitter.
How to Update
Updating from old Friendica versions
If you are updating from an older version than the 2022.06 release, please first update your Friendica instance to that version as it contained some breaking changes.
Pre-Update Procedures
Ensure that the last backup of your Friendica installation was done recently.
Using Git
Updating from the git repositories should only involve a pull from the Friendica core repository and addons repository, regardless of the branch (stable or develop) you are using. Remember to update the dependencies with composer as well. So, assuming that you are on the stable branch, the commands to update your installation to the 2022.12 release would be
cd friendica
git pull
bin/composer.phar install --no-dev
cd addon
git pull
If you want to use a different branch than the stable one, you need to fetch and checkout the branch before your perform the git pull.
Pulling in the dependencies with composer will show some deprecation warning, we will be working on that in the upcoming release.
Using the Archive Files
If you had downloaded the source files in an archive file (tar.gz) please download the current version of the archive from friendica-full-2023.01.tar.gz (sha256) and friendica-addons 2023.01.tar.gz (sha256)) and unpack it on your local computer.
As many files got deleted or moved around, please upload the unpacked files to a new directory on your server (say friendica_new) and copy over your existing configuration (config/local.config.php and config/addon.config.php) and .htaccess files. Afterwards rename your current Friendica directory (e.g. friendica) to friendica_old and friendica_new to friendica.
The files of the dependencies are included in the archive (make sure you are using the friendica-full-2023.01 archive), so you donโt have to worry about them.
Post Update Tasks
The database update should be applied automatically, but sometimes it gets stuck. If you encounter this, please initiate the DB update manually from the command line by running the script
bin/console dbstructure update
from the base of your Friendica installation. If the output contains any error message, please let us know using the channels mentioned below.
Please note, that some of the changes to the database structure will take some time to be applied, depending on the size of your Friendica database.
Known Issues
Regarding the update process none as of writing.
How to Contribute
If you want to contribute to the project, you donโt need to have coding experience. There are a number of tasks listed in the issue tracker with the label โJunior Jobsโ we think are good for new contributors. But you are by no means limited to these โ if you find a solution to a problem (even a new one) please make a pull request at github or let us know in the development forum.
Contribution to Friendica is also not limited to coding. Any contribution to the documentation, the translation or advertisement materials is welcome or reporting a problem. You donโt need to deal with Git(Hub) or Transifex if you donโt like to. Just get in touch with us and we will get the materials to the appropriate places.
Thanks everyone who helped making this release possible and have fun!
like this
reshared this
Friendica 2022.12 released
We are very happy to announce the avail-ability of the new stable version of Friendica. Wrapping up the sprint from the 2022.10 release of Friendica we closed 73 filed issues and had almost 300 pull requests by 19 contributors.
A special thanks goes out to Christian Pรถschl from usd AG and Matthias Moritz who have found a CSRF- and XSS-attack, that is fixed with this release.
In addition to fixing this bug, the highlights of the changes since the 2022.10 release are
- The default theme of Friendica (frio) got many improvements and some old themes got deprecated.
- The calendar saw some improvements and can now be made visible to anonymous visitors.
- The homepage mentioned on the user profile is now automatically verified via the rel-me backlink.
- Images attached to a posting are now shown in a grid at the bottom of the posting.
- A moderation corner was established from the admin panel where a future release will add more moderation tool and bundle them with the current once.
For details, please the CHANGELOG file in the repository.
Since version 2022.06 the lowest required PHP version Friendica needed on the server was raised to PHP 7.3 and PHP 8.0 is supported.
What is Friendica
Friendica is a decentralized communications platform, you can use to host your own social media server that integrates with independent social networking platforms (like the Fediverse or Diaspora*) but also some commercial ones like Twitter.
How to Update
Updating from old Friendica versions
If you are updating from an older version than the 2022.06 release, please first update your Friendica instance to that version as it contained some breaking changes.
Pre-Update Procedures
Ensure that the last backup of your Friendica installation was done recently.
Using Git
Updating from the git repositories should only involve a pull from the Friendica core repository and addons repository, regardless of the branch (stable or develop) you are using. Remember to update the dependencies with composer as well. So, assuming that you are on the stable branch, the commands to update your installation to the 2022.12 release would be
cd friendica
git pull
bin/composer.phar install --no-dev
cd addon
git pull
If you want to use a different branch than the stable one, you need to fetch and checkout the branch before your perform the git pull.
Pulling in the dependencies with composer will show some deprecation warning, we will be working on that in the upcoming release.
Using the Archive Files
If you had downloaded the source files in an archive file (tar.gz) please download the current version of the archive from friendica-full-2022.12.tar.gz (sha256) and friendica-addons 2022.12.tar.gz (sha256)) and unpack it on your local computer.
As many files got deleted or moved around, please upload the unpacked files to a new directory on your server (say friendica_new) and copy over your existing configuration (config/local.config.php and config/addon.config.php) and .htaccess files. Afterwards rename your current Friendica directory (e.g. friendica) to friendica_old and friendica_new to friendica.
The files of the dependencies are included in the archive (make sure you are using the friendica-full-2022.12 archive), so you donโt have to worry about them.
Post Update Tasks
The database update should be applied automatically, but sometimes it gets stuck. If you encounter this, please initiate the DB update manually from the command line by running the script
bin/console dbstructure update
from the base of your Friendica installation. If the output contains any error message, please let us know using the channels mentioned below.
Please note, that some of the changes to the database structure will take some time to be applied, depending on the size of your Friendica database.
Known Issues
Regarding the update process none as of writing.
How to Contribute
If you want to contribute to the project, you donโt need to have coding experience. There are a number of tasks listed in the issue tracker with the label โJunior Jobsโ we think are good for new contributors. But you are by no means limited to these โ if you find a solution to a problem (even a new one) please make a pull request at github or let us know in the development forum.
Contribution to Friendica is also not limited to coding. Any contribution to the documentation, the translation or advertisement materials is welcome or reporting a problem. You donโt need to deal with Git(Hub) or Transifex if you donโt like to. Just get in touch with us and we will get the materials to the appropriate places.
Thanks everyone who helped making this release possible and have fun!
like this
reshared this
.htaccess file, yes.
opensocial.at database data seems corrupt
It seems like I do have a problem with my opensocial.at database .
The database ran full about a year ago (what a shame, I know :( ...) and I had to recover some of the data (otherwise I had to import a backup about ~12hours ago, I tried to avoid to loose posts for this period of time for my users ...). So I stopped it, started it with innodb_force_recovery=3, repaired it and checked it with mysqlcheck.
Two days ago, I updated the mariadb from 1.10.3 to 1.10.10 and now the problems occur again.
I stopped the MariaDB 1.10.3 container, updated it to 1.10.10 and started it again .. And MariaDB started a "crash recovery". I really don't know why... The crash recovery wasn't successfully (I tried it ~4 times), so I had to add innodb_force_recovery=3 again.
Now the database went up, but everytime I stopped it and started it again, the crash recovery appears again.
So after the instance was up and running, I dumped the whole database with mysqldump into one single *.sql , started a brand new MariaDB 1.10.10 and imported the dump again.
So far so good ..
But... unfortunately, after a restart, the crash recovery appears again. So I'm totally lost, what's now happening..
I noticed during the import that one batch of rows took about 2 hours to complete with the result query affected 0 rows.
here's my customized.cnf, which I'm using (it's a Hetzner root server with 64 GB RAM and 12 CPUs):
[mysqld]
query_cache_size = 0
query_cache_type = 0
performance_schema = ON
join_buffer_size = 140M
innodb_buffer_pool_size = 12G
innodb_log_buffer_size = 31M
innodb_log_file_size = 3G
table_open_cache = 1000
max_connections = 400
wait_timeout = 200
interactive_timeout = 4000
log-bin = mysqld-bin
transaction-isolation = READ-COMMITTED
binlog-format = ROW
skip-innodb-read-only-compressed = ON
innodb_read_only_compressed = OFF
innodb_use_native_aio = OFF
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1For the new MariaDB instance, I didn't add any customization to avoid any wrong options.
currently, it makes a recovery again:
db_1 | 2022-12-16 10:41:00 0 [Note] InnoDB: Rolled back recovered transaction 371172
db_1 | 2022-12-16 10:41:11 0 [Note] InnoDB: To roll back: 1 transactions, 7611737 rows
db_1 | 2022-12-16 10:41:26 0 [Note] InnoDB: To roll back: 1 transactions, 7608132 rows
db_1 | 2022-12-16 10:41:41 0 [Note] InnoDB: To roll back: 1 transactions, 7603940 rows
db_1 | 2022-12-16 10:41:46 0 [Note] DDL_LOG: Crash recovery executed 1 entries
db_1 | 2022-12-16 10:41:46 0 [Note] Server socket created on IP: '0.0.0.0'.
db_1 | 2022-12-16 10:41:46 0 [Note] Server socket created on IP: '::'.
db_1 | 2022-12-16 10:41:47 0 [Note] mariadbd: ready for connections.
db_1 | Version: '10.10.2-MariaDB-1:10.10.2+maria~ubu2204' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
db_1 | 2022-12-16 10:41:56 0 [Note] InnoDB: To roll back: 1 transactions, 7601453 rows
db_1 | 2022-12-16 10:42:11 0 [Note] InnoDB: To roll back: 1 transactions, 7600146 rows
db_1 | 2022-12-16 10:42:26 0 [Note] InnoDB: To roll back: 1 transactions, 7598395 rows
db_1 | 2022-12-16 10:42:41 0 [Note] InnoDB: To roll back: 1 transactions, 7595967 rows
db_1 | 2022-12-16 10:42:56 0 [Note] InnoDB: To roll back: 1 transactions, 7594000 rows
db_1 | 2022-12-16 10:43:11 0 [Note] InnoDB: To roll back: 1 transactions, 7590646 rows
db_1 | 2022-12-16 10:43:26 0 [Note] InnoDB: To roll back: 1 transactions, 7585618 rows
db_1 | 2022-12-16 10:43:41 0 [Note] InnoDB: To roll back: 1 transactions, 7579929 rows
db_1 | 2022-12-16 10:43:56 0 [Note] InnoDB: To roll back: 1 transactions, 7574058 rows
db_1 | 2022-12-16 10:44:11 0 [Note] InnoDB: To roll back: 1 transactions, 7568214 rowslike this
Friendica Support reshared this.
Raroun likes this.
Friendica Support reshared this.
#Friendica needs new contributors!
I'm not used to do this, but with the large influx of new users and node admins recently coming from #Twitter, our small team is now behind the curve for handling support requests, bug reports and bring about much-needed features.
The project is built on a #PHP / #MySQL platform, but we also need people to be able to assist others just using the software to give developers some space.
If you're willing and able to help, please follow @Friendica Support and the project on Github: github.com/friendica/friendica
Thank you!
GitHub - friendica/friendica: Friendica Communications Platform
Friendica Communications Platform. Contribute to friendica/friendica development by creating an account on GitHub.GitHub
like this
reshared this
Michael Vogel
in reply to Philipp Holzer • • •Roland Hรคder
in reply to Philipp Holzer • • •SELECTcommand with acodeblock around it? And if you want to see what MySQL/MariaDB does when it executesSELECTcommands, just put aEXPLAIN EXTENDEDin front of it. ThatEXTENDEDgives more details about what the query optimizer did.