Solved

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

Posted on 2011-09-21
1
456 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

680 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