MySQL Server Performance I have a few questions regarding MySQL server running under Windows Server 2008 R2:


How do I check the number of allowed connections?
How do I turn on query logging to see performance of queries executed?
As I understand MySQL is multithreaded, will increasing the number of allowed connections increase performance on a Quad Core system?
Will having a cluster setup increase performance?
How can I setup load shedding with multiple MySQL servers in a cluster?


Thanks
QUESTION 1 : How do I check the number of allowed connections?

ANSWER

You can set the number of connections in my.ini. For example, to set it to 500 do this:

[mysqld]
max_connections = 500


However, keep in mind the mysqld wil dictate the variable open_files_limit

Run this query : SHOW VARIABLES LIKE 'open_files_limit';

You can raise this, BUT AT YOUR OWN RISK !!! Also, keep in mind that the number of max_connections will multiply the sum of per-connection parameters (sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size)

QUESTION 2 : How do I turn on query logging to see performance of queries executed?

ANSWER

To turn on the slow log and set the minimum slow query time to 2 seconds (default 10)

[mysqld]
slow-query-log
slow-query-log-file = slow-queries.log
long-query_time=2


This should place the slow-queries.log file in the same location as datadir. You can see where datadir is set by running SHOW VARIABLES LIKE 'datadir';

QUESTION 3 : As I understand MySQL is multithreaded, will increasing the number of allowed connections increase performance on a Quad Core system?

ANSWER

This answer depends on the storage engine of all your tables.

If you are using MyISAM, there is nothing to configure because the MyISAM storage engine does not utilize multiple CPUs.

If you are using InnoDB


MySQL 5.5 can be configured to utilize multiple CPUs
MySQL 5.1 InnoDb Plugin (MySQL 5.1.38+) can be configured to utilize multiple CPUs
MySQL 5.1.37 and prior does not utilize multiple CPUs


In order to configure InnoDB for multicore engagement, please read my past posts on this subject:


About single threaded versus multithreaded databases performance
Possible to make MySQL use more than one core?
Multi cores and MySQL Performance
MySQL Config for Large MyISAM tables
MariaDB XtraDB Performance Tuning
Insert-heavy InnoDB table won&#39;t use all my CPU


QUESTION 4 : Will having a cluster setup increase performance?

QUESTION 5 : How can I setup load shedding with multiple MySQL servers in a cluster?

ANSWERS

It can, but you must do two things


Configure a Replication Topology to have one Write Master and Multiple Read Slaves
Do the leg work of coding your app to access the Cluster as follows


Do all INSERTs, UPDATEs, DELETEs on the Write Master
Do all SELECTs on Read Slaves using some Network-Based Load Balancing