Howto: Database optimizing

Thought of something everyone else is having trouble with and just wanna help them? Post it here.
LaMaH
Apprentice
Posts: 44
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by LaMaH » Fri Nov 28, 2008 21:55

teck7 wrote:
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 :|
im not a noob :P
Ive done all the optimizations in the thread, innodb has lots of memory, apache is tweaked, mysql is tweaked, memcache is fixed, im using xcache but its the same, weekly optimizations are being done, server is not swapping to disk etc etc etc..

So like, yeah ;)
Co-Owner and developer of EVSCO

Raul Lustrom
Greenhorn
Posts: 13
Joined: Mon Oct 13, 2008 01:45

Re: Howto: Database optimizing

Post by Raul Lustrom » Sat Nov 29, 2008 18:10

I performed all the optimisations in this thread, and now my API Mod 2.6 has stopped importing kills and comes with this error:

Query failed. Data too long for column 'ind_sec_status' at row 1

What length should that column be? Its set to Char(5) at the moment.

Kovell
Veteran
Posts: 1036
Joined: Wed Nov 26, 2008 13:35

Re: Howto: Database optimizing

Post by Kovell » Mon Dec 01, 2008 04:01

Mine, unchanged, is set to varchar(5).

Kovell
Veteran
Posts: 1036
Joined: Wed Nov 26, 2008 13:35

Re: Howto: Database optimizing

Post by Kovell » Tue Dec 23, 2008 02:39

I've had good results changing the checks for week and year. Not as simple as changing indexes, though. A killlist, for example, has functions to restrict range by week using

Code: Select all

and date_format( kll.kll_timestamp, \"%u\" ) = $weekno
but MySQL needs to look at every row that may match and convert all the timestamps using date_format so it can make the comparison. If you use the setStartDate and setEndDate functions you get kll.kll_timestamp > [timestamp for start of week] and kll.kll_timestamp < [timestamp for end of week]. The index can then be used to select the range you want.

LaMaH
Apprentice
Posts: 44
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by LaMaH » Mon Mar 02, 2009 03:32

what is quite fun that we've noticed while running EVSCO.

Is that InnoDB did not work well, since we got alot of IOWait - so we changed the database to myisam, and bam - no iowait and queries are ten times faster.
Quite funny :P
Co-Owner and developer of EVSCO

beansman
Regular
Posts: 221
Joined: Sat Jun 14, 2008 20:24

Re: Howto: Database optimizing

Post by beansman » Wed Mar 04, 2009 10:16

But only after we hit 1 mio. mails :p
/HyperBeanie

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

Post Reply

Return to “Tutorials”

Who is online

Users browsing this forum: No registered users and 1 guest