I’ve just added two categories to our forum, so you can post your job listings if you’re looking for someone to help you, and you can post your qualifications if you are available for hire.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Venkatesh

Hi,

My Current MYSQL Config as below. Could you please provide us suggestions to improve the performance.

Hardware:

32 GB – RAM
12 Core Processor
1TB – Disk

My.cnf
==========

freehold:

[client]
port=3323
socket=/tmp/mysqld_mdmp.sock
character-sets-dir=/home/mysqld/sys/5.5.8/share/mysql/charsets

[mysqld]
port=3323
server-id=1
socket=/tmp/mysqld_mdmp.sock
datadir=/home/mysqld/mdmp/data
basedir=/home/mysqld/sys/5.5.8
tmpdir=/home/mysqld/mdmp/tmp
slow_query_log=mdmp_slow_queries.log

# binary logging and replication
binlog_cache_size=1M
binlog_format=MIXED
log-slave-updates
log-bin=mdmp_freehold_binlog
sync_binlog=1
relay-log=mdmp_freehold_relaylog
max_relay_log_size=100M
#read-only

# limits
max_connections=1000
#old-passwords
#lower_case_table_names=1
general_log = ON
max_heap_table_size=128M
event_scheduler=ON
query_cache_limit = 10M
open_files_limit = 8088
back_log=128
max_allowed_packet = 50M

# performance
sort_buffer_size = 8M
read_buffer_size = 2M
query_prealloc_size = 65536
read_rnd_buffer_size = 8M
table_open_cache = 1024
thread_cache_size = 8
query_cache_size = 128M
join_buffer_size = 8M
long_query_time=300
key_buffer_size = 500M
concurrent_insert=2
myisam_sort_buffer_size = 64M

# innodb
innodb_additional_mem_pool_size=32M
innodb_data_home_dir=/home/mysqld/mdmp/data/
innodb_log_group_home_dir=/home/mysqld/mdmp/data
#innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:1073741824:a
utoextend
innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:5158993920;in
nodbspace3.dbf:17179869184;innodbspace4.dbf:17179869184;innodbspace5.dbf:1717986
9184;innodbspace6.dbf:17179869184;innodbspace7.dbf:17179869184;innodbspace8.dbf:
17179869184:autoextend
# innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:250G;/mydat
a/data2/mdmp/innodbspace3.dbf:1073741824:autoextend
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=16
innodb_buffer_pool_size = 4500M
innodb_lock_wait_timeout=60
innodb_log_buffer_size = 32M
innodb_log_file_size = 512M
innodb_open_files=2048

#Replication-skip
replicate-ignore-table=mysql.gsd_runs
replicate-ignore-table=mysql.gsd_monitor

boonton:

[client]
port=3323
socket=/tmp/mysqld_mdmp.sock
character-sets-dir=/home/mysqld/sys/5.5.8/share/mysql/charsets

[mysqld]
port=3323
server-id=2
socket=/tmp/mysqld_mdmp.sock
datadir=/home/mysqld/mdmp/data
basedir=/home/mysqld/sys/5.5.8
tmpdir=/home/mysqld/mdmp/tmp
slow_query_log=mdmp_slow_queries.log

# binary logging and replication
binlog_cache_size=1M
binlog_format=MIXED
log-slave-updates
log-bin=mdmp_boonton_binlog
sync_binlog=1
relay-log=mdmp_boonton_relaylog
max_relay_log_size=100M
read-only

# limits
max_connections=1000
#old-passwords
#lower_case_table_names=1
general_log = ON
max_heap_table_size=128M
event_scheduler=ON
query_cache_limit = 10M
open_files_limit = 8088
back_log=128
max_allowed_packet = 50M

# performance
sort_buffer_size = 8M
read_buffer_size = 2M
query_prealloc_size = 65536
read_rnd_buffer_size = 8M
table_open_cache = 1024
thread_cache_size = 8
query_cache_size = 128M
join_buffer_size = 8M
long_query_time=300
key_buffer_size = 500M
concurrent_insert=2
myisam_sort_buffer_size = 64M

# innodb
innodb_additional_mem_pool_size=32M
innodb_data_home_dir=/home/mysqld/mdmp/data/
innodb_log_group_home_dir=/home/mysqld/mdmp/data
#innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:1073741824:a
utoextend
innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:4580179968;in
nodbspace3.dbf:17179869184;innodbspace4.dbf:17179869184;innodbspace5.dbf:1717986
9184;innodbspace6.dbf:17179869184;innodbspace7.dbf:17179869184;innodbspace8.dbf:
17179869184:autoextend
# innodb_data_file_path=innodbspace1.dbf:1073741824;innodbspace2.dbf:250G;/mydat
a/data2/mdmp/innodbspace3.dbf:1073741824:autoextend
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=16
innodb_buffer_pool_size = 4500M
innodb_lock_wait_timeout=60
innodb_log_buffer_size = 32M
innodb_log_file_size = 512M
innodb_open_files=2048

#Replication-skip
replicate-ignore-table=mysql.gsd_runs
replicate-ignore-table=mysql.gsd_monitor

========

==> max_heap_table_size=128M

0