Database optimization

Development information and discussion about EDK.
Post Reply
Bas
Apprentice
Posts: 65
Joined: Sun Apr 20, 2014 12:11
Location: Russia, Moscow
Contact:

Database optimization

Post by Bas » Tue Feb 09, 2016 16:10

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: viewtopic.php?f=506&p=49391#p49391
I think I can make the changes myself, but i can break something as usual ::facepalm::

User avatar
Salvoxia
Developer
Posts: 1531
Joined: Wed Feb 22, 2012 12:11

Re: Database optimization

Post by Salvoxia » Tue Feb 09, 2016 17:55

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
Apprentice
Posts: 65
Joined: Sun Apr 20, 2014 12:11
Location: Russia, Moscow
Contact:

Re: Database optimization

Post by Bas » Tue Feb 09, 2016 21:56

>>
>> 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 :)

User avatar
Salvoxia
Developer
Posts: 1531
Joined: Wed Feb 22, 2012 12:11

Re: Database optimization

Post by Salvoxia » Tue Feb 09, 2016 23:16

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
Apprentice
Posts: 65
Joined: Sun Apr 20, 2014 12:11
Location: Russia, Moscow
Contact:

Re: Database optimization

Post by Bas » Wed Feb 10, 2016 09:10

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

User avatar
Salvoxia
Developer
Posts: 1531
Joined: Wed Feb 22, 2012 12:11

Re: Database optimization

Post by Salvoxia » Thu Feb 11, 2016 19:01

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.

Post Reply

Return to “EDK Development Suggestions”

Who is online

Users browsing this forum: No registered users and 1 guest