Howto: Database optimizing

Thought of something everyone else is having trouble with and just wanna help them? Post it here.
User avatar
sapyx
Noob
Posts: 8
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by sapyx » Thu Oct 30, 2008 12:50

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

Erku Badu

Re: Howto: Database optimizing

Post by Erku Badu » Thu Oct 30, 2008 23:43

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

rosander
Noob
Posts: 6
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by rosander » Mon Nov 10, 2008 13:41

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
Apprentice
Posts: 44
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by LaMaH » Tue Nov 11, 2008 00:32

rosander wrote: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
Co-Owner and developer of EVSCO

ralle030583
Padavan
Posts: 219
Joined: Sat Jun 14, 2008 20:24
Location: Germany
Contact:

Re: Howto: Database optimizing

Post by ralle030583 » Tue Nov 11, 2008 00:48

LaMaH wrote:
rosander wrote: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 :-P (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
Apprentice
Posts: 44
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by LaMaH » Tue Nov 11, 2008 13:47

ralle030583 wrote: thats the build in anti-Triumvirate code in it :-P (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

:lol:


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 ;)
Co-Owner and developer of EVSCO

Guest

Re: Howto: Database optimizing

Post by Guest » Wed Nov 19, 2008 08:58

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
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman » Wed Nov 19, 2008 13:12

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

Author of the Value Fetcher: Get It Here
Co-owner and developer of EVSCO

rosander
Noob
Posts: 6
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by rosander » Thu Nov 20, 2008 20:57

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
Noob
Posts: 2
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by teck7 » Thu Nov 27, 2008 16:17

LaMaH wrote:
rosander wrote: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 :|

Post Reply

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 2 guests