MySQL Installation Configuration Recommendations

[ewm]

Hello,

We are setting up IPB on a self-hosted server and I was wondering if anyone could explain a few of the configuration options I see when I go through the MySQL server instance configuration wizard (it's being setup on a Windows server).

[*]Database Usage: Multifuncational Database, Transactional Database Only, Non-Transactional Database Only - in your experience, which is the best choice for an IPB installation? [*]Concurrent Connections: Decision Support (DSS)/OLAP, Online Transaction Processing (OLTP), Manual Setting - is it necessary to set a high level of concurrent connections, or would 20 be sufficient? Our board would not be extremely high-traffic at the moment (though like everyone else, we're hoping to grow). [*]Enable TCP/IP Networking: Is this necessary for IPB to function if the application is running on the same machine as the database? I've also heard some chatter about the differences between InnoDB and MyISAM. It seems like they're both suited for different tasks and either will work well depending on the job at hand, but does one tend to work better with IPB than the other? Thanks for any advice or pointers you may have for setting this up on a Windows-based system. I have very limited experience with MySQL, so I'm unfamiliar with these settings and, more importantly, how they may impact our IPB installation. Thanks!

1. 
   
   
   
   
   
   
   
   

[AlexJ]

@1. Use multi functional. That's what I use on my stats server because I assume you will be using MyISAM mainly but it also depends on your DB size and usage. Many here use InnoDB for couple of tables.

Multifunctional Database: This option enables both the InnoDB and MyISAM storage engines and divides resources evenly between the two. This option is recommended for users who use both storage engines on a regular basis.

Transactional Database Only: This option enables both the InnoDB and MyISAM storage engines, but dedicates most server resources to the InnoDB storage engine. This option is recommended for users who use InnoDB almost exclusively and make only minimal use of MyISAM.

Non-Transactional Database Only: This option disables the InnoDB storage engine completely and dedicates all server resources to the MyISAM storage engine. This option is recommended for users who do not use InnoDB.

@2. http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard-connections.html

Only you can choose that one because only you know your MySQL max connections on peak time. I normally get around 10-15 at max. So 20 will be fine for me but not enough for you if you are going to have 500+ users or so.

@3. Disable. I assume you are not going to remote connect to your DB.

By default the MySQL server will allow connections via TCP/IP from any host (but may reject a connection based on the user's remote hostname/IP address). In many cases TCP/IP connectivity is not required and can be disabled to prevent remote access to the MySQL server. If you are using MySQL locally for development or for use with a web server, you should disable TCP/IP networking.

To disable TCP/IP networking, choose the Detailed Configuration option during installation, and uncheck the Enable TCP/IP Networking option (you can re-configure a server by starting the MySQL Configuration Wizard located at Start > Programs > MySQL > MySQL Server 4.1 > MySQL Server Instance Config Wizard).

Users of older versions of MySQL can add the following lines to the [mysqld] section of your MySQL server configuration file:

skip-networking

enable-named-pipes

This will disable TCP/IP connections and enable named pipes. For this to work you must install MySQL on an NT-based Windows operating system, and use the mysqld-nt.exe server binary. The location of your configuration file will vary depending on the version of MySQL you have installed, look for one of the following files:

C:\my.cnf

C:\Windows\my.ini

[andavis]

Have a look at this: http://www.cpanel.net/videos/mysql-optimization/