Hi;

My sites are popular and have accumulated may users over the years.

When I login as administrator and select "users", page times out and mysqld CPU is greater than 100%.
This state of affairs persists until site VM reboot.

xoops-2.5.4, php-5.4.16

I have much custom coding on these sites and no time / resources to update

Any quick-n-dirty suggestions?

Thanks;
Bill

You might try to clear the cache via the Admin/Maintenance
I'm not sure if it will help, but you can try.

The other option is to delete user accounts that are old but never connected.

The best option is to update PHP to PHP7+, as it will speed things up, but for that, you would need to update XOOPS and your modules, which seems that it's not an option for your right now.

Thanks Michael;

did this:
[ code]
mysql > use dbname;
mysql > DELETE FROM `xoops_users` WHERE `last_login` = 0 ORDER BY `last_login` ASC; #Not sure if ORDER BY and ASC neccessary
[ /code]
...and went from approx 300000 to approx 100000 users. Problem still persists

next (phpmyadmin), looked for duplicate url's (spam registrations). Approx 1000 users with url's so, not issue

Clean cache ineffective. Can you "bless" my clean cache script?:
[ 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.html ../xoops_data/caches/xoops_cache
cp ../xoops_data/caches/index.html ../xoops_data/caches/smarty_cache
cp ../xoops_data/caches/index.html ../xoops_data/caches/smarty_compile
chown -R www:sysops ../xoops_data/caches
[ /code]

I guess I will have to use phpmyadmin for any user modifications.

Any other suggestions?

Thanks;
Bill

cp -p ../xoops_data/caches/index.html ../xoops_data/caches/xoops_cache

"-p" to preserve mode,ownership,timestamps

There is a solution to delete all except 1 file,
in bash :
>shopt -s extglob
then
>rm -- !(index.html)
But be careful, test it on tempory directory and with recursive directories (rm -r)

Note A :
Since XOOPS 2.5.9, there is a admin maintenance page to do it , empty cache (smarty_cache / smarty_compile, xoops_cache)

Note B :
On the XOOPS 2.5.11, we use index.php with this content , not index.html :

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

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.

@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 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

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

@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