CheersnGears Posted January 26, 2012 Share Posted January 26, 2012 What are the ideal tables to switch away from MyISAM to Memory or Innodb? Link to comment Share on other sites More sharing options...
Grumpy Posted January 27, 2012 Share Posted January 27, 2012 Session to memory. Everything to myisam and sessions to memory is what IPB does on this forum (they said somewhere). Other data is too precious to host on memory. Innodb.... you can convert EVERYTHING to innodb if you like. lol Link to comment Share on other sites More sharing options...
Clickfinity Posted January 27, 2012 Share Posted January 27, 2012 What are the ideal tables to switch away from MyISAM to Memory or Innodb? Have you setup InnoDB for your MySQL install yet? (There's a bit of work to do before you can convert tables). How many posts do you have on your forums, and on average, how many members do you have active during the main parts of the day? Also, do you have Sphinx installed for search? (with Sphinx you can off-load some of the table data to the Sphinx indexes and reduce the size of your MySQL database). Cheers, Shaun :D Link to comment Share on other sites More sharing options...
CheersnGears Posted January 27, 2012 Author Share Posted January 27, 2012 Posts - 650k+ Users - 150 at a time, spiking to 250-300 during busy weeks (auto shows, big automotive news) I've not installed Sphinx. I though it was just for search, but if it can also downsize some of my tables, that perks my interest up a bit. Link to comment Share on other sites More sharing options...
Clickfinity Posted January 27, 2012 Share Posted January 27, 2012 I switched user sessions to HEAP (memory) and the only tables I switched to InnoDB when CycleChat was on IPB were the POST tables (I had 1.2 million posts at the time) - the key benefit being the row-level locking. I didn't really see a massive increase in speed or anything just a slight reduction in pauses when posting (writing to the tables). Are you looking to speed up your site or just make your MySQL setup more efficient? As an aside do you have any file caching or opcode caching installed? You'll likely see a greater speed benefit from going down that route first, but only if speed is the issue. If it's specifically MySQL based then you could consider converting your POST tables to InnoDB, but you need to prep MySQL first. Cheers, Shaun :D Link to comment Share on other sites More sharing options...
CheersnGears Posted January 27, 2012 Author Share Posted January 27, 2012 I have xcache installed. My board performance is ok, but any table operation crashes the server. I'm on VPS.Net and my hardware isn't shared with any other site. Yes, I've already optimized the tables, but I have to take down Apache to do it. Link to comment Share on other sites More sharing options...
Clickfinity Posted January 27, 2012 Share Posted January 27, 2012 Do you have any other apps using MySQL or is it exclusively for your forums? Link to comment Share on other sites More sharing options...
CheersnGears Posted January 27, 2012 Author Share Posted January 27, 2012 Forums exclusively Link to comment Share on other sites More sharing options...
Clickfinity Posted January 27, 2012 Share Posted January 27, 2012 TBH it sounds like your VPS is a bit underpowered - you shouldn't be crippling MySQL with that number of posts. Do you know the specs of your VPS? Link to comment Share on other sites More sharing options...
Grumpy Posted January 27, 2012 Share Posted January 27, 2012 "VPS.Net and my hardware isn't shared with any other site" Technically, it is. The whole point of VPS/Cloud is to share the hardware with other applications. One off topic ironic point is that vps.net is actually a cloud and not a vps, though some use it interchangeably. lol "I've not installed Sphinx. I though it was just for search, but if it can also downsize some of my tables, that perks my interest up a bit." Sphinx is just for search. It won't downsize some of your tables. However, search is the heaviest operation of any forum. Sphinx will do the search for you, and more efficiently, thus alleviating the need of a better hardware for mysql. If you have a LOT of memory available, and I mean a lot (which you can't really get in a vps/cloud system), you can use innodb to improve performance by allowing nearly the entire database to run off of memory caches. However, I suspect that's not possible here. "but any table operation crashes the server" Something doesn't really sound right... "Yes, I've already optimized the tables, but I have to take down Apache to do it." Something doesn't sound right here either. Optimizing tables shouldn't need anything to be taken down. Link to comment Share on other sites More sharing options...
Clickfinity Posted January 28, 2012 Share Posted January 28, 2012 Using Sphinx allows you to truncate your IPB search tables - however I don't think that will help. Tell us the specs of your VPS - the size of your MySQL database (#du -h ... in your MySQL data directory if you're on Linux) - operating system - and post the content of your MySQL config file (my.cnf) Also do you use gallery, and do you have a lot of images? Cheers, Shaun :D Link to comment Share on other sites More sharing options...
CheersnGears Posted January 28, 2012 Author Share Posted January 28, 2012 Dedicated CPU - 4.8GHz RAM - 3008MB SQL total table size as listed in MyPHPAdmin - 1.2 GiB Gallery - 4765 pictures taking up 7.19 gb of disk I can have as much memory as I want. I just have to pay for it, which I'd rather not do. Link to comment Share on other sites More sharing options...
CheersnGears Posted January 28, 2012 Author Share Posted January 28, 2012 # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # # # * IMPORTANT # If you make changes to these settings and your system uses apparmor, you may # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. # user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer = 512M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 tmp_table_size = 128M max_heap_table_size=128M join_buffer_size = 2M read_buffer_size = 1M sort_buffer_size = 2M open_files_limit=8192 table_cache=256 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 200 #table_cache = 128 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size = 64M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 log_error = /var/log/mysql/error.log # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer=96M sort_buffer=96M read_buffer=64M write_buffer=64M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ Link to comment Share on other sites More sharing options...
Luke Posted January 31, 2012 Share Posted January 31, 2012 By the way, FULLTEXT search is only available on MyISAM. Converting your posts table to InnoDB means you lose your ability to search. At that point you have to use Sphinx. There are other options, but IPB supports Sphinx. In general Sphinx is faster and more reliable than MyISAM's built in FULLTEXT functionality. You can also search for 3 letter words in Sphinx, which you can't with MyISAM unless you compile MySQL to allow smaller words (which makes it slower anyway). InnoDB handles large tables better. There's no reason why you can't convert them all to InnoDB. Making your sessions table a HEAP/MEMORY table helps as well, but keep in mind sessions will be lost if MySQL is restarted (server reboot, change of config, power loss, etc...). If you don't have a dedicated server, you have little control over when this happens. InnoDB's row level locking will help here. On my non-IPB apps I tend to use InnoDB for sessions (for survival) and cache them to memcache/apc for quick access (Not sure if IPB supports memcache like this). Link to comment Share on other sites More sharing options...
kmf Posted January 31, 2012 Share Posted January 31, 2012 I have a board with 5,5milion posts and have switched to innodb for several tables years ago when we were a small forum with only 1 million or so posts. we've switched posts, topics, session, members, forums. These are the most updated AND selected tables. Using myIsam killed the servers with all those table locking. Even session on HEAP suffered locking, so we switched that to innodb. It works fine, with some "buts". Due to the sheer size of the post-table (8GB), a restore (and thus rebuild of indexes) takes 7 hours the last time we did it.... So we only do that once a year... Select count-statements are REALLY slow, so we had to change a couple of queries. Also a pain in the ass when upgrading, because there are always some select count(*) in those upgrade scripts. Thesse would hopefully be history when ipb 3.3 is out, and I can archive like 80% of the posts. The new archival system puts the posts (and topics?) in another table. I'd make that myisam again. Link to comment Share on other sites More sharing options...
Clickfinity Posted February 1, 2012 Share Posted February 1, 2012 Dedicated CPU - 4.8GHz RAM - 3008MB SQL total table size as listed in MyPHPAdmin - 1.2 GiB Gallery - 4765 pictures taking up 7.19 gb of disk I can have as much memory as I want. I just have to pay for it, which I'd rather not do. I'll have a look at your .cnf file later, but just out of interest - how much would your host charge to up you to 8GB? Link to comment Share on other sites More sharing options...
CheersnGears Posted February 3, 2012 Author Share Posted February 3, 2012 Another $80. It would be another 8 nodes. It would also double my processor Link to comment Share on other sites More sharing options...
Clickfinity Posted February 6, 2012 Share Posted February 6, 2012 Another $80. It would be another 8 nodes. It would also double my processor Sorry, I haven't had chance to review your .cnf file - but if you can afford the $80 (and it's a one-off payment) I would get it organised ASAP as I think you'd definitely benefit from it. Link to comment Share on other sites More sharing options...
CheersnGears Posted February 6, 2012 Author Share Posted February 6, 2012 no, that would be monthly... and some changes to the My.cnf as of today along with some changes to the Apache config seem to have made the site feel a looooooot snappier. MySQL and Apache conspired last night to take my site down while I was on the train an unable to get a wireless signal. Newer My.cnf # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # # # * IMPORTANT # If you make changes to these settings and your system uses apparmor, you may # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. # user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking skip-innodb # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer = 512M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 tmp_table_size = 128M max_heap_table_size=128M join_buffer_size = 2M read_buffer_size = 1M sort_buffer_size = 2M open_files_limit=8192 table_cache=512 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 200 #table_cache = 128 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size = 64M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 log_error = /var/log/mysql/error.log # Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer=96M sort_buffer=96M read_buffer=64M write_buffer=64M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ Link to comment Share on other sites More sharing options...
Clickfinity Posted February 7, 2012 Share Posted February 7, 2012 no, that would be monthly... YIKES!!! :ohmy: Okay, well see how you go with your new my.cnf - but the only change I can see (apart from enabling logging of slow queries and non-index use) is a small increase in table cache size (which you can possibly increase further). I've modified your original my.cnf if you want to give it a go - MAKE A BACKUP OF YOUR my.cnf FIRST!! :) # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # # # * IMPORTANT # If you make changes to these settings and your system uses apparmor, you may # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. # user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking skip-innodb # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer = 512M myisam_sort_buffer_size = 32M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 tmp_table_size = 64M max_heap_table_size = 64M join_buffer_size = 256K read_buffer_size = 1M sort_buffer_size = 256K read_rnd_buffer_size = 256K open_files_limit=8192 table_cache=3000 concurrent_insert = 2 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 200 #table_cache = 128 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size = 64M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 log_error = /var/log/mysql/error.log # Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com...-variables.html Link to comment Share on other sites More sharing options...
Clickfinity Posted February 7, 2012 Share Posted February 7, 2012 Note: These are per-thread values as opposed to Global ones so will have a greater impact when changed (Global = assigned once, per-thread = assigned multiple times e.g. 20 threads x 256K [5MB] - 20 x 2MB [40MB] - 20 x 4MB [80MB] and so on, so a small value change from 256K to 4M can have a reasonable impact on your server which you might not intend.): join_buffer_size = 256K read_buffer_size = 1M sort_buffer_size = 256K read_rnd_buffer_size = 256K Also, you might want to check how large your /tmp directory is - if it's quite small this may be why MySQL crashes on large operations (may be completely unrelated, but it's something to check - just in case!). #df - at the command line should give you the size. Cheers, Shaun :D Link to comment Share on other sites More sharing options...
Clickfinity Posted February 9, 2012 Share Posted February 9, 2012 Just another thought - do you have phpMyAdmin insalled? You can use the status page to assess how your settings changes are impacting on MySQL. Also there are a couple of command line tools you can use that give you good feedback on MySQL's current efficiency:MySQLTuner.pl - https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl (highlight all the text and copy - then save on your machine as mysqltuner.pl and run it from the command line [may need to adjust perms] - DON'T do File > Save As ... because it will wrap HTML around the header and footer!!)tuning-primer.sh - http://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh Cheers, Shaun :D Link to comment Share on other sites More sharing options...
CheersnGears Posted February 11, 2012 Author Share Posted February 11, 2012 I did run the mysqltuner and have been tweeking it based on those suggestions every 2 days. The DF for my /tmp directory shows 33gig free. Everything seems to be running smoothly lately. We made it through the high traffic days of the Chicago auto show without any hiccups Link to comment Share on other sites More sharing options...
Clickfinity Posted February 11, 2012 Share Posted February 11, 2012 Great stuff ... :thumbsup: Link to comment Share on other sites More sharing options...
Alexia Smith Posted February 14, 2012 Share Posted February 14, 2012 InnoDB for every* single table running under MySQL 5.5. *Some of the sites I work on use Redis caching for the sessions table. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.