Re: InnoDB Performance

[Heikki Tuuri]

Marcin,

you must set innodb_log_file_size as recommended in the manual:

http://dev.mysql.com/doc/mysql/en/innodb-configuration.html

[mysqld]
# You can write your other MySQL server options here
# ...
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#
# Set the log file size to about 25% of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

Since the workload is disk-bound, the following are relevant:

http://dev.mysql.com/doc/mysql/en/news-5-0-3.html
"
InnoDB: Introduced a compact record format that does not store the number of 
columns or the lengths of fixed-size columns. The old format can be 
requested by specifying ROW_FORMAT=REDUNDANT. The new format 
(ROW_FORMAT=COMPACT) is the default.
"

The above saves about 20 % of space.

http://www.innodb.com/todo.php
"
Implement transparent zip-like compression of InnoDB index pages. Compressed 
tables will take about 60 % less disk space than normal tables. The downside 
is some more CPU usage in queries and inserts. Appears in 5.1.
"

Best regards,

Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/


----- Original Message ----- 
From: "Marcin Lewandowski" <lists@stripped>
Newsgroups: mailing.database.myodbc
Sent: Wednesday, April 13, 2005 12:28 AM
Subject: Re: InnoDB Performance


> I've changed settings to:
>
> innodb_data_file_path = ibdata1:128M:autoextend
> innodb_buffer_pool_size=150M
> innodb_additional_mem_pool_size = 50M
>
> and system load is "only" 2 to 3.
>
> kernel napisa³(a):
>> What does the cpu % show when the machine has the high load avg ?
>
>
> Now, there are about 50% of normal load and system load is "only" circa
> 1.0. On myisam there was about 0.5. And here comes few lines from top:
>
> Cpu(s): 19.9% us,  2.6% sy,  0.0% ni, 74.8% id,  2.3% wa,  0.0% hi,  0.3% 
> si
>
> Cpu(s): 14.3% us,  1.0% sy,  0.0% ni, 82.4% id,  2.0% wa,  0.0% hi,  0.3% 
> si
>
> Cpu(s): 26.9% us,  3.3% sy,  0.0% ni, 69.4% id,  0.0% wa,  0.0% hi,  0.3% 
> si
>
>> Usually high load avgs point to disk I/O isssues. What is the size of
>> your ibdata1 file ? If you have more ram, you can increase
>
> I've got 512 mb of RAM, and it's full (and 200mb of swap is currently 
> used).
>
> server root # ls -l /data/mysql/ib*
> -rw-rw----  1 mysql mysql    5242880 Apr 12 23:14 /data/mysql/ib_logfile0
> -rw-rw----  1 mysql mysql    5242880 Apr 12 18:48 /data/mysql/ib_logfile1
> -rw-rw----  1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1
>
>> innodb_buffer_pool_size or do some tweaks to the OS so it  caches  the
>> disk a little more.
>
>
>
> -- 
> Marcin Lewandowski
> [  mailto:www@stripped  gg:188068 jid:saepia@stripped  ]
> [   http://www.nosoftwarepatents.com/pl/m/intro/index.html   ]
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
>