• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

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

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
fosiul01
Asked:
fosiul01
1 Solution
 
wesly_chenCommented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now