Question about slow storage and InnoDB compression

--========GMXBoundary93181315928201616186
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 8bit

Hello

I have upgraded our backup server from mysql 5.0.77 to mysql 5.5.15. We are using bacula as backup software, and all the info from backups is stored in a mysql database. Today I have upgraded from mysql 5.0 to 5.5 using IUS repository RPMS and with mysql_upgrade procedure, no problem so far. This backup systems hold the bacula daemon, the mysql server and the backup of other 100 systems (Solaris/Linux/Windows)

Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid-6 SATA disks (7200 rpm) connected to a Smart Array P812 controller & Red Hat Enterprise Linux 5.7 x64. Our mysql has dozens of millions of lines, and we are using InnoDB as storage engine for bacula internal data. We add hundred of thousands lines /day to our mysql (files are incrementally backed up daily from our 100 servers). So, we have a 7-8 concurrent writes (in different lines, of course) , and theorically we only read from mysql when we restore from backup.

Daily we launch a cron job that executes an "optimize table" in each table of our database to compact the database. It takes almost an hour. We are going to increase the memory of the server from 6 to 12 GB in a couple of weeks, and I will change my.cnf to reflect more memory. My actual my.cnf is attached below:


These are my questions:


- We have real slow storage (raid 6 SATA), but plenty CPU and ram . Should I enable innodb compression to make this mysql faster?
- This system is IOPS-constrained for mysql (fine for backup, though). Should I add a SSD only to hold mysql data?
- Any additional setting I should use to tune this mysql server?



my.cnf content:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock


[mysqld]
innodb_flush_method=O_DIRECT
max_connections = 15
wait_timeout = 86400
port = 3306
socket = /var/lib/mysql/mysql.sock
key_buffer = 100M
max_allowed_packet = 2M
table_cache = 2048
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 12M
myisam_sort_buffer_size = 384M
query_cache_type=1
query_cache_size=32M
thread_cache_size = 16
query_cache_size = 250M
thread_concurrency = 6
tmp_table_size = 1024M
max_heap_table = 1024M


skip-federated
innodb_buffer_pool_size= 2500M
innodb_additional_mem_pool_size = 32M

[mysqldump]
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 1250M
sort_buffer_size = 384M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 1250M
sort_buffer_size = 384M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout


Regards

Maria

--========GMXBoundary93181315928201616186--

Re: Question about slow storage and InnoDB compression

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

I would recommend to go for a 15K rpm SSD raid-10 to keep the mysql data and
add the Barracuda file format with innodb file per table settings, 3 to 4 GB
of innodb buffer pool depending the ratio of myisam v/s innodb in your db.
Check the current stats and reduce the tmp and heap table size to a lower
value, and reduce the remaining buffer's and cache as well.

On Tue, Sep 13, 2011 at 9:06 PM, Maria Arrea wrote:

> Hello
>
> I have upgraded our backup server from mysql 5.0.77 to mysql 5.5.15. We
> are using bacula as backup software, and all the info from backups is stored
> in a mysql database. Today I have upgraded from mysql 5.0 to 5.5 using IUS
> repository RPMS and with mysql_upgrade procedure, no problem so far. This
> backup systems hold the bacula daemon, the mysql server and the backup of
> other 100 systems (Solaris/Linux/Windows)
>
> Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid-6
> SATA disks (7200 rpm) connected to a Smart Array P812 controller & Red Hat
> Enterprise Linux 5.7 x64. Our mysql has dozens of millions of lines, and we
> are using InnoDB as storage engine for bacula internal data. We add hundred
> of thousands lines /day to our mysql (files are incrementally backed up
> daily from our 100 servers). So, we have a 7-8 concurrent writes (in
> different lines, of course) , and theorically we only read from mysql when
> we restore from backup.
>
> Daily we launch a cron job that executes an "optimize table" in each table
> of our database to compact the database. It takes almost an hour. We are
> going to increase the memory of the server from 6 to 12 GB in a couple of
> weeks, and I will change my.cnf to reflect more memory. My actual my.cnf is
> attached below:
>
>
> These are my questions:
>
>
> - We have real slow storage (raid 6 SATA), but plenty CPU and ram . Should
> I enable innodb compression to make this mysql faster?
> - This system is IOPS-constrained for mysql (fine for backup, though).
> Should I add a SSD only to hold mysql data?
> - Any additional setting I should use to tune this mysql server?
>
>
>
> my.cnf content:
>
> [client]
> port = 3306
> socket = /var/lib/mysql/mysql.sock
>
>
> [mysqld]
> innodb_flush_method=O_DIRECT
> max_connections = 15
> wait_timeout = 86400
> port = 3306
> socket = /var/lib/mysql/mysql.sock
> key_buffer = 100M
> max_allowed_packet = 2M
> table_cache = 2048
> sort_buffer_size = 16M
> read_buffer_size = 16M
> read_rnd_buffer_size = 12M
> myisam_sort_buffer_size = 384M
> query_cache_type=1
> query_cache_size=32M
> thread_cache_size = 16
> query_cache_size = 250M
> thread_concurrency = 6
> tmp_table_size = 1024M
> max_heap_table = 1024M
>
>
> skip-federated
> innodb_buffer_pool_size= 2500M
> innodb_additional_mem_pool_size = 32M
>
> [mysqldump]
> max_allowed_packet = 16M
>
> [mysql]
> no-auto-rehash
>
> [isamchk]
> key_buffer = 1250M
> sort_buffer_size = 384M
> read_buffer = 8M
> write_buffer = 8M
>
> [myisamchk]
> key_buffer = 1250M
> sort_buffer_size = 384M
> read_buffer = 8M
> write_buffer = 8M
>
> [mysqlhotcopy]
> interactive-timeout
>
>
> Regards
>
> Maria
>



--
Thanks
Suresh Kuna
MySQL DBA

--e89a8f6478237805c204acdc8d43--

Re: Question about slow storage and InnoDB compression

--e89a8f647823b5944904acdf1d26
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Thanks for correcting me in the disk stats Singer, A typo error of SSD
instead of SAS 15k rpm.

Compression may not increase the memory requirements :
To minimize I/O and to reduce the need to uncompress a page, at times the
buffer pool contains both the compressed and uncompressed form of a databas=
e
page. To make room for other required database pages, InnoDB may =93evict=
=94
from the buffer pool an uncompressed page, while leaving the compressed pag=
e
in memory. Or, if a page has not been accessed in a while, the compressed
form of the page may be written to disk, to free space for other data. Thus=
,
at any given time, the buffer pool may contain both the compressed and
uncompressed forms of the page, or only the compressed form of the page, or
neither.

More details and benefits about the barracuda file format can be found in
the below url Which helps to know the pros and cons on file format

http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_an telope
http://dev.mysql.com/doc/innodb/1.1/en/glossary.html#glos_ba rracuda
http://www.mysqlperformanceblog.com/2008/04/23/real-life-use -case-for-barra=
cuda-innodb-file-format/
http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes- file-formats.ht=
ml

I would go with the Singer suggestions in "What you want to do is" part.

Thanks
Suresh Kuna


On Wed, Sep 14, 2011 at 7:21 AM, Singer X.J. Wang wrot=
e:

> Comments:
> 1) There is no such thing as 15K RPM SSDs... SSDs are NON ROTATIONAL
> STORAGE, therefore RPMS make no sense..
> 2) Upgrading to Barracuda file format isn't really worth it in this case,
> you're not going to get any real benefits. In your scenario I doubt InnoD=
B
> table compression will help, as it will significantly increase your memor=
y
> requirements as it to keep uncompressed and compressed copies in RAM.
>
> Questions:
> 1) Why are you putting your MySQL data on the same volume as your Bacula
> backups? Bacula does large sequential I/O and MySQL will do random I/O ba=
sed
> on teh structure.
>
> What you want to do is:
>
> 1) you have 5MB InnoDB Log Files, that's a major bottleneck. I would use =
at
> 256MB or 512MB x 2 InnoDB log files.
> 2) dump and import the database using innodb_file_per_table so that
> optimization will free up space..
> 3) are you running Bacula on the server as well? If so, decrease the buff=
er
> pool to 1-2GB.. if not bump it up to to 3GB as you need some memory for
> bacula
>
> and 4, this is the most important one:
> How big is your MySQL data? Its not that big, I figure in the 80-100GB
> range. Get yourself a pair of 240GB SSDs, mount it locally for MySQL.
>
> S
>
>
>
>
>
> On Tue, Sep 13, 2011 at 21:19, Suresh Kuna wrot=
e:
>
>> I would recommend to go for a 15K rpm SSD raid-10 to keep the mysql data
>> and
>> add the Barracuda file format with innodb file per table settings, 3 to =
4
>> GB
>> of innodb buffer pool depending the ratio of myisam v/s innodb in your d=
b.
>> Check the current stats and reduce the tmp and heap table size to a lowe=
r
>> value, and reduce the remaining buffer's and cache as well.
>>
>>
>
>> On Tue, Sep 13, 2011 at 9:06 PM, Maria Arrea wrote=
:
>>
>> > Hello
>> >
>> > I have upgraded our backup server from mysql 5.0.77 to mysql 5.5.15. =
We
>> > are using bacula as backup software, and all the info from backups is
>> stored
>> > in a mysql database. Today I have upgraded from mysql 5.0 to 5.5 using
>> IUS
>> > repository RPMS and with mysql_upgrade procedure, no problem so far.
>> This
>> > backup systems hold the bacula daemon, the mysql server and the backup
>> of
>> > other 100 systems (Solaris/Linux/Windows)
>> >
>> > Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid=
-6
>> > SATA disks (7200 rpm) connected to a Smart Array P812 controller & Red
>> Hat
>> > Enterprise Linux 5.7 x64. Our mysql has dozens of millions of lines, a=
nd
>> we
>> > are using InnoDB as storage engine for bacula internal data. We add
>> hundred
>> > of thousands lines /day to our mysql (files are incrementally backed u=
p
>> > daily from our 100 servers). So, we have a 7-8 concurrent writes (in
>> > different lines, of course) , and theorically we only read from mysql
>> when
>> > we restore from backup.
>> >
>> > Daily we launch a cron job that executes an "optimize table" in each
>> table
>> > of our database to compact the database. It takes almost an hour. We a=
re
>> > going to increase the memory of the server from 6 to 12 GB in a couple
>> of
>> > weeks, and I will change my.cnf to reflect more memory. My actual my.c=
nf
>> is
>> > attached below:
>> >
>> >
>> > These are my questions:
>> >
>> >
>> > - We have real slow storage (raid 6 SATA), but plenty CPU and ram .
>> Should
>> > I enable innodb compression to make this mysql faster?
>> > - This system is IOPS-constrained for mysql (fine for backup, though)=
..
>> > Should I add a SSD only to hold mysql data?
>> > - Any additional setting I should use to tune this mysql server?
>> >
>> >
>> >
>> > my.cnf content:
>> >
>> > [client]
>> > port =3D 3306
>> > socket =3D /var/lib/mysql/mysql.sock
>> >
>> >
>> > [mysqld]
>> > innodb_flush_method=3DO_DIRECT
>> > max_connections =3D 15
>> > wait_timeout =3D 86400
>> > port =3D 3306
>> > socket =3D /var/lib/mysql/mysql.sock
>> > key_buffer =3D 100M
>> > max_allowed_packet =3D 2M
>> > table_cache =3D 2048
>> > sort_buffer_size =3D 16M
>> > read_buffer_size =3D 16M
>> > read_rnd_buffer_size =3D 12M
>> > myisam_sort_buffer_size =3D 384M
>> > query_cache_type=3D1
>> > query_cache_size=3D32M
>> > thread_cache_size =3D 16
>> > query_cache_size =3D 250M
>> > thread_concurrency =3D 6
>> > tmp_table_size =3D 1024M
>> > max_heap_table =3D 1024M
>> >
>> >
>> > skip-federated
>> > innodb_buffer_pool_size=3D 2500M
>> > innodb_additional_mem_pool_size =3D 32M
>> >
>> > [mysqldump]
>> > max_allowed_packet =3D 16M
>> >
>> > [mysql]
>> > no-auto-rehash
>> >
>> > [isamchk]
>> > key_buffer =3D 1250M
>> > sort_buffer_size =3D 384M
>> > read_buffer =3D 8M
>> > write_buffer =3D 8M
>> >
>> > [myisamchk]
>> > key_buffer =3D 1250M
>> > sort_buffer_size =3D 384M
>> > read_buffer =3D 8M
>> > write_buffer =3D 8M
>> >
>> > [mysqlhotcopy]
>> > interactive-timeout
>> >
>> >
>> > Regards
>> >
>> > Maria
>> >
>>
>>
>>
>> --
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>>
>
> --
> The best compliment you could give Pythian for our service is a referral.
>
>
>


--=20
Thanks
Suresh Kuna
MySQL DBA

--e89a8f647823b5944904acdf1d26--

Re: Question about slow storage and InnoDB compression

--========GMXBoundary93151315986640168617
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 8bit

The server hosting bacula and the database only has one kind of disk: SATA, maybe I should buy a couple of SSD for mysql.

I have read all your mails, and still not sure if I should enable innodb compression. My ibfile is 50 GB, though.

Regards

Maria


Questions:
1) Why are you putting your MySQL data on the same volume as your Bacula backups? Bacula does large sequential I/O and MySQL will do random I/O based on teh structure.





What you want to do is:

1) you have 5MB InnoDB Log Files, that's a major bottleneck. I would use at 256MB or 512MB x 2 InnoDB log files.
2) dump and import the database using innodb_file_per_table so that optimization will free up space..
3) are you running Bacula on the server as well? If so, decrease the buffer pool to 1-2GB.. if not bump it up to to 3GB as you need some memory for bacula

and 4, this is the most important one:
How big is your MySQL data? Its not that big, I figure in the 80-100GB range. Get yourself a pair of 240GB SSDs, mount it locally for MySQL.

S



On Tue, Sep 13, 2011 at 21:19, Suresh Kuna < sureshkumarilu@gmail.com > wrote:
I would recommend to go for a 15K rpm SSD raid-10 to keep the mysql data and
add the Barracuda file format with innodb file per table settings, 3 to 4 GB
of innodb buffer pool depending the ratio of myisam v/s innodb in your db.
Check the current stats and reduce the tmp and heap table size to a lower
value, and reduce the remaining buffer's and cache as well.

On Tue, Sep 13, 2011 at 9:06 PM, Maria Arrea < maria_arrea@gmx.com > wrote:

> Hello
>
> I have upgraded our backup server from mysql 5.0.77 to mysql 5.5.15. We
> are using bacula as backup software, and all the info from backups is stored
> in a mysql database. Today I have upgraded from mysql 5.0 to 5.5 using IUS
> repository RPMS and with mysql_upgrade procedure, no problem so far. This
> backup systems hold the bacula daemon, the mysql server and the backup of
> other 100 systems (Solaris/Linux/Windows)
>
> Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid-6
> SATA disks (7200 rpm) connected to a Smart Array P812 controller & Red Hat
> Enterprise Linux 5.7 x64. Our mysql has dozens of millions of lines, and we
> are using InnoDB as storage engine for bacula internal data. We add hundred
> of thousands lines /day to our mysql (files are incrementally backed up
> daily from our 100 servers). So, we have a 7-8 concurrent writes (in
> different lines, of course) , and theorically we only read from mysql when
> we restore from backup.
>
> Daily we launch a cron job that executes an "optimize table" in each table
> of our database to compact the database. It takes almost an hour. We are
> going to increase the memory of the server from 6 to 12 GB in a couple of
> weeks, and I will change my.cnf to reflect more memory. My actual my.cnf is
> attached below:
>
>
> These are my questions:
>
>
> - We have real slow storage (raid 6 SATA), but plenty CPU and ram . Should
> I enable innodb compression to make this mysql faster?
> - This system is IOPS-constrained for mysql (fine for backup, though).
> Should I add a SSD only to hold mysql data?
> - Any additional setting I should use to tune this mysql server?
>
>
>
> my.cnf content:
>
> [client]
> port = 3306
> socket = /var/lib/mysql/mysql.sock
>
>
> [mysqld]
> innodb_flush_method=O_DIRECT
> max_connections = 15
> wait_timeout = 86400
> port = 3306
> socket = /var/lib/mysql/mysql.sock
> key_buffer = 100M
> max_allowed_packet = 2M
> table_cache = 2048
> sort_buffer_size = 16M
> read_buffer_size = 16M
> read_rnd_buffer_size = 12M
> myisam_sort_buffer_size = 384M
> query_cache_type=1
> query_cache_size=32M
> thread_cache_size = 16
> query_cache_size = 250M
> thread_concurrency = 6
> tmp_table_size = 1024M
> max_heap_table = 1024M
>
>
> skip-federated
> innodb_buffer_pool_size= 2500M
> innodb_additional_mem_pool_size = 32M
>
> [mysqldump]
> max_allowed_packet = 16M
>
> [mysql]
> no-auto-rehash
>
> [isamchk]
> key_buffer = 1250M
> sort_buffer_size = 384M
> read_buffer = 8M
> write_buffer = 8M
>
> [myisamchk]
> key_buffer = 1250M
> sort_buffer_size = 384M
> read_buffer = 8M
> write_buffer = 8M
>
> [mysqlhotcopy]
> interactive-timeout
>
>
> Regards
>
> Maria
>


--
Thanks
Suresh Kuna
MySQL DBA
-- The best compliment you could give Pythian for our service is a referral.

--========GMXBoundary93151315986640168617--

Re: Question about slow storage and InnoDB compression

--------------enigF2D3E5433B10F57B52DC181F
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable



Am 14.09.2011 09:50, schrieb Maria Arrea:
> I have read all your mails, and still not sure if I should enable inno=
db compression

if you have enough free cpu-ressources and IO is your problem simply yes
because the transfer from/to disk will be not so high as uncompressed






--------------enigF2D3E5433B10F57B52DC181F
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk5wa8cACgkQhmBjz394AnksDQCffc4dsScCZOUzDQuTjzHJ sAre
vosAnjxeb20Wi7SF1p7yfYh9KNw6UPdh
=RMQR
-----END PGP SIGNATURE-----

--------------enigF2D3E5433B10F57B52DC181F--

Re: Question about slow storage and InnoDB compression

--========GMXBoundary4061316004617806830
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 8bit

I have finally enabled compression:


+----------------+--------+---------+------------+---------- -+----------------+-------------+-----------------+--------- -----+-----------+----------------+---------------------+--- ------------------+---------------------+------------------- +----------+-----------------------------------------+------ ---+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+---------- -+----------------+-------------+-----------------+--------- -----+-----------+----------------+---------------------+--- ------------------+---------------------+------------------- +----------+-----------------------------------------+------ ---+
| BaseFiles | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| CDImages | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Client | InnoDB | 10 | Compressed | 46 | 356 | 16384 | 0 | 16384 | 0 | 53 | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Counters | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Device | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| File | InnoDB | 10 | Compressed | 106551231 | 129 | 13763608576 | 0 | 7449083904 | 7340032 | 516304137 | 2011-09-14 12:53:45 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| FileSet | InnoDB | 10 | Compressed | 8 | 2048 | 16384 | 0 | 0 | 0 | 11 | 2011-09-14 11:26:17 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Filename | InnoDB | 10 | Compressed | 39608549 | 62 | 2455764992 | 0 | 3063939072 | 4194304 | 49584798 | 2011-09-14 13:11:41 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Job | InnoDB | 10 | Compressed | 3499 | 454 | 1589248 | 0 | 212992 | 4194304 | 10200 | 2011-09-14 13:11:42 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| JobHisto | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | | 2011-09-14 11:42:30 | 2011-09-14 11:42:30 | 2011-09-14 11:42:30 | latin1_swedish_ci | | | |
| JobMedia | InnoDB | 10 | Compressed | 52788 | 69 | 3686400 | 0 | 2637824 | 4194304 | 150064 | 2011-09-14 13:11:44 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Location | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2011-09-14 11:42:32 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| LocationLog | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | 1 | 2011-09-14 11:42:32 | 2011-09-14 11:42:32 | | latin1_swedish_ci | | | |
| Log | InnoDB | 10 | Compressed | 31578 | 349 | 11026432 | 0 | 1589248 | 4194304 | 285940 | 2011-09-14 13:11:45 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Media | MyISAM | 10 | Dynamic | 39 | 142 | 5568 | 281474976710655 | 4096 | 0 | 47 | 2011-09-14 11:42:33 | 2011-09-14 11:42:33 | 2011-09-14 11:42:33 | latin1_swedish_ci | | | |
| MediaType | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | 2 | 2011-09-14 11:42:33 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Path | InnoDB | 10 | Compressed | 4681359 | 81 | 380452864 | 0 | 581959680 | 7340032 | 4527256 | 2011-09-14 13:13:23 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| PathHierarchy | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 | 0 | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| PathVisibility | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 | 0 | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Pool | InnoDB | 10 | Compressed | 8 | 2048 | 16384 | 0 | 16384 | 0 | 9 | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Status | InnoDB | 10 | Compressed | 21 | 780 | 16384 | 0 | 0 | 0 | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Storage | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | 2 | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| UnsavedFiles | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
| Version | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=COMPRESSED KEY_BLOCK_SIZE=16 | |
+----------------+--------+---------+------------+---------- -+----------------+-------------+-----------------+--------- -----+-----------+----------------+---------------------+--- ------------------+---------------------+------------------- +----------+-----------------------------------------+------ ---+


I am still benchmarking, but I see a 15-20% performance gain after enabling compression using bacula gui (bat).

Regards

Maria

----- Original Message -----
From: Maria Arrea
Sent: 09/14/11 09:50 AM
To: mysql@lists.mysql.com
Subject: Re: Question about slow storage and InnoDB compression

The server hosting bacula and the database only has one kind of disk: SATA, maybe I should buy a couple of SSD for mysql. I have read all your mails, and still not sure if I should enable innodb compression. My ibfile is 50 GB, though. Regards Maria Questions: 1) Why are you putting your MySQL data on the same volume as your Bacula backups? Bacula does large sequential I/O and MySQL will do random I/O based on teh structure. What you want to do is: 1) you have 5MB InnoDB Log Files, that's a major bottleneck. I would use at 256MB or 512MB x 2 InnoDB log files. 2) dump and import the database using innodb_file_per_table so that optimization will free up space.. 3) are you running Bacula on the server as well? If so, decrease the buffer pool to 1-2GB.. if not bump it up to to 3GB as you need
some memory for bacula and 4, this is the most important one: How big is your MySQL data? Its not that big, I figure in the 80-100GB range. Get yourself a pair of 240GB SSDs, mount it locally for MySQL. S On Tue, Sep 13, 2011 at 21:19, Suresh Kuna < sureshkumarilu@gmail.com > wrote: I would recommend to go for a 15K rpm SSD raid-10 to keep the mysql data and add the Barracuda file format with innodb file per table settings, 3 to 4 GB of innodb buffer pool depending the ratio of myisam v/s innodb in your db. Check the current stats and reduce the tmp and heap table size to a lower value, and reduce the remaining buffer's and cache as well. On Tue, Sep 13, 2011 at 9:06 PM, Maria Arrea < maria_arrea@gmx.com > wrote: > Hello > > I have upgraded our backup server from mysql 5.0.77 to mysql 5
.5.15. We > are using bacula as backup software, and all the info from backups is stored > in a mysql database. Today I have upgraded from mysql 5.0 to 5.5 using IUS > repository RPMS and with mysql_upgrade procedure, no problem so far. This > backup systems hold the bacula daemon, the mysql server and the backup of > other 100 systems (Solaris/Linux/Windows) > > Our server has 6 GB of ram, 1 quad Intel Xeon E5520 and 46 TB of raid-6 > SATA disks (7200 rpm) connected to a Smart Array P812 controller & Red Hat > Enterprise Linux 5.7 x64. Our mysql has dozens of millions of lines, and we > are using InnoDB as storage engine for bacula internal data. We add hundred > of thousands lines /day to our mysql (files are incrementally backed up > daily from our 100 servers). So, we have a 7-8 conc
urrent writes (in > different lines, of course) , and theorically we only read from mysql when > we restore from backup. > > Daily we launch a cron job that executes an "optimize table" in each table > of our database to compact the database. It takes almost an hour. We are > going to increase the memory of the server from 6 to 12 GB in a couple of > weeks, and I will change my.cnf to reflect more memory. My actual my.cnf is > attached below: > > > These are my questions: > > > - We have real slow storage (raid 6 SATA), but plenty CPU and ram . Should > I enable innodb compression to make this mysql faster? > - This system is IOPS-constrained for mysql (fine for backup, though). > Should I add a SSD only to hold mysql data? > - Any additional setting I should use to tune this mysql serve
r? > > > > my.cnf content: > > [client] > port = 3306 > socket = /var/lib/mysql/mysql.sock > > > [mysqld] > innodb_flush_method=O_DIRECT > max_connections = 15 > wait_timeout = 86400 > port = 3306 > socket = /var/lib/mysql/mysql.sock > key_buffer = 100M > max_allowed_packet = 2M > table_cache = 2048 > sort_buffer_size = 16M > read_buffer_size = 16M > read_rnd_buffer_size = 12M > myisam_sort_buffer_size = 384M > query_cache_type=1 > query_cache_size=32M > thread_cache_size = 16 > query_cache_size = 250M > thread_concurrency = 6 > tmp_table_size = 1024M > max_heap_table = 1024M > > > skip-federated > innodb_buffer_pool_size= 2500M > innodb_additional_mem_pool_size = 32M > > [mysqldump] > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > > [isamchk] > key_buffer = 1250M > sort_buffer_
size = 384M > read_buffer = 8M > write_buffer = 8M > > [myisamchk] > key_buffer = 1250M > sort_buffer_size = 384M > read_buffer = 8M > write_buffer = 8M > > [mysqlhotcopy] > interactive-timeout > > > Regards > > Maria > -- Thanks Suresh Kuna MySQL DBA -- The best compliment you could give Pythian for our service is a referral.

--========GMXBoundary4061316004617806830--

Re: Question about slow storage and InnoDB compression

--------------enigD89FDD34C9CCAE90AD756D24
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable



Am 14.09.2011 14:50, schrieb Maria Arrea:
> I have finally enabled compression:
> I am still benchmarking, but I see a 15-20% performance gain after enab=
ling compression using bacula gui
as expected if disk-io is the only bottenleck
the same with NTFS-Compression inside a VMware Machine on modern hardware=



--------------enigD89FDD34C9CCAE90AD756D24
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk5wpOkACgkQhmBjz394AnnpsACfULvGpH2QEJhTvdQ7rRyE BuLe
io4An14zcBjveL/VGqVG2QTDJOCtMz9f
=l9v5
-----END PGP SIGNATURE-----

--------------enigD89FDD34C9CCAE90AD756D24--

Re: Question about slow storage and InnoDB compression

--20cf303f67faf5190c04acf4149e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

" I am still benchmarking, but I see a 15-20% performance gain after
enabling compression using bacula gui (bat)."

This is a very good performance improvement and how much disk space did you
saved here ?

Can you do bench marking with 4kb and 8kb key_block_size as well to check
what suits your application. I saw there has been improvement in performanc=
e
by adjusting this one too.


On Wed, Sep 14, 2011 at 6:20 PM, Maria Arrea wrote:

> I have finally enabled compression:
>
>
>
> +----------------+--------+---------+------------+---------- -+----------=
------+-------------+-----------------+--------------+------ -----+---------=
-------+---------------------+---------------------+-------- -------------+-=
------------------+----------+------------------------------ -----------+---=
------+
> | Name | Engine | Version | Row_format | Rows | Avg_row_length |
> Data_length | Max_data_length | Index_length | Data_free | Auto_increment=
|
> Create_time | Update_time | Check_time | Collation | Checksum |
> Create_options | Comment |
>
> +----------------+--------+---------+------------+---------- -+----------=
------+-------------+-----------------+--------------+------ -----+---------=
-------+---------------------+---------------------+-------- -------------+-=
------------------+----------+------------------------------ -----------+---=
------+
> | BaseFiles | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 | 0 |=
1
> | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | CDImages | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | Client | InnoDB | 10 | Compressed | 46 | 356 | 16384 | 0 | 16384 | 0 |
> 53 | 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=3DCOMPRES=
SED
> KEY_BLOCK_SIZE=3D16 | |
> | Counters | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | Device | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 |
> 2011-09-14 10:33:04 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | File | InnoDB | 10 | Compressed | 106551231 | 129 | 13763608576 | 0 |
> 7449083904 | 7340032 | 516304137 | 2011-09-14 12:53:45 | | |
> latin1_swedish_ci | | row_format=3DCOMPRESSED KEY_BLOCK_SIZE=3D16 | |
> | FileSet | InnoDB | 10 | Compressed | 8 | 2048 | 16384 | 0 | 0 | 0 | 11=
|
> 2011-09-14 11:26:17 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | Filename | InnoDB | 10 | Compressed | 39608549 | 62 | 2455764992 | 0 |
> 3063939072 | 4194304 | 49584798 | 2011-09-14 13:11:41 | | |
> latin1_swedish_ci | | row_format=3DCOMPRESSED KEY_BLOCK_SIZE=3D16 | |
> | Job | InnoDB | 10 | Compressed | 3499 | 454 | 1589248 | 0 | 212992 |
> 4194304 | 10200 | 2011-09-14 13:11:42 | | | latin1_swedish_ci | |
> row_format=3DCOMPRESSED KEY_BLOCK_SIZE=3D16 | |
> | JobHisto | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 =
|
> 0 | | 2011-09-14 11:42:30 | 2011-09-14 11:42:30 | 2011-09-14 11:42:30 |
> latin1_swedish_ci | | | |
> | JobMedia | InnoDB | 10 | Compressed | 52788 | 69 | 3686400 | 0 | 26378=
24
> | 4194304 | 150064 | 2011-09-14 13:11:44 | | | latin1_swedish_ci | |
> row_format=3DCOMPRESSED KEY_BLOCK_SIZE=3D16 | |
> | Location | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | 1 |
> 2011-09-14 11:42:32 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | LocationLog | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 10=
24
> | 0 | 1 | 2011-09-14 11:42:32 | 2011-09-14 11:42:32 | | latin1_swedish_ci=
|
> | | |
> | Log | InnoDB | 10 | Compressed | 31578 | 349 | 11026432 | 0 | 1589248 =
|
> 4194304 | 285940 | 2011-09-14 13:11:45 | | | latin1_swedish_ci | |
> row_format=3DCOMPRESSED KEY_BLOCK_SIZE=3D16 | |
> | Media | MyISAM | 10 | Dynamic | 39 | 142 | 5568 | 281474976710655 | 40=
96
> | 0 | 47 | 2011-09-14 11:42:33 | 2011-09-14 11:42:33 | 2011-09-14 11:42:3=
3 |
> latin1_swedish_ci | | | |
> | MediaType | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 |=
2
> | 2011-09-14 11:42:33 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | Path | InnoDB | 10 | Compressed | 4681359 | 81 | 380452864 | 0 |
> 581959680 | 7340032 | 4527256 | 2011-09-14 13:13:23 | | | latin1_swedish_=
ci
> | | row_format=3DCOMPRESSED KEY_BLOCK_SIZE=3D16 | |
> | PathHierarchy | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 |=
0
> | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=3DCOMPRESS=
ED
> KEY_BLOCK_SIZE=3D16 | |
> | PathVisibility | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 16384 =
|
> 0 | | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=3DCOMPRE=
SSED
> KEY_BLOCK_SIZE=3D16 | |
> | Pool | InnoDB | 10 | Compressed | 8 | 2048 | 16384 | 0 | 16384 | 0 | 9=
|
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | Status | InnoDB | 10 | Compressed | 21 | 780 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | Storage | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | 2=
|
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | UnsavedFiles | InnoDB | 10 | Compressed | 0 | 0 | 16384 | 0 | 0 | 0 | =
1
> | 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
> | Version | InnoDB | 10 | Compressed | 1 | 16384 | 16384 | 0 | 0 | 0 | |
> 2011-09-14 11:44:16 | | | latin1_swedish_ci | | row_format=3DCOMPRESSED
> KEY_BLOCK_SIZE=3D16 | |
>
> +----------------+--------+---------+------------+---------- -+----------=
------+-------------+-----------------+--------------+------ -----+---------=
-------+---------------------+---------------------+-------- -------------+-=
------------------+----------+------------------------------ -----------+---=
------+
>
>
> I am still benchmarking, but I see a 15-20% performance gain after
> enabling compression using bacula gui (bat).
>
> Regards
>
> Maria
>
> ----- Original Message -----
> From: Maria Arrea
> Sent: 09/14/11 09:50 AM
> To: mysql@lists.mysql.com
> Subject: Re: Question about slow storage and InnoDB compression
>
> The server hosting bacula and the database only has one kind of disk:
> SATA, maybe I should buy a couple of SSD for mysql. I have read all your
> mails, and still not sure if I should enable innodb compression. My ibfil=
e
> is 50 GB, though. Regards Maria Questions: 1) Why are you putting your My=
SQL
> data on the same volume as your Bacula backups? Bacula does large sequent=
ial
> I/O and MySQL will do random I/O based on teh structure. What you want to=
do
> is: 1) you have 5MB InnoDB Log Files, that's a major bottleneck. I would =
use
> at 256MB or 512MB x 2 InnoDB log files. 2) dump and import the database
> using innodb_file_per_table so that optimization will free up space.. 3) =
are
> you running Bacula on the server as well? If so, decrease the buffer pool=
to
> 1-2GB.. if not bump it up to to 3GB as you need some memory for bacula an=
d
> 4, this is the most important one: How big is your MySQL data? Its not th=
at
> big, I figure in the 80-100GB range. Get yourself a pair of 240GB SSDs,
> mount it locally for MySQL. S On Tue, Sep 13, 2011 at 21:19, Suresh Kuna =
<
> sureshkumarilu@gmail.com > wrote: I would recommend to go for a 15K rpm
> SSD raid-10 to keep the mysql data and add the Barracuda file format with
> innodb file per table settings, 3 to 4 GB of innodb buffer pool depending
> the ratio of myisam v/s innodb in your db. Check the current stats and
> reduce the tmp and heap table size to a lower value, and reduce the
> remaining buffer's and cache as well. On Tue, Sep 13, 2011 at 9:06 PM, Ma=
ria
> Arrea < maria_arrea@gmx.com > wrote: > Hello > > I have upgraded our
> backup server from mysql 5.0.77 to mysql 5.5.15. We > are using bacula as
> backup software, and all the info from backups is stored > in a mysql
> database. Today I have upgraded from mysql 5.0 to 5.5 using IUS > reposit=
ory
> RPMS and with mysql_upgrade procedure, no problem so far. This > backup
> systems hold the bacula daemon, the mysql server and the backup of > othe=
r
> 100 systems (Solaris/Linux/Windows) > > Our server has 6 GB of ram, 1 qua=
d
> Intel Xeon E5520 and 46 TB of raid-6 > SATA disks (7200 rpm) connected to=
a
> Smart Array P812 controller & Red Hat > Enterprise Linux 5.7 x64. Our mys=
ql
> has dozens of millions of lines, and we > are using InnoDB as storage eng=
ine
> for bacula internal data. We add hundred > of thousands lines /day to our
> mysql (files are incrementally backed up > daily from our 100 servers). S=
o,
> we have a 7-8 concurrent writes (in > different lines, of course) , and
> theorically we only read from mysql when > we restore from backup. > > Da=
ily
> we launch a cron job that executes an "optimize table" in each table > of
> our database to compact the database. It takes almost an hour. We are >
> going to increase the memory of the server from 6 to 12 GB in a couple of=
>
> weeks, and I will change my.cnf to reflect more memory. My actual my.cnf =
is
> > attached below: > > > These are my questions: > > > - We have real slow
> storage (raid 6 SATA), but plenty CPU and ram . Should > I enable innodb
> compression to make this mysql faster? > - This system is IOPS-constraine=
d
> for mysql (fine for backup, though). > Should I add a SSD only to hold my=
sql
> data? > - Any additional setting I should use to tune this mysql server? =
> >
> > > my.cnf content: > > [client] > port =3D 3306 > socket =3D
> /var/lib/mysql/mysql.sock > > > [mysqld] > innodb_flush_method=3DO_DIRECT=
>
> max_connections =3D 15 > wait_timeout =3D 86400 > port =3D 3306 > socket =
=3D
> /var/lib/mysql/mysql.sock > key_buffer =3D 100M > max_allowed_packet =3D =
2M >
> table_cache =3D 2048 > sort_buffer_size =3D 16M > read_buffer_size =3D 16=
M >
> read_rnd_buffer_size =3D 12M > myisam_sort_buffer_size =3D 384M >
> query_cache_type=3D1 > query_cache_size=3D32M > thread_cache_size =3D 16 =
>
> query_cache_size =3D 250M > thread_concurrency =3D 6 > tmp_table_size =3D=
1024M >
> max_heap_table =3D 1024M > > > skip-federated > innodb_buffer_pool_size=
=3D 2500M
> > innodb_additional_mem_pool_size =3D 32M > > [mysqldump] > max_allowed_p=
acket
> =3D 16M > > [mysql] > no-auto-rehash > > [isamchk] > key_buffer =3D 1250M=
>
> sort_buffer_size =3D 384M > read_buffer =3D 8M > write_buffer =3D 8M > >
> [myisamchk] > key_buffer =3D 1250M > sort_buffer_size =3D 384M > read_buf=
fer =3D
> 8M > write_buffer =3D 8M > > [mysqlhotcopy] > interactive-timeout > > >
> Regards > > Maria > -- Thanks Suresh Kuna MySQL DBA -- The best complimen=
t
> you could give Pythian for our service is a referral.
>



--=20
Thanks
Suresh Kuna
MySQL DBA

--20cf303f67faf5190c04acf4149e--