Hello, I run a site that has a large database (~7GB). I've been constantly looking for ways to optimize the server as sometimes it will lag with a bunch of HTTPD processes, and high server load. I've just come across editing my my.cnf and here is what I have. Also I have run mysqltuner and tried to edit accordingly to it's suggestions. I just want the server to "crash" so to speak which I believe is due to the mysql. [mysqld] safe-show-database back_log = 100 max_connections = 6000 query_cache_type = 1 query_cache_size = 50M key_buffer = 512M thread_cache = 4 skip-innodb log-slow-queries table_cache = 256 join_buffer_size = 128M Code (markup): My Server Stats: Intel(R) Xeon(R) CPU X3320 @ 2.50GHz x 4 cores 4GB MEM MySQL 5.0.51a Php 5.2.16 Apache 2.2.17 Thanks for your help. I am not an Expert at this stuff; but I am quite computer savy.
MyISAM doesn't really tune well. You can adjust your caches and buffers, but you can't get much better than out of the box with MyISAM. If you switch to Innodb there is a lot you can do but make sure you understand how your application will react if you do decide to switch. http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/
whatever you do, in the end mysql is slow! did you consider using a NoSQL database type? do a google search for "nosql"
? It's the MYSQL that's with the 100% CPU. Well it looks like InnoDB is better for larger databases.. And my forum software is compatible with InnoDB. Looks like you should install spinx for search as innodb doesn't handle full text searches.. but how do I go about converting from MyISAM to InnoDB then? Keep in mind I have a 6.5GB Database. Will my forum software even work with nosql?
i have to agree with @jestep innodb is way better in terms of tweaking and scaling. migration is not hard at all: just dump your myisam db with the mysqldump command line tool, string replace the "type = myisam" lines in the dump, convert your db server then load the data. 7 gigs is not that much of a data, should be done in a matter of minutes
While talking about percona, i'd switch to MariaDB 5.2.x MySQL server better MyISAM and InnoDB performance overall as it's the only performance fork to continue improve MyISAM performance as well see benchmarks at http://vbtechsupport.com/606/ and http://vbtechsupport.com/657/ Just be aware innodb uses dramatically more cpu, memory and disk storage / I/O resources than MyISAM. For 7GB database full innodb based you're need at least 8-12GB of physically installed memory and appropriate innodb tuned parameters in my.cnf
With a setting like this, im amazed it runs, as this is going to run the server out of ram very easily when a select or two has no indexes. join_buffer_size = 128M
Matter of minutes? I'm not one hundred percent sure on the procedure behind this, could you elaborate please? Can anyone confirm this, I would have to have that much ram installed? .. With mariaDB, I would be better off with the 4 gigs installed? What should I have this set at? Higher? Lower??