QUESTION 1 : How do I check the number of allowed connections?
You can set the number of connections in my.ini. For example, to set it to 500 do this:
max_connections = 500
However, keep in mind the mysqld wil dictate the variable
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?
To turn on the slow log and set the minimum slow query time to 2 seconds (default 10)
slow-query-log-file = slow-queries.log
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?
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:
QUESTION 4 : Will having a cluster setup increase performance?
QUESTION 5 : How can I setup load shedding with multiple MySQL servers in a cluster?
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