mod.php?/IP/x.x.x.x takes way too long to load when there's a lot of posts

[christian-2hu]

Is it overkill to add a cache on IP addresses, create an index on the IP column in posts_sql or even add pagination? Loading posts from db takes ages when there's a lot of it.

I can do this, but I think messing with the db schema may lead to a major release and cache would be a little to much

[perdedora]

The page ip is probably one of heaviest to load and render. The DB schema doesn't help at all since each board has its own table, so if your imageboard has several tables, this is going to stress a lot.

Caching kinda works, but if your instance has any filters/fields only to be displayed to admin/mod, the cache became a problem if whom set the cache is from a different access group, twig will throw an error another if other person from a different group access. Another problem is: if a mod who has only access to 1 board and set the cache, the history would be incomplete.

I'm currently working on a solution to be able to paginate the ban list (another nightname) and will extend to that.
But of course, if you made a solution, we would be happy to take a look

[christian-2hu]

Caching kinda works, but if your instance has any filters/fields only to be displayed to admin/mod, the cache became a problem if whom set the cache is from a different access group, twig will throw an error

Hmmm, I'm not talking about a twig cache but an in-memory cache. What would be in the cache is not the rendered twig pages, but the post itself so twig can render ir later. Since vichan already has integration with in-memory tools like memcached and redis-server, it wouldn't be that difficult.

Another problem is: if a mod who has only access to 1 board and set the cache, the history would be incomplete.

The only difference here is that the post is on the cache, instead of being on database, the logic won't really be affected.

For example, if cache on ip pages is set on instance-config and there is a cache miss, it will load all the posts for the given ip and display for the end-user. If it takes more than it would take, since we are now storing all the posts from the ip and displaying it later, we can display first and trigger an event to load the posts on the cache.

This could not be as great as the solution you are working on, but could help while you're not done. What do you think?

[ctrlcctrlv]

you're starting from the tail end of the problem. what does the profiler say? what's even taking a long time? the query, the html generation, something else?

[christian-2hu]

you're starting from the tail end of the problem. what does the profiler say? what's even taking a long time? the query, the html generation, something else?

The query is the problem, that's why I suggested an index in the IP column in the posts. And if messing with the database schema is not possible, then maybe a cache would be a good idea or even limit the quantity of posts shown per board instead of showing it all.

[ctrlcctrlv]

this is pretty easy to A/B test as (correct me if i'm wrong 😄) in MariaDB née MySQL you cannot access from the query language the index data (that is to say there is no CREATE [OR REPLACE] SEQUENCE command).

so get a mariadb shell and just try a CREATE INDEX and see. then if it works well you can PR against install.sql