Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 16th March 2011, 17:11
guimnk guimnk is offline
Member
 
Join Date: Jul 2009
Posts: 90
Thanks: 7
Thanked 2 Times in 2 Posts
Default MySQL Innodb - Performance

Hi all!

I've two machines running ubuntu 10.10 x64. The machines are very fast, HP Proliant DL380 G5 with 2 processors Xeon 2.66ghz and 16GB of RAM. They have 2 RAID's 1+0 (SAS disks). In the first raid I've installed the ubuntu and the second raid is a /var/lib/mysql directory.
The replication is activated and ok, the informations are correct..
My problem is the performance. I've a changed my.cnf and I want the opnion if this configuration is correct, because I've many slow queries.
All of databases are tunned with index in all fields used in WHERE clauses. I'm using INNODB engine and only one database. The size of database is 7GB (.sql exported) and 12GB (/var/lib/mysql/ibdata1).
I've tried to run mysqltunning.pl and tuning-primer.pl (I think), but those scripts only says "upgrade your memory / change the size of innodb_buffer_size". I've changed that, but the performance is still low.

How can I do to setting up the correct changes to get more performance?

This is my my.cnf:

Code:
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld]

user = mysql
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
old_passwords=1

log-bin
binlog-do-db    = db1ERP
binlog-do-db    = openfire
binlog-ignore-db=mysql
binlog-ignore-db=test

master-host = 192.168.1.38
master-user = replication
master-password = slave2
master-port = 3306

back_log = 50
max_connections = 9000
max_connect_errors = 10
table_cache = 3072
max_allowed_packet = 16M
expire_logs_days = 2
binlog_cache_size = 1M
max_heap_table_size = 512M
sort_buffer_size = 32M
join_buffer_size = 2048M
thread_cache_size = 8
thread_concurrency = 10
query_cache_size = 1024M
query_cache_limit = 512M
ft_min_word_len = 4
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M
log-bin=mysql-bin
general_log_file = /var/log/mysql.log
log_warnings
log_short_format
server-id = 1
key_buffer_size = 1024M
read_buffer_size = 512M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_autoextend_increment = 50
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 8000M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 1000M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 240


[mysqldump]
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
Reply With Quote
Sponsored Links
  #2  
Old 17th March 2011, 18:30
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,741 Times in 2,575 Posts
Default

Have you tried to increase query_cache_size?
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #3  
Old 17th March 2011, 18:51
guimnk guimnk is offline
Member
 
Join Date: Jul 2009
Posts: 90
Thanks: 7
Thanked 2 Times in 2 Posts
Default

To 4096M ?
Reply With Quote
  #4  
Old 18th March 2011, 14:54
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,741 Times in 2,575 Posts
Default

Just try it - I can't say if it helps.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #5  
Old 18th March 2011, 15:13
guimnk guimnk is offline
Member
 
Join Date: Jul 2009
Posts: 90
Thanks: 7
Thanked 2 Times in 2 Posts
 
Default

I changed it today. I'll analyze and post here the result.

Thanks falko
Reply With Quote
The Following User Says Thank You to guimnk For This Useful Post:
falko (19th March 2011)
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CPU load locks up box. Apache or MYSQL related. crypted General 61 29th October 2010 22:16
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 13:58
Tuning MySQL Performance with MySQLTuner VMartins Server Operation 3 28th October 2008 15:18
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 12:09.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.