AutoItScript

I'd agree. Personally I kinda prefer a light theme. But seriously, if its as easy as they say "just edit the css" then IPS should get an intern on that job every new release and make it in-box so we all have a template to build from. It's awful trying to do it from scratch at the moment - all the relevant colours aren't even in the theme editor. Hello, ".ipsBox" I'm looking at you!

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 offline Backup your board!!!! Add/Change conf_global.php to include 'sql_utf8mb4' = true In the ACP goto Support -> Something is not working It 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 long For the tables with index-too-long error:
Goto the table / Structure tab. Scroll down and click on "+indexes" Delete all but the primary indexes Rerun the Support tool in the ACP, it should give you the correct command to recreate the index with the correct length

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 
 

Arrgh the marketplace is driving me crazy, how hard can it be to upload a zip file 
Edit: Should be fixed now...

Hi,
Outstanding plugin, THANK YOU! 
Would it be possible, for the regex verification, to be able to mark as a spammer, instead of putting the content under moderation review?
That would be amazing!
Thanks again,

I'm still getting a fair bit of Korean spam which is being created by users who aren't on any of IPS/StopForumSpam/http:BL lists. I'm testing a feature where a regular expression can be applied to post titles and content. If it matches the post is automatically flagged for moderator approval. This means that the forum will still look clean for users and moderators can then clean it up easily with the Flag As Spammer tool. I'm testing the changes on my forum at the moment - I'll update the plugin on Marketplace after testing looks ok.

I've not uploaded the new version yet, currently testing it on my 4.1.7 live site and it's looking ok.

Added option to check posts with the Akismet service and auto-moderate if matched I've got visible spam down to 0 this week on my forum. Down from about a maximum of 90 posts a day before I started on the plugin  
 

Spam Happy is an automatic IPS rank I think, I've not got around to changing it yet
No point not being honest about the functionality - I'm not trying to sell anything. I'm not interested at all in writing plugins/code for IPS 4 but I'm just finding it a necessity at the moment to make things work how I/my community want. And I'm geeky enough to enjoy the challenge so it's not all bad  
 We've had up to 40 of these korean spammers per day each creating up to 10 topics each. It looks terrible until the moderators clean up. So I'm trying all kinds of things to balance ease-of-registration but preventing spam.
Here's a screenshot of the new feature as it stands:

The php regex I'm testing at the moment is '~\p{Hangul}|\p{Han}|\p{Cyrillic}~u' which flags any Russian/Chinese/Korean. At that point the user has already registered so I'm then flagging the matching topic/post and switching it to moderator approval and the moderators receive a notification. So users never even see it. If the post is legitimate then it can just be approved. Otherwise I'm clicking Flag As Spammer which deletes all the content and bans the users (that's how I have it setup).
Here is a screenshot of my forum right now after I've had it running for a while.

 

I've reworked the plugin (the auto moderation parts) based on some new API features in 4.1.6 -it makes the code much neater and hopefully fixes any problems. I'm upgrading my live site this weekend and if all goes well I'll release the new version then. It will require 4.1.6+ though.

I've reworked the plugin (the auto moderation parts) based on some new API features in 4.1.6 -it makes the code much neater and hopefully fixes any problems. I'm upgrading my live site this weekend and if all goes well I'll release the new version then. It will require 4.1.6+ though.

Added option to check posts with the Akismet service and auto-moderate if matched I've got visible spam down to 0 this week on my forum. Down from about a maximum of 90 posts a day before I started on the plugin  
 

The IPS service (or StopForumSpam/http:bl) seems to be missing 99% of these spammers. If you do a google for korean spammers you'll see that they've been a plague this year with many big forums have 10/20 pages of spam on the front pages. They seems to be highliy sophisticated and using new accounts/IPs all the time. When I click Flag As Spammer it will tell the IPS service about this particular email address so someone on a less busy board may benefit.

Spam Happy is an automatic IPS rank I think, I've not got around to changing it yet
No point not being honest about the functionality - I'm not trying to sell anything. I'm not interested at all in writing plugins/code for IPS 4 but I'm just finding it a necessity at the moment to make things work how I/my community want. And I'm geeky enough to enjoy the challenge so it's not all bad  
 We've had up to 40 of these korean spammers per day each creating up to 10 topics each. It looks terrible until the moderators clean up. So I'm trying all kinds of things to balance ease-of-registration but preventing spam.
Here's a screenshot of the new feature as it stands:

The php regex I'm testing at the moment is '~\p{Hangul}|\p{Han}|\p{Cyrillic}~u' which flags any Russian/Chinese/Korean. At that point the user has already registered so I'm then flagging the matching topic/post and switching it to moderator approval and the moderators receive a notification. So users never even see it. If the post is legitimate then it can just be approved. Otherwise I'm clicking Flag As Spammer which deletes all the content and bans the users (that's how I have it setup).
Here is a screenshot of my forum right now after I've had it running for a while.

 

That worked, thank you.

New version uploaded that should fix this now.

I made the move to UTFMB4 while I was still on 3.4.7. I just had 4 or 5 columns left that were giving me issues which I've now resolved with @AutoItScript's help. At least it's all done now and I don't have to worry about it in future.

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.

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 offline Backup your board!!!! Add/Change conf_global.php to include 'sql_utf8mb4' = true In the ACP goto Support -> Something is not working It 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 long For the tables with index-too-long error:
Goto the table / Structure tab. Scroll down and click on "+indexes" Delete all but the primary indexes Rerun the Support tool in the ACP, it should give you the correct command to recreate the index with the correct length

Stop spreading these false information. The UTF8 type is a user-choice during installation or upgrade. There is no “automatic” conversion, because that wouldn’t even work, e.g. when your database runs on MySQL 5.1. In addition, your are not “stuck” with UTF8. Its YOUR MySQL database on YOUR server. You can do whatever you want with it. If you are not able to, maybe you should better move to IPS’ clould solution. But don’t expect IPS to become your server admin. 

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 offline Backup your board!!!! Add/Change conf_global.php to include 'sql_utf8mb4' = true In the ACP goto Support -> Something is not working It 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 long For the tables with index-too-long error:
Goto the table / Structure tab. Scroll down and click on "+indexes" Delete all but the primary indexes Rerun 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 
 

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

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