Database optimization

[Bas]

Hi!

*** mysql + partitioning ***

On behalf of http://dev.mysql.com/doc/refman/5.7/en/ ... oning.html ;]
Sorry for imgs, but forum tags are bugged.

EDK has some big tables: http://c2n.me/3u4MB6V.png (lol, yeah. my kb not so big)
Some of them with datetime field: http://c2n.me/3u4Q0Cp.png
I tried to create partition based on datetime field, but have error: http://c2n.me/3u4QU76.png

This optimization can help with big data tables, because users fetch only index page and some pages ago. So we can split table for current month / week and "all others". Queries will run faster.

First of all i must to change field type to timestamp, but what EDK will think about this changes?



*** mysql + memcache ***

Okey, 2nd... Memcache and multi-kb instances.
I have 3 boards on my server. File cache is ok, but not enough
Can you add KB_SITE or database name to memcache key as prefix? This will help with servers with one memcahe instance.
For example, this will fix my problem: http://www.evekb.org/forum/viewtopic.ph ... 391#p49391
I think I can make the changes myself, but i can break something as usual ::facepalm::

[Salvoxia]

Hi,

will definitely look it this! But it won't make it the February release, I'm afraid.
At the first glance this change should be possible and be backwards compatible, but these tables are always a PITA to migrate (but we did it before).

And great that you found out what caused your bug! Sounds like a sensible change, will look at this, too!

Best Regards,
Salvoxia

[Bas]

>>
>> will definitely look it this! But it won't make it the February release, I'm afraid.
>> At the first glance this change should be possible and be backwards compatible,
>> but these tables are always a PITA to migrate (but we did it before).
>>
>> And great that you found out what caused your bug! Sounds like a sensible change, will look at this, too!
>>

1st - yeah, sure, i'm afraid to do this on my board w/o tests too ;] i'm so lazy, that is why i post message to discuss ;]
If changing the data type won't break anything, nobody will known about this.
But these changes will help to use this DB tweak for advanced users.

2nd - i want to try, but I can miss something important in sources, because looked only class.cache* files for a while.
can help with tests

[Salvoxia]

Hi,

regarding 2nd, from the top of my head:
In the files class.cachehandlerhashedmem.php and class.cachehandlerhashedredis.php, in all functions that get $key as an argument, prefix $key with the constant KB_SITE. It's a completely transparent change and should do the trick.

Best Regards,
Salvoxia

[Bas]

I test with patch in attach.

Omg, forum works worse than my kb Can't attach files

https://github.com/evekb/evedev-kb/pull/52

[Salvoxia]

I gave it a test run and it seems to work. However, my original proposal was a bit overdone: All the other methods, that get $key as an argument, use it to call the hash() method. So it's enough to add the site there. The way it is know, it gets added twice.

Best Regards,
Salvoxia

P.S.: And I enabled attachments for .php files.