Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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:

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
share|improve this answer
    
Thanks for the brilliant answer –  Armand May 14 '12 at 7:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.