Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
P15-D24 Posted May 29, 2015 Posted May 29, 2015 Chasing a reoccurring issue with my sessions table corrupting and crashing the site. Usually I can recover with the tools in phpadmin, however not always and I need to create a new DB and load from my morning backup. Site is on a dedicated 3 GB virtual server (not shared). One problem the ISP has identified is search bot traffic and I have taken steps to control access. (Tend to have issues around the 21st of every month). Currently looking at using Sphinx for search but it is not yet implemented. Site typically has 40-50 users all day log, with peaks to 75-100 users in the early evening. The ISP has suggested changing the sessions table to InnoDB structure to help stop the corruption issues.I'm unfamiliar with this and looking for input from the community as the best way to proceed. In talking to IPS customer support they keep pointing me back to the ISP, while the ISP makes suggestions but I'm on my own when converting the database tables to InnoDB. I'm sure their are larger community sites that have already dealt with this issues and can provide some hands on guidance. Your input is appreciated. Regards, GT
AutoItScript Posted May 29, 2015 Posted May 29, 2015 The sessions table shouldn't be getting corrupted. End of story. Reasons to move to InnoDB would be mostly performance related. This page shows some ways a table would be corrupted, all are the result of something being very wrong with the server/setup. http://dev.mysql.com/doc/refman/5.1/en/corrupted-myisam-tables.html If your site is crazy busy, it will just be slow, it still wouldn't cause corruption. I do a nightly backup and also run an auto-repair operation during that time just to be sure but there's not been a single instance of a corrupt table that I can recall in 10 years.
P15-D24 Posted May 30, 2015 Author Posted May 30, 2015 ... This page shows some ways a table would be corrupted, all are the result of something being very wrong with the server/setup. http://dev.mysql.com/doc/refman/5.1/en/corrupted-myisam-tables.html If your site is crazy busy, it will just be slow, it still wouldn't cause corruption. I do a nightly backup and also run an auto-repair operation during that time just to be sure but there's not been a single instance of a corrupt table that I can recall in 10 years.Site is not crazy busy. So if InnoDB is really a performance fix, what are the areas in the server setup I need to be investigating? I looked at the supplied link but don't see any specific server config information to try. Looking at your site you have at least 4 or 5 X the traffic I see so I believe mySQL can be set up to be stable. Sounds like the nightly auto repair routine is a good preemptive repair approach. Any suggestions appreciated.
AutoItScript Posted May 30, 2015 Posted May 30, 2015 I can't think of any obvious settings that would cause this. It's most likely a hardware issue. But it's worth making sure that the config is correct by running the script from http://mysqltuner.com . Post your my.cnf file as well to see if there's anything obviously misconfigured. There is one config option that will corrupt tables if the mysqld service crashes, but it's not a default so unlikely. And the service shouldn't be crashing anyway so worth checking the uptime of the service to see if it is getting restarted (or the mysqld.log file).My server has 8GB of ram but I only need to use 4GB in mysql to support my site for 100 simultaneous connections. Usually there are only ~15 connections in use.
P15-D24 Posted June 3, 2015 Author Posted June 3, 2015 >> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filteringPlease enter your MySQL administrative login: userXXPlease enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.35-log[OK] Operating on 64-bit architecture -------- Storage Engine Statistics -------------------------------------------[--] Status: +CSV +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 383M (Tables: 296)[!!] InnoDB is enabled but isn't being used[!!] Total fragmented tables: 37 -------- Security Recommendations -------------------------------------------ERROR 1142 (42000) at line 1: SELECT command denied to user ‘userXX’@‘localhost' for table 'user'[OK] All database users have passwords assigned -------- Performance Metrics -------------------------------------------------[--] Up for: 5d 0h 17m 40s (3M q [8.433 qps], 137K conn, TX: 92B, RX: 1B)[--] Reads / Writes: 56% / 44%[--] Total buffers: 240.0M global + 1.6M per thread (500 max threads)[OK] Maximum possible memory usage: 1.0G (36% of installed RAM)[OK] Slow queries: 0% (17/3M)[OK] Highest usage of available connections: 6% (34/500)[OK] Key buffer size / total MyISAM indexes: 128.0M/222.8M[OK] Key buffer hit rate: 99.4% (93M cached / 545K reads)[OK] Query cache efficiency: 37.5% (840K cached / 2M selects)[!!] Query cache prunes per day: 23548[OK] Sorts requiring temporary tables: 0% (43 temp sorts / 75K sorts)[!!] Joins performed without indexes: 4236[OK] Temporary tables created on disk: 17% (15K on disk / 85K total)[!!] Thread cache is disabled[!!] Table cache hit rate: 0% (128 open / 25K opened)[OK] Open file limit used: 10% (254/2K)[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks) -------- Recommendations -----------------------------------------------------General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes Set thread_cache_size to 4 as a starting value Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4CVariables to adjust: query_cache_size (> 16M) join_buffer_size (> 128.0K, or always use indexes with joins) thread_cache_size (start at 4) table_open_cache (> 128)
P15-D24 Posted June 3, 2015 Author Posted June 3, 2015 ,cnf info...Variables (--variable-name=value)and boolean options {FALSE|TRUE} Value (after reading options)--------------------------------- ----------------------------------------auto-rehash TRUEauto-vertical-output FALSEcharacter-sets-dir (No default value)column-type-info FALSEcomments FALSEcompress FALSEdebug-check FALSEdebug-info FALSEdatabase (No default value)default-character-set autodelimiter ;enable-cleartext-plugin FALSEvertical FALSEforce FALSEnamed-commands FALSEignore-spaces FALSEinit-command (No default value)local-infile FALSEno-beep FALSEhost (No default value)html FALSExml FALSEline-numbers TRUEunbuffered FALSEcolumn-names TRUEsigint-ignore FALSEport 0prompt mysql> quick FALSEraw FALSEreconnect TRUEsocket (No default value)ssl FALSEssl-ca (No default value)ssl-capath (No default value)ssl-cert (No default value)ssl-cipher (No default value)ssl-key (No default value)ssl-verify-server-cert FALSEtable FALSEuser (No default value)safe-updates FALSEi-am-a-dummy FALSEconnect-timeout 0max-allowed-packet 16777216net-buffer-length 16384select-limit 1000max-join-size 1000000secure-auth FALSEshow-warnings FALSEplugin-dir (No default value)default-auth (No default value)
P15-D24 Posted September 3, 2015 Author Posted September 3, 2015 Post an update for others who may have this issue. First, I did not change any tables to Innodb. I went back to my ISP tech support and requested they do more in-depth research as to what was happening when I would start to have issues and the DB finally crashing. Their research determined apache was crashing as traffic kept growing. On June 28 I upgraded to a larger server, 6GB memory. Since that time I have had zero issues with DB. So it looks like just a hardware resources issue.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.