jrcore_cache gets very fragmented (and other db question)

i noticed this has always been an issue but with the db being big it can be a big issue
jrcore_cache often is the only table fragmented if not the rest are a few k
but the cache table often has 800 megs of overhead and optimization can reduce the total db size by nearly 1/3


another db thing
what would cause sub par db performance results along with sub par disk results

note: with default my.cnf after fresh os install i got a 2,400+ rating
with cpu 0.1 db i think was 4.9 or 5.6 in that range and disk was 0.87-98

i added most of my prevoius optimisations from old server
but had issues when restarting mysql (mariadb) i would have a huge number of waiting for table lock messages in error log and the shutdown would timeout (continue to shutdown but not restart) shutdown took 5-10 minutes!
i added some innodb tweaks i found in searches meant to improve that (and it did to a decet degre)
but somewhere in there the db performance fropped to between 9.6 and 15.6
and disk performance dropped to 1.86-2.65 (approx estimations)
this leads me to think the db is using disk too much (there is 252 m of swap being used..im used to that always being zero)

not asking ya o tune it for me (mysqltuner only suggests increase join buffer but thats what it always says)
just wondering what you think might be the most important thing to look at that might be causing this

ps will be testing an outrageously expensive db optimizer as soon as i figure it out
just wondering in the meantime what might be the cause..if you had to guess
really feel like its an innodb setting



--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
updated by @soaringeagle: 05/21/18 09:56:19PM

I'd recommend using the Redis cache module - I've just released the first non-beta version of it and it now handles multiple JR sites running in the same Redis server.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

just installed it (but got super sick) about to set it up


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

theres no documentation on how to set it up


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

There's not much to it. Click on "server browser" and add the local Redis server in - i.e.

127.0.0.1
6379

leave password blank for the local server.

Then in ACP -> Core enable Redis as the active data cache.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

ok i remembered having to set up diferent ports for diferent sites


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

getting Unable to retrieve stats from Redis Server
when trying to view server stats


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

Then Redis is either not running or not configured, or the info you entered is wrong.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

redis-cli MONITOR says unable to connect to redis server connection refused
so it is installed
im guessing its just a difeent port


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

i think i got it figured out


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

just so you know i did have to edit a few files to correct these issues
1755:M 20 Feb 12:42:21.679 # WARNING: The TCP backlog setting of 511 cannot be enforced because /proc/sys/net/core/somaxconn is set to the lower value of 128.
1755:M 20 Feb 12:42:21.679 # Server started, Redis version 3.2.11
1755:M 20 Feb 12:42:21.679 # WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.
1755:M 20 Feb 12:42:21.679 # WARNING you have Transparent Huge Pages (THP) support enabled in your kernel. This will create latency and memory usage issues with Redis. To fix this issue run the command 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' as root, and add it to your /etc/rc.local in order to retain the setting after a reboot. Redis must be restarted after THP is disabled.


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities