Solved

Multi threading with mysql Server version: 5.1.49-3 (Debian)

Posted on 2011-09-21
1
454 Views
Last Modified: 2012-05-12
hi
I just configured one debian myql server which will work as master for a high load database.

becasue there would be 3 instances. I configured mysqld_multi with 3 different port.

and it willb e innodb database,.

now few things i want to discuss for the performance

(1) data1, data2  ( for  data directry ) in a different "Array 10" partition
2) innodb_buffer_pool_size= 16GB for each database
3) innodb_thread_concurrency=16
 

now  :

(a)is there any disadvantages of mysqld_multi for high end database ??  ( i did not see an y thing documentation for this so i belived it will be same)
(b) is there any variables to tell mysql to use multi procesosr ?? ( although, it set to us innodb_thread_concurrency=16)

other then

query_cache_size,table_cache,tmp_table_size,thread_cache is there any thing else to increase performance of innodb database ?


i will use mysql_tuner later on .. but i looking for extra advise .. to see what i am missing ..




0
Comment
Question by:fosiul01
1 Comment
 
LVL 38

Accepted Solution

by:
wesly_chen earned 500 total points
ID: 36575913
1. innodb_buffer_pool_size 70-80% of memory is a safe bet.
2. Run those two scripts after your MySQL server run for certain time (two weeks or one month at least)
mysqltuner.pl
https://github.com/rackerhacker/MySQLTuner-perl
Tuning-primer.sh
http://genomewiki.ucsc.edu/index.php/Tuning-primer.sh

   Those two scripts will analyze your MySQL db data read/write, connection, cache hit ... and give you some suggestion. MySQL db tuning vary from data type and usage. So those parameters such as uery_cache_size,table_cache,tmp_table_size,thread_cache are based on your actual usage to adjust/tune.

3. MySQL_multi doesn't change the performance, it just allows one database daemon listen to multiple ports.

4. By default, MySQL is multi-thread which use all the available processors.

Check those articles for performance tuning:
InnoDB Performance Tuning Tips
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html

MySQL Innodb Performance Tuning For Disk-Bound Workloads On CMT Servers
http://blogs.oracle.com/luojiach/entry/mysql_innodb_performance_tuning_for

Innodb Performance Optimization Basics
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question