Mysql Database Grow | How Optimize parameters

[xs-]

Are you using the innodb_file_per_table option?

The innodb space will always grow, because it doesn't clean / re-use free-space areas within the innodb files very well (if at all).

Now Mysql has a the option to 'optimize tables' which, among other things, shrinks the innodb files (but only if you are using the innodb_file_per_table). It will not shrink the default 1/big innodb file.

PS
Keep in mind that you can't just 'turn on innodb_file_per_table'. You need to dump everything, delete all mysql files, enable the option and restore the dumps.

[pierre-hoffmann]

PS
Keep in mind that you can't just 'turn on innodb_file_per_table'. You need to dump everything, delete all mysql files, enable the option and restore the dumps.

Yes it's a big operation for 18Go of data mySql database so i don't make it for this moment.
But i've don't understand that, with innoDb per file Table option enable, the OPTIMIZE shrink the datafile ... it's Great news for me !

Regards,
Pierre.

[pierre-hoffmann]

Oups another problem:

today, i've try to clean my database with make an OPTIMIZE table,
and i've this error; do you know it ?

mysql> optimize table history_uint;
+---------------------+----------+----------+------------------------------------+
| Table               | Op       | Msg_type | Msg_text                           |
+---------------------+----------+----------+------------------------------------+
| zabbix.history_uint | optimize | error    | The table '#sql-194f_f2a9' is full |
| zabbix.history_uint | optimize | status   | Operation failed                   |
+---------------------+----------+----------+------------------------------------+
2 rows in set, 1 warning (1 hour 7 min 38.16 sec)

[EDIT]
OPTIMIZE have create an TMP table (copy of history_uint) that has auto-extend ibdata1 tablespace to full filesystem and Operation failed ...

080709 11:34:01  InnoDB: Error:
   Write to file /db/mysql/ibdata1 failed at offset 9 563085312.
InnoDB: 1048576 bytes should have been written, only 942080 were written.
InnoDB: Operating system error number 0.

/dev/mapper/system-dblv
                      40151648  38520196         0 100% /db

So i've now a 40 Go datafile ... not better

Regards,
Pierre.

[xs-]

yeah you dont want to do that with one big ibdata file.

[pierre-hoffmann]

Hi,

it's the day; i want migrate Zabbix 1.4.6 to 1.6.1; so i want migrate my database too. (for instant it's only a test)

Now with all optimizations i've make; database size was stable at 20 Gb.

So, i've make export (about 40 minutes)

mysqldump --extended-insert --quick --single-transaction
 --default-character-set=latin1 -t zabbix > ${export_data}

i've make import (about 21 hours and 45 minutes)

(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
cat $export_data
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
) | mysql -u root -p zabbix

Now i've some question:

• How to reduce import time ?
• Why export file is only 6 Go ?? (Source 20 Go and on destination 14 Go)
• My database is in "latin1_swedish_ci" character set so when import i lose all french specific chars (éèà ...);
  solution is to force utf-8 import or something else ??

Regards,
Pierre.

[xs-]

* How to reduce import time ?
More mem, faster disks. The autocommit and foreignkeycheck tricks are the most common way to speed things up (which you are already doing)

* Why export file is only 6 Go ?? (Source 20 Go and on destination 14 Go)
Indexes

* My database is in "latin1_swedish_ci" character set so when import i lose all french specific chars (éèà ...);
solution is to force utf-8 import or something else ??
Hmm not really. It should stay that way. On table reation you can pass a charset (which mysqldump already should do) but you negated with --default-character-set=latin1. If you dump without this, mysqldump should dump the with the original charset.

[pierre-hoffmann]

Hi,

* How to reduce import time ?
More mem, faster disks.

• 2 Go physical RAM (perhaps i can extend this...)
• EMC Clariion FC 15K Disks (no more ...)

Hmm not really. It should stay that way. On table reation you can pass a charset (which mysqldump already should do) but you negated with --default-character-set=latin1. If you dump without this, mysqldump should dump the with the original charset.

I' ve added "--default-character-set=latin1" cause of this problem of charset but it doesn't resolve it !!

Regards,
Pierre.

[pierre-hoffmann]

Hi,

No idea for my charset problem ??

Regards

[pierre-hoffmann]

Hi,

I'm go migrate Zabbix 1.4.6 to 1.6.2 and "per_table_file";
so make mysql export and import; and i've this result:

• Existing database : 26 Go / Export 1h20
• Imported database : 20 Go /import 31h00

Ooops 31h .... it's too long; what's doing to reduce that ...

My export batchs:

export_struct=/db/mysql/export/zabbix_struct.dmp
export_data=/db/mysql/export/zabbix_data.dmp

# Export de la structure
display_msg "Exporting structure [$(date)]"
mysqldump --no-data --complete-insert zabbix > ${export_struct}
display_ok
display_msg "-> $(ls -lap ${export_struct})\n"
display_msg "End export [$(date)]\n"

# Export des data
display_msg "Data export [$(date)]"
mysqldump --no-autocommit=TRUE --default-character-set=latin1 -t zabbix > ${export_data} # ${tables_list} > ${export_data}
display_ok
display_msg "-> $(ls -lap ${export_data})\n"
display_msg "End  export [$(date)]\n"

My import script:

# Export des data
print "+ Begin Import [$(date)]"
(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
echo "SET SQL_LOG_BIN=0;"
cat $export_data
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
echo "SET SQL_LOG_BIN=1;"
) | mysql -u root -p zabbix
print "+ End l'import [$(date)]"

Regards,
Pierre.

[xs-]

Well, for starters, these should give some performance increase

innodb_flush_method = O_DIRECT # only use for local disks, not SAN
innodb_buffer_pool_size = # (80% of total mem)
innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0

But what kind of storage hardware are you using? Local disk + raid card, NAS, SAN, DAS?
If DAS or local disks + raid card, try playing around with the read ahead and write policy.

[pierre-hoffmann]

Hi,

I'm Using EMC2 Clariion SAN with FC Disks ...

so i can add this options; only for import ??
innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0

Regards,

[xs-]

Well, is you are using FC SAN storage, you should be looking at optimizing the system itself, doesn't sound like mysql is the bottleneck.
There's plenty of information on how to optimize disk access via FC (plus which innodb_flush_method is best).

Dont know how to set those parameters for dump only, but imo you can use these in general for a zabbix db. Turning these off (worst case) would cause you to lose some transactions after crash. No biggie for measurement data.

[pierre-hoffmann]

Ooops,

I think i've found the probleme, import was made on test machine (not true server); and on it i've default value for "innodb_buffer_pool_size" ...

I've se in process_list ; an insert take about 9/11 seconds ....
and with good value of innodb_buffer_pool size an import take about 1 second


... import in progress

Regards,
Pierre.

[pierre-hoffmann]

Yes it work : only 3h40 for importing 20 Go
I go migrate to zabbix 1.6.2 and new Linux SLES version.

Thanks for your help !

Regards,
Pierre.

[alj]

Hi,


-> I'm affraid by

[[COLOR="Red"]!![/COLOR]] Key buffer hit rate: -1224.5%

But don't know why ...

Its because key buffer is not being used for innodb (only for myisam)