Solved

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

Posted on 2011-09-21
1
458 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

734 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