Hello !Hubzilla Support Forum
I have some performance issues with a database query. The query is running for around 10 seconds and is producing a remarkable load. Sometimes a lot (50-70) of these queries are running at the same time. When this happens they're running for up to half an hour each and the load goes through the roof. Today it was so bad (load avg. 70) that the server was overloaded and the frontend stopped working.
# Query_time: 10.013510 Lock_time: 0.000065 Rows_sent: 50 Rows_examined: 13878807
# Rows_affected: 0
# Full_scan: No Full_join: Yes Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
select xtag_term as term, count(xtag_term) as total from xtag
left join hubloc on xtag_hash = hubloc_hash
where xtag_flags = 0 and xtag_hash in (select hubloc_hash from hubloc where hubloc_host = 'some.hubzilla.node' )
group by xtag_term order by total desc limit 50;
"Rows_examined: 13878807" - wow! Any idea what that query does and why there are so many at the same time?