InnoDB - 16GB Data

Hi Guys i have a dedicated mysql-server and neeed tips and sugestion
to optimize its to a better performance.

1-) Here i have results from mysqltunner

>> MySQLTuner 1.0.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: toscaoSo
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.4.3-beta-community
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 458M (Tables: 349)
[--] Data in InnoDB tables: 15G (Tables: 73)
[!!] Total fragmented tables: 47

-------- Performance Metrics -------------------------------------------------
[--] Up for: 29d 12h 8m 1s (334M q [131.330 qps], 153K conn, TX: 194B, RX: 77B)
[--] Reads / Writes: 31% / 69%
[--] Total buffers: 15.5G global + 16.2M per thread (50 max threads)
[OK] Maximum possible memory usage: 16.3G (69% of installed RAM)
[OK] Slow queries: 0% (386/334M)
[OK] Highest usage of available connections: 46% (23/50)
[OK] Key buffer size / total MyISAM indexes: 300.0M/87.3M
[OK] Key buffer hit rate: 100.0% (78M cached / 22K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4M sorts)
[OK] Temporary tables created on disk: 0% (548 on disk / 1M total)
[OK] Thread cache hit rate: 99% (23 created / 153K connections)
[OK] Table cache hit rate: 44% (467 open / 1K opened)
[OK] Open file limit used: 1% (684/65K)
[OK] Table locks acquired immediately: 99% (320M immediate / 320M locks)
[!!] InnoDB data size / buffer pool: 15.5G/15.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_size (>= 8M)
innodb_buffer_pool_size (>= 15G)



2-) And here is my dedicate server i have (24GB ):


1 [
0.0%] Tasks: 71 total, 2 running
2 [|||||||
7.8%] Load average: 0.11 0.18 0.19
3 [|
0.7%] Uptime: 62 days, 19:24:09
4 [|
0.7%]
Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||| |||||||||16878/24165MB]
Swp[|
0/5122MB]


3-) And my.cnf

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

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

#
# The MySQL server
#
[mysqld]
#large-pages

# generic configuration options
port = 3306
socket = /tmp/mysql.sock
skip-locking
skip-external-locking
datadir = /disk3/Datareal/oficial/mysql
net_buffer_length = 1024K
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
table_cache = 1000
max_allowed_packet = 160M

max_connections=50
max_user_connections=200

key_buffer = 300M
key_buffer_size = 300M
#thread_cache = 400
thread_stack = 192K
thread_cache_size = 96
thread_concurrency = 8
#thread_stack = 128K

default-character-set = utf8
innodb_flush_method=O_DSYNC
innodb_buffer_pool_size= 15G
innodb_additional_mem_pool_size=128M
innodb_log_file_size= 256M
innodb_log_buffer_size=72M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=8
innodb_file_per_table=1
innodb_log_files_in_group=2
innodb_table_locks=0
innodb_lock_wait_timeout = 50

"~/.my.cnf" 72L, 1570C



Thanks guys for any tips/suggestion !

--
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: InnoDB - 16GB Data

On Sat, Apr 10, 2010 at 12:10 AM, Junior Ortis wrote:
> Hi Guys i have a dedicated mysql-server and neeed tips and sugestion
> to optimize its to a better performance.
>
> 1-) Here i have results from mysqltunner
>
>>> =A0MySQLTuner 1.0.1 - Major Hayden
> =A0>> =A0Bug reports, feature requests, and downloads at http://mysqltune=
r.com/
> =A0>> =A0Run with '--help' for additional options and output filtering
> Please enter your MySQL administrative login: toscaoSo
> Please enter your MySQL administrative password:
>
> -------- General Statistics ---------------------------------------------=
-----
> [--] Skipped version check for MySQLTuner script
> [OK] Currently running supported MySQL version 5.4.3-beta-community
> [OK] Operating on 64-bit architecture
>
> -------- Storage Engine Statistics --------------------------------------=
-----
> [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
> [--] Data in MyISAM tables: 458M (Tables: 349)
> [--] Data in InnoDB tables: 15G (Tables: 73)
> [!!] Total fragmented tables: 47
>
> -------- Performance Metrics --------------------------------------------=
-----
> [--] Up for: 29d 12h 8m 1s (334M q [131.330 qps], 153K conn, TX: 194B, RX=
: 77B)
> [--] Reads / Writes: 31% / 69%
> [--] Total buffers: 15.5G global + 16.2M per thread (50 max threads)
> [OK] Maximum possible memory usage: 16.3G (69% of installed RAM)
> [OK] Slow queries: 0% (386/334M)
> [OK] Highest usage of available connections: 46% (23/50)
> [OK] Key buffer size / total MyISAM indexes: 300.0M/87.3M
> [OK] Key buffer hit rate: 100.0% (78M cached / 22K reads)
> [!!] Query cache is disabled
> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4M sorts)
> [OK] Temporary tables created on disk: 0% (548 on disk / 1M total)
> [OK] Thread cache hit rate: 99% (23 created / 153K connections)
> [OK] Table cache hit rate: 44% (467 open / 1K opened)
> [OK] Open file limit used: 1% (684/65K)
> [OK] Table locks acquired immediately: 99% (320M immediate / 320M locks)
> [!!] InnoDB data size / buffer pool: 15.5G/15.0G
>
> -------- Recommendations ------------------------------------------------=
-----
> General recommendations:
> =A0 =A0Run OPTIMIZE TABLE to defragment tables for better performance
> =A0 =A0Enable the slow query log to troubleshoot bad queries
> Variables to adjust:
> =A0 =A0query_cache_size (>=3D 8M)
> =A0 =A0innodb_buffer_pool_size (>=3D 15G)
>
>
>
> 2-) And here is my dedicate server i have (24GB ):
>
>
> =A01 =A0[
> =A0 =A0 =A0 =A0 0.0%] =A0 =A0 Tasks: 71 total, 2 running
> =A02 =A0[|||||||
> =A0 =A0 =A0 =A0 7.8%] =A0 =A0 Load average: 0.11 0.18 0.19
> =A03 =A0[|
> =A0 =A0 =A0 =A0 0.7%] =A0 =A0 Uptime: 62 days, 19:24:09
> =A04 =A0[|
> =A0 =A0 =A0 =A0 0.7%]
> =A0Mem[||||||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||1=
6878/24165MB]
> =A0Swp[|
> =A0 =A0 0/5122MB]
>
>
> 3-) And my.cnf
>
> vim .my.cnf
> [client]
> #password =A0 =A0 =A0 =3D [your_password]
> port =A0 =A0 =A0 =A0 =A0  = 3306
> socket =A0 =A0 =A0 =A0  = /tmp/mysql.sock
>
> # *** Application-specific options follow here ***
>
> #
> # The MySQL server
> #
> [mysqld]
> #large-pages
>
> # generic configuration options
> port =A0 =A0 =A0 =A0 =A0  = 3306
> socket =A0 =A0 =A0 =A0  = /tmp/mysql.sock
> skip-locking
> skip-external-locking
> datadir =3D /disk3/Datareal/oficial/mysql
> net_buffer_length =A0 =A0 =A0 =3D 1024K
> join_buffer_size =A0 =A0 =A0  = 4M
> sort_buffer_size =A0 =A0 =A0  = 4M
> read_buffer_size =A0 =A0 =A0  = 4M
> read_rnd_buffer_size =A0  = 4M
> table_cache =A0 =A0 =A0 =A0 =A0 =A0 =3D 1000
> max_allowed_packet =A0 =A0  = 160M
>
> max_connections=3D50
> max_user_connections=3D200
>
> key_buffer =A0 =A0 =A0 =A0 =A0 =A0  = 300M
> key_buffer_size =A0 =A0 =A0 =A0 =3D 300M
> #thread_cache =A0 =A0 =A0 =A0 =A0 =3D 400
> thread_stack =A0 =A0 =A0 =A0 =A0  = 192K
> thread_cache_size =A0 =A0 =A0 =3D 96
> thread_concurrency =A0 =A0  = 8
> #thread_stack =A0 =A0 =A0 =A0 =A0 =3D 128K
>
> default-character-set =A0 =3D utf8
> innodb_flush_method=3DO_DSYNC
> innodb_buffer_pool_size=3D 15G
> innodb_additional_mem_pool_size=3D128M
> innodb_log_file_size=3D 256M
> innodb_log_buffer_size=3D72M
> innodb_flush_log_at_trx_commit=3D0
> innodb_thread_concurrency=3D8
> innodb_file_per_table=3D1
> innodb_log_files_in_group=3D2
> innodb_table_locks=3D0
> innodb_lock_wait_timeout =3D 50
>
> "~/.my.cnf" 72L, 1570C
>
>
>
> Thanks guys for any tips/suggestion !
>

First, most performance comes from optimized table structures, index,
and queries. Server tuning will not get you you all that much
additions performance, if you have a semi sane configuration. What is
your current bottleneck or performance problem?

Anyways... here are some reactions:

innodb_flush_log_at_trx_commit=3D0 ... THIS MEANS YOU CAN LOSE COMMITTED
TRANSACTIONS. Read up on this.

"innodb_flush_method=3DO_DSYNC"
Any particular reason you aren't using O_DIRECT ? Read up on this.

Why do you not have skip-name-resolve on? Read up on this.

innodb_thread_concurrency... As you are running 5.4 you can probably
set this to 0. Assuming you have 4 cores or less I wouldn't worry too
much about this.

I do not see log-bin... which would indicate that you don't have
binary logging on. What is your disaster recover plan?

To create an optimal cnf would require more knowledge about your
workload and your hardware.

--=20
Rob Wultsch
wultsch@gmail.com

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

Re: InnoDB - 16GB Data

Also, if you have read heavy workload, you might want to try using and
tuning your query cache.
Start off with something like 32M and incrementally tune it.
You can monitor some query cache related server variables.
Kyong

On Sat, Apr 10, 2010 at 4:28 PM, Rob Wultsch wrote:
> On Sat, Apr 10, 2010 at 12:10 AM, Junior Ortis wrote:
>> Hi Guys i have a dedicated mysql-server and neeed tips and sugestion
>> to optimize its to a better performance.
>>
>> 1-) Here i have results from mysqltunner
>>
>>>> =A0MySQLTuner 1.0.1 - Major Hayden
>> =A0>> =A0Bug reports, feature requests, and downloads at http://mysqltun=
er.com/
>> =A0>> =A0Run with '--help' for additional options and output filtering
>> Please enter your MySQL administrative login: toscaoSo
>> Please enter your MySQL administrative password:
>>
>> -------- General Statistics --------------------------------------------=
------
>> [--] Skipped version check for MySQLTuner script
>> [OK] Currently running supported MySQL version 5.4.3-beta-community
>> [OK] Operating on 64-bit architecture
>>
>> -------- Storage Engine Statistics -------------------------------------=
------
>> [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
>> [--] Data in MyISAM tables: 458M (Tables: 349)
>> [--] Data in InnoDB tables: 15G (Tables: 73)
>> [!!] Total fragmented tables: 47
>>
>> -------- Performance Metrics -------------------------------------------=
------
>> [--] Up for: 29d 12h 8m 1s (334M q [131.330 qps], 153K conn, TX: 194B, R=
X: 77B)
>> [--] Reads / Writes: 31% / 69%
>> [--] Total buffers: 15.5G global + 16.2M per thread (50 max threads)
>> [OK] Maximum possible memory usage: 16.3G (69% of installed RAM)
>> [OK] Slow queries: 0% (386/334M)
>> [OK] Highest usage of available connections: 46% (23/50)
>> [OK] Key buffer size / total MyISAM indexes: 300.0M/87.3M
>> [OK] Key buffer hit rate: 100.0% (78M cached / 22K reads)
>> [!!] Query cache is disabled
>> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4M sorts)
>> [OK] Temporary tables created on disk: 0% (548 on disk / 1M total)
>> [OK] Thread cache hit rate: 99% (23 created / 153K connections)
>> [OK] Table cache hit rate: 44% (467 open / 1K opened)
>> [OK] Open file limit used: 1% (684/65K)
>> [OK] Table locks acquired immediately: 99% (320M immediate / 320M locks)
>> [!!] InnoDB data size / buffer pool: 15.5G/15.0G
>>
>> -------- Recommendations -----------------------------------------------=
------
>> General recommendations:
>> =A0 =A0Run OPTIMIZE TABLE to defragment tables for better performance
>> =A0 =A0Enable the slow query log to troubleshoot bad queries
>> Variables to adjust:
>> =A0 =A0query_cache_size (>=3D 8M)
>> =A0 =A0innodb_buffer_pool_size (>=3D 15G)
>>
>>
>>
>> 2-) And here is my dedicate server i have (24GB ):
>>
>>
>> =A01 =A0[
>> =A0 =A0 =A0 =A0 0.0%] =A0 =A0 Tasks: 71 total, 2 running
>> =A02 =A0[|||||||
>> =A0 =A0 =A0 =A0 7.8%] =A0 =A0 Load average: 0.11 0.18 0.19
>> =A03 =A0[|
>> =A0 =A0 =A0 =A0 0.7%] =A0 =A0 Uptime: 62 days, 19:24:09
>> =A04 =A0[|
>> =A0 =A0 =A0 =A0 0.7%]
>> =A0Mem[||||||||||||||||||||||||||||||||||||||||||||||||||||| ||||||||||||=
16878/24165MB]
>> =A0Swp[|
>> =A0 =A0 0/5122MB]
>>
>>
>> 3-) And my.cnf
>>
>> vim .my.cnf
>> [client]
>> #password =A0 =A0 =A0 =3D [your_password]
>> port =A0 =A0 =A0 =A0 =A0  = 3306
>> socket =A0 =A0 =A0 =A0  = /tmp/mysql.sock
>>
>> # *** Application-specific options follow here ***
>>
>> #
>> # The MySQL server
>> #
>> [mysqld]
>> #large-pages
>>
>> # generic configuration options
>> port =A0 =A0 =A0 =A0 =A0  = 3306
>> socket =A0 =A0 =A0 =A0  = /tmp/mysql.sock
>> skip-locking
>> skip-external-locking
>> datadir =3D /disk3/Datareal/oficial/mysql
>> net_buffer_length =A0 =A0 =A0 =3D 1024K
>> join_buffer_size =A0 =A0 =A0  = 4M
>> sort_buffer_size =A0 =A0 =A0  = 4M
>> read_buffer_size =A0 =A0 =A0  = 4M
>> read_rnd_buffer_size =A0  = 4M
>> table_cache =A0 =A0 =A0 =A0 =A0 =A0 =3D 1000
>> max_allowed_packet =A0 =A0  = 160M
>>
>> max_connections=3D50
>> max_user_connections=3D200
>>
>> key_buffer =A0 =A0 =A0 =A0 =A0 =A0  = 300M
>> key_buffer_size =A0 =A0 =A0 =A0 =3D 300M
>> #thread_cache =A0 =A0 =A0 =A0 =A0 =3D 400
>> thread_stack =A0 =A0 =A0 =A0 =A0  = 192K
>> thread_cache_size =A0 =A0 =A0 =3D 96
>> thread_concurrency =A0 =A0  = 8
>> #thread_stack =A0 =A0 =A0 =A0 =A0 =3D 128K
>>
>> default-character-set =A0 =3D utf8
>> innodb_flush_method=3DO_DSYNC
>> innodb_buffer_pool_size=3D 15G
>> innodb_additional_mem_pool_size=3D128M
>> innodb_log_file_size=3D 256M
>> innodb_log_buffer_size=3D72M
>> innodb_flush_log_at_trx_commit=3D0
>> innodb_thread_concurrency=3D8
>> innodb_file_per_table=3D1
>> innodb_log_files_in_group=3D2
>> innodb_table_locks=3D0
>> innodb_lock_wait_timeout =3D 50
>>
>> "~/.my.cnf" 72L, 1570C
>>
>>
>>
>> Thanks guys for any tips/suggestion !
>>
>
> First, most performance comes from optimized table structures, index,
> and queries. Server tuning will not get you you all that much
> additions performance, if you have a semi sane configuration. =A0What is
> your current bottleneck or performance problem?
>
> Anyways... here are some reactions:
>
> innodb_flush_log_at_trx_commit=3D0 ... THIS MEANS YOU CAN LOSE COMMITTED
> TRANSACTIONS. Read up on this.
>
> "innodb_flush_method=3DO_DSYNC"
> Any particular reason you aren't using O_DIRECT ? Read up on this.
>
> Why do you not have skip-name-resolve on? Read up on this.
>
> innodb_thread_concurrency... As you are running 5.4 you can probably
> set this to 0. Assuming you have 4 cores or less I wouldn't worry too
> much about this.
>
> I do not see log-bin... which would indicate that you don't have
> binary logging on. What is your disaster recover plan?
>
> To create an optimal cnf would require more knowledge about your
> workload and your hardware.
>
> --
> Rob Wultsch
> wultsch@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gmai=
l.com
>
>

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