HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   MySQL Innodb - Performance (http://www.howtoforge.com/forums/showthread.php?t=51910)

guimnk 16th March 2011 17:11

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


falko 17th March 2011 18:30

Have you tried to increase query_cache_size?

guimnk 17th March 2011 18:51

To 4096M ?

falko 18th March 2011 14:54

Just try it - I can't say if it helps.

guimnk 18th March 2011 15:13

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

Thanks falko


All times are GMT +2. The time now is 07:36.

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