MySQL Server Performance

Question

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

1. How do I check the number of allowed connections?

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

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

4. Will having a cluster setup increase performance?

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

Thanks

Answer 1

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'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