Howto: Database optimizing

[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

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

[Raul Lustrom]

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]

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

[Kovell]

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]

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

[beansman]

But only after we hit 1 mio. mails :p