Performance Innodb my.cnf

Hi guys, first thanks for all help, this list is amazing.

Well i have a dedicated server on Fedora 11 x64, its have 12GB ram and
a SCSI 15k rpm on datadir.

I need a improve on my mysql conf to that my software run better, its
my.cnf HOW i Can improve this :D

Thanks !!

HERE:

[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock

# *** Application-specific options follow here ***

#
# The MySQL server
#
[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-external-locking
datadir = /disk3/mysql
net_buffer_length = 1024K
join_buffer_size = 1M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
table_cache = 500
max_allowed_packet = 16M

max_connections=30
max_user_connections=200

key_buffer = 1000M
key_buffer_size = 1000M
#thread_cache = 400
thread_stack = 128K
thread_cache_size = 1024
thread_concurrency = 8
#thread_stack = 128K

default-character-set = utf8
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size= 11000M
innodb_additional_mem_pool_size=10M
innodb_log_file_size= 256M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=32
innodb_file_per_table
innodb_table_locks=0

query_alloc_block_size = 16k

query_cache_limit = 512M
query_cache_size = 512M
query_cache_type = 1

long_query_time = 3
table_cache = 800
#innodb_force_recovery = 3
table_definition_cache = 800
query_cache_min_res_unit = 5K
delay-key-write=OFF
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_support_xa = false
innodb_io_capacity = 10000
innodb_max_dirty_pages_pct = 90

concurrent_insert = 2

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performance Innodb my.cnf

--00504502cc1859f559047d6cf64e
Content-Type: text/plain; charset=ISO-8859-1

Hi Ortis,
How abt the hits or load i.e ( DML, DDL ) to the server.
My initial assessment after looking at you cnf file is
1) Calculate and place an appropriate value for innodb_buffer_pool_size
2) Reduse the innodb_thread_concurrency to 4 or 8.

and how about the no. of tables in the database and the table type.



On Sun, Jan 17, 2010 at 3:03 AM, Junior Ortis wrote:

> Hi guys, first thanks for all help, this list is amazing.
>
> Well i have a dedicated server on Fedora 11 x64, its have 12GB ram and
> a SCSI 15k rpm on datadir.
>
> I need a improve on my mysql conf to that my software run better, its
> my.cnf HOW i Can improve this :D
>
> Thanks !!
>
> HERE:
>
> [client]
> #password = [your_password]
> port = 3306
> socket = /tmp/mysql.sock
>
> # *** Application-specific options follow here ***
>
> #
> # The MySQL server
> #
> [mysqld]
> # generic configuration options
> port = 3306
> socket = /tmp/mysql.sock
> skip-locking
> skip-external-locking
> datadir = /disk3/mysql
> net_buffer_length = 1024K
> join_buffer_size = 1M
> sort_buffer_size = 4M
> read_buffer_size = 4M
> read_rnd_buffer_size = 4M
> table_cache = 500
> max_allowed_packet = 16M
>
> max_connections=30
> max_user_connections=200
>
> key_buffer = 1000M
> key_buffer_size = 1000M
> #thread_cache = 400
> thread_stack = 128K
> thread_cache_size = 1024
> thread_concurrency = 8
> #thread_stack = 128K
>
> default-character-set = utf8
> innodb_flush_method=O_DIRECT
> innodb_buffer_pool_size= 11000M
> innodb_additional_mem_pool_size=10M
> innodb_log_file_size= 256M
> innodb_log_buffer_size=4M
> innodb_flush_log_at_trx_commit=0
> innodb_thread_concurrency=32
> innodb_file_per_table
> innodb_table_locks=0
>
> query_alloc_block_size = 16k
>
> query_cache_limit = 512M
> query_cache_size = 512M
> query_cache_type = 1
>
> long_query_time = 3
> table_cache = 800
> #innodb_force_recovery = 3
> table_definition_cache = 800
> query_cache_min_res_unit = 5K
> delay-key-write=OFF
> innodb_read_io_threads = 16
> innodb_write_io_threads = 16
> innodb_support_xa = false
> innodb_io_capacity = 10000
> innodb_max_dirty_pages_pct = 90
>
> concurrent_insert = 2
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=sureshkumarilu@gmail.com
>
>


--
Thanks
Suresh Kuna
MySQL DBA

--00504502cc1859f559047d6cf64e--