Hello Mamba, Thank you for this wonderful work!

I hope you will forgive my poor level of English but I have the impression that there is still a bug with the attached files (or else it is only at home, but I do not think so because I have tested on several versions of xoops).

It is possible to post them on the forum, but when I want to open them I get this message:

Quote:
While on final version 5.0, the attached file downloads correctly.

But on the other hand, on version 5.0, I could only attach one file at a time.
When I send a second, it removes the first.

So to summarize:

- 5.00 final, only one attachment is accepted by mail. if we send a second, it removes the first.

- 5.01 Beta 4, we can send more than one, but we can't get them back.

@geekwright

FYI; reverted to old db 300000K+ users, 100K never logged in. Your changes still work.

...B

The NewBB 5.01 Beta 4 is now available for testing, forking, and improvement contributions! You can help us with testing and improvements.

Big THANK YOU to BigKev73 (again) for his several improvements/fixes!

DOWNLOAD: https://github.com/XoopsModules25x/newbb/releases

FORK: You can fork it from GitHub: https://github.com/XoopsModules25x/newbb/

Note: After download, rename the folder from "newbb-master" to "newbb"

WARNING: Do NOT use it in production installation, this is only for testing

Minimum requirements:
XOOPS 2.5.10 (for testing we recommend the upcoming XOOPS 2.5.11)
PHP 5.7.2+ (tested with PHP 8)

That is great news!

Thank you for bringing this issue to our attention, and reporting your results.

Since you reported this, I've observed several sites where the problem queries are taking quite a bit of resource to complete. None are extreme enough to grind to a halt, but obviously they are using way too much. Eventually, left unchanged, I'm sure they will run out of something and start failing. Xoops.org is one of those, and mamba does a good job of keeping us honest with the "eat our own dogfood" strategy -- most of the time, especially near releases, we are running the newest code from the head once it has passed local testing.

Next step, I'll use that 2.5.4 system I started up to test the upgrade for 2.5.11. Waste not, want not as my mother used to say.

Thanks again.

@geekwright

Thanks!!!
Replaced two files - can now see to admin->users (still at 200000 users)
Have not yet tried cleanup query

If you want/need a large users table for testing your changes on trunk, I can provide.

...B

@geekwright

You da man. Went above and beyond in this one. Will give it a spin late today or tomorrow once I untangle current (non-xoops) issues I am working on.

Surprises me that other xoops users have not run into this previously...

Thanks;
Bill

I played with the users page and SQL in the member handler to try and lower the MySQL load with some success. I tracked down XOOPS 2.5.4 and spun up a docker environment it could run on and backported the patches. There are two changed files. I've put them on GitHub so you can download them:

Patch for XOOPS 2.5.4 part 1 - htdocs/kernel/member.php
https://gist.github.com/geekwright/f845e0341fc538e7ed2c8cce43863494

Patch for XOOPS 2.5.4 part 2 - htdocs/modules/system/admin/users/main.php
https://gist.github.com/geekwright/e25047092476995a5d5b095087a18b7c

Please five them a try -- they should drop the load significantly.

Quote:
Here is a query that can do the cleanup.


Replace the xxxx with your prefix.

I would recommend against the index on user_regdate now. The changes in admin/users/main.php should achieve the goal without that.

Give it a try, and let us know how it goes. Good Luck!

@geekwright

- my tables are too large for php(myadmin) import, am using using cli import
- original sites use sme-server 9.2 which has been replaced by sme-server 10. The sme 10 copies I am working on use sql exported (phpmyadmin) and imported (cli). Problem exists on old sme 9.2 and sme 10 sites.
- I always use expendable VM copy when doing dangerous stuff such as (db) global changes
- Disk layout or corruptions not the issue, since sme-10 VM's created from scratch. Xoops installation copied from old.

"try an index on the user_regdate column" HOW?, Just a matter of adding index property, or altering mysql queries? Ditto for limit clause.

"groups_users_link" dead entries. Problem?, how remove?

If your investigation requires access to 2.5.4, PM me and I will setup ssh (to a sme-10 copy VM, primary still running under sme-9.2 until migration complete) access for you. Or, you can DL entire VM, but my upload speed slow, will take hours to DL.

...B

I started looking at what the users page queries, and I spot some things that are definitely going to be inefficient at scale, specifically this query as shown in the debugger:

SELECT DISTINCT u.* FROM users AS u LEFT JOIN groups_users_link AS m ON m.uid = u.uid WHERE ((`level` >= '0')) ORDER BY user_regdate DESC LIMIT 0, 20

I do not have a running XOOPS 2.5.4 system handy to check, but I will just assume it is similar. It has a guaranteed table scan, with a join, and a sort on a non-indexed column -- that's going to be problematic at scale. I'll look at this issue, but I'll be working from our newest code. I'm not sure at all how anything will backport at this point.

Thinking strictly on the MySQL side, when things make sudden jumps in resource usage, there is often a factor in how things are laid out on the disk volume, or some corruptions. I would try exporting the table, dropping and importing it. (On your scale, import is probably best done from mysql on the command line, not through phpMyAdmin.) That might smooth out a few things.

If that didn't do it, I might also try an index on the user_regdate column. That and the limit clause might be enough to reduce the physical i/o to manageable levels.

Another thing to consider, since your user delete was done outside of XOOPS, the groups_users_link table now contains entries where the uid no longer exists in the users table. The saving grace is the table row is very small, but it should have around 200000 dead rows.

NB - always make a backup, and work on an expendable copy NOT on your production data.

Thanks Alain;

Yes, aware of maintenance GUI, use the script after contents update (too lazy to unset / set caching) and (maybe, eventually) a cron job.

How 'bout this:
[ code]
#!/bin/sh

rm -rf ../xoops_data/caches/xoops_cache
rm -rf ../xoops_data/caches/smarty_cache
rm -rf ../xoops_data/caches/smarty_compile
mkdir -p ../xoops_data/caches/xoops_cache
mkdir -p ../xoops_data/caches/smarty_cache
mkdir -p ../xoops_data/caches/smarty_compile
cp ../xoops_data/caches/index.* ../xoops_data/caches/xoops_cache
cp ../xoops_data/caches/index.* ../xoops_data/caches/smarty_cache
cp ../xoops_data/caches/index.* ../xoops_data/caches/smarty_compile
chown -R www:sysops ../xoops_data/caches
[ /code]

Correct me if wrong: "cp -p" unnecessary since ownership explicitly set, mode is inherited from dest dir and, don't care about timestamps

Regards;
Bill