AutoItScript

Yeah that's one of them. So you are looking for indexes with varchars in them. In the case above if you "drop index auth_user on blogs_blogs" the support tool should recreate it with the right values. In my case I actually dropped all the indexes on problem tables and let the tool recreate them all. Far quicker than inspecting each one. You don't want to be ignored things on indexes, you want them nuked and created properly.

Trust me VARCHAR(255) is correct for the column. But it won't "take" until there are no more varchar indexes. I need to see the structure of your indexes so we can see which other ones we need to drop as well. Let me see if there is a mysql command to show them because your phpmyadmin is not showing them. show indexes from blogs_blogs;

DROP INDEX blog_authorized_users ON blogs_blogs; DROP INDEX blog_authorized_users_2 ON blogs_blogs; DROP INDEX blog_authorized_users_3 ON blogs_blogs; Then the support tool should work.

I don't have blogs. But you are still looking at the wrong place, double check the arrows on the screenshot. Scroll right down to the bottom of the page you showed and click in Indexes. You almost certainly need to drop the index for blog_authorized_users.

This is what it looks like for cms_databases. Table -> Structure -> Indexes

It's ok for the column to be VARCHAR(255) it's the index that needs to be shorter. In my cms_databases the column database_key is VARCHAR(255) but the index for database_key is (191) (4 x 191 = 764 which is close to the max index length under Innodb). You should be able to drop the index and the support tool should give you a new command to recreate the index that uses the right value. You might need to drop the index and change to utb8mb4 with the commands you have above before recreating the index. i.e. converting the columns while the index is not present.

It won't work. You'll still come across the index too long issue as I keep saying... Well you're not going to be happy until you get a step by step so try this. At your own large risk! Backup your board.Take the board offlineBackup your board!!!!Add/Change conf_global.php to include 'sql_utf8mb4' = trueIn the ACP goto Support -> Something is not workingIt will spit out hundreds of MySql commands, copy them to a text file (there's no chance of them working from Fix Automatically unless your board is small)Run the commands in phpmyadmin one by one or in small batches. Make a note of the tables that give errors about indexes being too longFor the tables with index-too-long error: Goto the table / Structure tab.Scroll down and click on "+indexes"Delete all but the primary indexesRerun the Support tool in the ACP, it should give you the correct command to recreate the index with the correct length

Did you select the table, click Operations, and then change collation that way? If so, you've not changed the collation of all the columns in the table. That's where the fun is If you use the structure tab to show the columns, you should see the collation there too in tables that contain text fields.

Unfortunately it's not that simple. Some of the indexes are created with a length based on 3 bytes per char in mind. So when you try and change the collation on those tables you get an error about indexes being too large. To fix it you have to completely delete the indexes on that table, recreate them with a length suitable for 4 bytes per char. Then you can change the collation. I did it on my board by deleting all the indexes and then changing the collation type in conf_global.php. Then when you run the support tool it spits out the commands for recreating the indexes - even with the correct index lengths for utf8mb4. Not for the faint hearted, it took about 5 hours to recreate. Info about the index issue: https://mathiasbynens.be/notes/mysql-utf8mb4

Looks like you've got two installs there. One with a ibf_ prefix.

Which is kind of odd as utf8mb4 is the default if you install a fresh copy of IPS4 - it's clearly the way forward. I've converted my DB to utf8mb4 yesterday and it was a real pain to get everything right.

Yeah, that was my point. No point listening to people who just tell you numbers based on your server's memory. You need to monitor what the load is and then decide what to do based on what free memory you have. I just saw an old post on here where the answer to "I have 8GB of RAM" was to set key_buffer to 2GB. Heh :)

Background: I'm running CentOS 5.3 (linux) with mysql 5.0.58. My IPB site has around 500 users online and 700,000 posts. I've spent the best part of a week learning about tuning mysql instead of blindly using my-huge.cnf, or randomly plugging in values. I've written my findings here as there appears to be a load of anecdotes and misinformation out there on this topic. I'm still a newbie though so if you find that something I've said it utterly wrong then let me know! For the purposes of this guide I'll assume you have a simple dedicated server with web server and mysql on the same box and the main database being run by mysql is IPB. That said, I think most of the concepts are applicable to other systems. Ok, so when tweaking my.cnf the first "easy win" is to turn on query caching (which is off by default) with these lines: query_cache_type=1 query_cache_limit=1M query_cache_size=32M This just turns caching on, uses 32M for the cache, and allows the biggest single entry to be 1M. You can adjust this later when we monitor the server (below) but these values are pretty good for most. The next most important settings are these: key_buffer - used for caching table indexes. sort_buffer_size - used when performing sorts read_rnd_buffer_size - used after a sort for ORDERBY operations read_buffer_size - used for table scans I've read loads of articles where "general wisdom" is given for these. But after reading all of them I've come to the conclusion that they are all talking rubbish. The best method is to monitor what's going on and then tweak. Download these two scripts, tuning-primer.sh and mysqltuner.pl: wget http://www.day32.com/MySQL/tuning-primer.sh wget http://mysqltuner.com/mysqltuner.pl chmod 700 tuning-primer.sh chmod 700 mysqltuner.pl These scripts will give you easy access to some monitoring statistics and even try to give advice. Even if you ignore the advice they are very useful to give easy access to stats like total memory that could be used by mysql, cache hit rates, etc. Both give slightly different output so I've been using both as required. key_buffer (or key_buffer_size) - (Global) This is a global value - it is shared between all databases. There are two widely accepted comments about how to set this: 1. Set between 25% and 50% of RAM 2. Try and set as large as the total of all your myISAM index files (*.MYI files) (mysqltuner.pl shows this size this neatly) Option 1 - Setting as an amount of RAM is crazy. My board has 380M of .MYI files setting key_buffer larger than this does NOTHING apart from totally and utterly waste the RAM. What about Option 2 - Setting key_buffer large enough to contain all the .MYI files? This is a bit better but even still it probably won't ever use the entire amount unless every single index is read. And (this applies to the other memory settings as well) mysql is only caching index lookups, when the actual data is read from disk it is the OS that will be doing the majority of the caching. The more memory you needlessly waste in mysql the worse the OS caching will perform if you get low on RAM. So the best thing to do is to montior the key_buffer hit rate and aim for a 99% ratio (that is 99% of index lookups are done from RAM). After your server has been running for a while (the tools suggest 2 days, but you can do that when fine tuning). Run mysqltuner.pl and see what the key buffer ratio is. My server has 380M of index files and 2GB of RAM so conventional wisdom is to set it to either 512M-1024M (option 1) or 380M (option 2). Well, here is the mysqltuner.pl excerpt from my server: [OK] Key buffer size / total MyISAM indexes: 64.0M/379.7M [OK] Key buffer hit rate: 99.3% What the heck?! I'm only using a 64M key_buffer and yet I have a 99% hit rate! Is it worth increasing it to 390M just to get 99.9%? Maybe if you have more RAM than sense - I've only got a 2GB server so that extra RAM is much better utilized by the OS for general caching. My recommendation would be to start the key_buffer low, monitor it and increase it by a small amount each time until you hit a 99% hit rate. Conventional wisdom size: 512M My server size: 64M sort_buffer_size - (Per-connection) This is allocated per-connection - if you have 100 connections then you use 100 * sort_buffer_size As with key_buffer there is lots of advice on a "allocate 1M per GB of RAM" advice. Again I would ignore that and instead just monitor how the buffer is used. On my server I started with 1M and then upped it by 1M at a time until 99% of sorts where done in RAM and only 1% needed a temporary table. On my server that required a sort_buffer_size of 4M. Here's the mysqltuner.pl output: Sorts requiring temporary tables: 1% For my 2GB server the "wisdom" setting of 2M setting was too small and I had about 15% of sorts needing to use temporary tables. Conventional wisdom size: 2M My server size: 4M read_buffer_size - (Per-connection) This is allocated per-connection - if you have 100 connections then you use 100 * read_buffer_size This is used to buffer table scans. In theory as long as threads are cached (see below) then bigger is better, I've seen values from 2M to 16M suggested in articles and our old friend "1M per GB of RAM" is the most common. That's probably ok UNLESS YOU ARE RUNNING LINUX. Yep, it's bugged. In most versions of mysql 5 (and certainly in my server version of 5.0.58) any any read_buffer_size above 256K will actually make performance worse! The bug report (and repo steps) can be found at http://bugs.mysql.com/bug.php?id=44723 - I followed the steps and on my server a 2M buffer was TWICE as slow as a 256K buffer. The more I increased the buffer the worse it got. I highly recommend running the repro steps on your linux server to see if you are affected before going above 256K Conventional wisdom size: 2M My server size: 256K read_rnd_buffer_size - (Per-connection) This is allocated per-connection - if you have 100 connections then you use 100 * read_rnd_buffer_size This is used after a sort operation for certain queries like ORDER_BY. It's used quite a bit by IPB but I couldn't really detect any performance differences for values greater than 256K Conventional wisdom size: 2M My server size: 256K Those are some of the most "controversial" values. As said before, it's better to use buffers that are correctly sized rather than just using a percentage of RAM and leave as much memory as possible for the OS to do general caching. Per connection memory use is: sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size (join_buffer_size defaults to 128K and I've left it low as IPB doesn't use queries that require it). Ensure that your max_connections value actually doesn't use more RAM than you actually have! mysqltuner.pl shows this information neatly: [--] Total buffers: 178.0M global + 4.9M per thread (50 max threads) [OK] Maximum possible memory usage: 421.8M (21% of installed RAM) The "global" value is essentially key_buffer + tmp_table_size. tmp_table_size - (Global) This is a global value - it is shared between all databases. When mysql needs to create temp tables this value alters whether the table is created in RAM or on disk. First, you must set max_heap_table_size and tmp_table_size to the same value as the lower of the two is used. The defaults are 16M and 32M respectively which essentially means that the default tmp_table_size is 16M. I currently have mine set to 32M and even when I set it to 512M the number of tmp tables created on disk is the same as when the value was 32M. Further research shows that TEXT and BLOB tables cannot be created in RAM. If you are running IPB only you are probably in the same position and won't see benefit above 32M either. So, what are the differences between the conventional wisdom sizing and my "just right" sizing for my server for 100 connections? Wisdom: 512M + 32M + (100 * (2M + 2M + 2M + 128K)) = 1156M My Server tweaked: 64M + 32M + (100 * (4M + 256K + 256K + 128K)) = 558M Quite a saving... half a gig extra for the OS to use for other things :) Ok, some more important variables thread_cache_size - during normal operation you don't really want to be creating threads (expensive memory allocations especially with large buffers), use mysqltuner.pl to monitor and tune table_cache - table open operations that have been cached. use mysqltuner.pl to monitor and tune. For a single IPB forum my value was 500 I also found some advice on a vBulletin for improving mysql searching which I'm still testing and researching (http://dev.mysql.com/doc/refman/5.0/en/midpoint-insertion.html): low_priority_updates=1 concurrent_insert=2 key_cache_division_limit=40 Ok, that's it I hope it's been useful. Please feedback on anything that is completely wrong - I'm still learning :) Oh, and here is my my.cnf for reference for my IPB. [mysqld] set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 skip-networking skip-locking skip-bdb query_cache_type=1 query_cache_limit=1M query_cache_size=32M # Set to combined size of all .MYI files, or between .25 and .5 of total RAM # Turns out this is nonsense, just monitor the key buffers and go for 99% hit rate key_buffer=64M # The following are created for each connection, so are important for sizing # per thread/connection memory = sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size # Defaults: # sort_buffer_size=1M # read_buffer_size=128K # read_rnd_buffer_size=256K # join_buffer_size=128K # # If running on linux there is a bug that anything over read_buffer_size=256K will actually # harm performance. See http://bugs.mysql.com/bug.php?id=44723. # I've verified on CentOS 5.3 with mysql 5.0.58 sort_buffer_size=4M read_buffer_size=256K read_rnd_buffer_size=256K join_buffer_size=128K # Ensure you only use the number of connections you can support in memory (see above for per thread memory): # Memory used = key_buffer + tmp_table_size + ((per thread mem)*max_connections) max_connections=50 # Timeouts, set to not let idle connections - the defaults are huge interactive_timeout=90 wait_timeout=90 connect_timeout=10 # Check opened tables and adjust accordingly after running for a while. open_files_limit=1500 table_cache=500 # Check created_tmp_disk_tables, also needs heap table size as uses the smallest value # Have tried 512M for IPB but still same number of temp tables, might as well keep small tmp_table_size=32M max_heap_table_size=32M # thread_concurrency = 2 * (no. of CPU) - BUT only works on solaris so don't need to bother thread_concurrency=4 thread_cache_size=8 # Stuff that can help with mysql searches # For key_cache_division_limit see http://dev.mysql.com/doc/refman/5.0/en/midpoint-insertion.html low_priority_updates=1 concurrent_insert=2 key_cache_division_limit=40 # Log slow queries log_slow_queries=/var/log/mysqld.slow.log long_query_time=5 expire_logs_days=7