Howto: Database optimizing

[sapyx]

Ty guys.
StainWagon now...fly!!!

[Erku Badu]

Sod editing files ill wait till kb evolves to have it added

[rosander]

First off for 98% of the users here the greatest benefit can be found in mysql 5+, innodb tables, and adding helpful index's. 1 Gb of ram with a large cache for innodb tables really makes all the difference and with todays memory prices there is no excuse for not having at least that much installed.

Memcache is helpful to the kb but you do need to weigh the extra install work and upkeep of another program vs using built in cache of mysql. Just to beat a dead horse. For MOST users a well tuned db and cached server will resolve any load issues. Now if you are hosting the kb in a cluster, get memcache. I doubt however many users are. As much as we like to be cool, the kb's just don't draw that much load.

[LaMaH]

First off for 98% of the users here the greatest benefit can be found in mysql 5+, innodb tables, and adding helpful index's. 1 Gb of ram with a large cache for innodb tables really makes all the difference and with todays memory prices there is no excuse for not having at least that much installed.

Memcache is helpful to the kb but you do need to weigh the extra install work and upkeep of another program vs using built in cache of mysql. Just to beat a dead horse. For MOST users a well tuned db and cached server will resolve any load issues. Now if you are hosting the kb in a cluster, get memcache. I doubt however many users are. As much as we like to be cool, the kb's just don't draw that much load.

what?..

try running an alliance killboard, and then say that a kb doesn't draw that much load
Even WITH Memcache my quadcore server is struggling to keep the Triumvirate board up

[ralle030583]

First off for 98% of the users here the greatest benefit can be found in mysql 5+, innodb tables, and adding helpful index's. 1 Gb of ram with a large cache for innodb tables really makes all the difference and with todays memory prices there is no excuse for not having at least that much installed.

Memcache is helpful to the kb but you do need to weigh the extra install work and upkeep of another program vs using built in cache of mysql. Just to beat a dead horse. For MOST users a well tuned db and cached server will resolve any load issues. Now if you are hosting the kb in a cluster, get memcache. I doubt however many users are. As much as we like to be cool, the kb's just don't draw that much load.

what?..

try running an alliance killboard, and then say that a kb doesn't draw that much load
Even WITH Memcache my quadcore server is struggling to keep the Triumvirate board up

thats the build in anti-Triumvirate code in it (sry coudn't resist) *activating cloak*

but sadly LaMaH is right, if you reach a certain amount of kills (which big alliances will do) you will get load probs with the EDK

[LaMaH]

thats the build in anti-Triumvirate code in it (sry coudn't resist) *activating cloak*

but sadly LaMaH is right, if you reach a certain amount of kills (which big alliances will do) you will get load probs with the EDK

anti-Triumvirate code?! :O
U be kidding me! its not there.. im sure..

/me goes to have a look




Anyway, memcache is easy - you install it, set it up to use an x-portion of memory, setup a script that restarts it every 24h (atleast what i did - might be easier ways to clear its memory lol) - and bam, you dont need to mess with it anymore

And usually most hosts are very friendly against _ANYTHING_ that will lower your SQL Database usage

[Guest]

should i run this before, or after doing the QR datadump?

my kb version atm, is 351, and i plan on doing the datadump and this script in this thread before updating my kb

[beansman]

Doesn't matter, just make sure you have a backup before you do it.

[rosander]

Well I still stand by my statement about good optimization on mysql with indexing takes care of most of your system load. We've got over 180k mails (yes for an alliance) with average load per day at 12 Apache hits per sec. Peaks at 45. Multiple corp boards in addition to alliance all using the common database. Add in phpbb3 and a kos system. Still no real performance hits. If we added another machine to function as memcache w/ apache round robin I'd support that plugin. The point being you don't have to.

There's no question about benefit. I manage dozens of servers and always have to weigh more programs/process's that can lead to greater down times. Shoot for 6-8 "9's" in uptime and you always have to balance the number of tools vs reliability.

[teck7]

First off for 98% of the users here the greatest benefit can be found in mysql 5+, innodb tables, and adding helpful index's. 1 Gb of ram with a large cache for innodb tables really makes all the difference and with todays memory prices there is no excuse for not having at least that much installed.

Memcache is helpful to the kb but you do need to weigh the extra install work and upkeep of another program vs using built in cache of mysql. Just to beat a dead horse. For MOST users a well tuned db and cached server will resolve any load issues. Now if you are hosting the kb in a cluster, get memcache. I doubt however many users are. As much as we like to be cool, the kb's just don't draw that much load.

what?..

try running an alliance killboard, and then say that a kb doesn't draw that much load
Even WITH Memcache my quadcore server is struggling to keep the Triumvirate board up

Although yes the tri board has a large amount of kills and EDK doesnt scale well past a certain point, you should nevertheless be able to reach acceptable load times (10s<) per request per visitor given that you:
1) pay close attention to the optimization details in this thread, in particular the indexing/table format changes
2) use innodb AND MAKE SURE innodb is given a good bit of memory to work with
3) ensure the mysql config is appropriate for both the hardware you are using AND the request load upon the server
4) make sure that web content and databases if possible are being served from different disks (i/o becomes an issue on very active and larger killboards)
5) memcached is nice but careful with how much memory you give it, it will eat whatever is thrown at it often with no benefit to performance past a certain point, you may also want to try PHP APC Cache (http://pecl.php.net/package/apc) along side memcached
5) turn off atime tracking on frequently used file systems (i.e: / , /var etc..), reduces i/o requests (set noatime flag in fstab)
6) running regularly scheduled mysql optimize operations to clear out overhead data is always a good idea , such as weekly (usr/bin/mysqlcheck --optimize --all-databases)
7) make sure the server is not swapping memory to disk (free -m , make sure little to no swap in use), if it is refine the memcached/mysql settings

just my 2cents
me need sleep