InnoDb: how to install, how to copy/backup databases, how to change configurations

[ghost]

Reported by @frederikhabils on 18 Sep 2003 11:58 UTC I think it's worth to separate this issue, as it could be a long story by itself. It is being suggested as a technical prerequisite to solve bug 542 and bug 48 (e.g. bug 48 comment 7 and bug 48 comment 8 and bug 542 comment 0 and bug 542 comment 1)

questions

• would makumba only support innoDB, or also myIsam ?
• how will makumba handle other DBs that have no transactions?
• what are we gaining and what are we losing, when moving from myIsam to innoDB?

remarks:

• read http://www.mysql.com/doc/en/InnoDB.html and http://www.mysql.com/doc/en/InnoDB_restrictions.html

• "In Windows InnoDB stores the database names and table names internally always in lower case. To move databases in a binary format from Unix to Windows or from Windows to Unix you should have all table and database names in lower case. (7.5.15 mysql doc)". So we might choose to have makumba lowercase all table names (see also the (pro-) case-sensitivity discussion in bug 549)

Migrated-From: http://trac.makumba.org/ticket/581

[ghost]

Comment by @cristianbogdan on 19 Sep 2003 07:12 UTC i am playing with innodb and so no major differences. in fact, think mysql is configured by default to support both

another difference is that innodb represents floats differently on big-endian and little-endian platforms. as makumba doesn't have a float, this doesn't affect us now, but it will

for transaction issues that don't relate specifically to innodb, see you at bug 48

[ghost]

Comment by @cristianbogdan on 19 Sep 2003 07:55 UTC

• what are we gaining and what are we losing, when moving from myIsam to innoDB?

it may be that we're losing some performance (especially with a badly written BL). but i can't know, we have to test. we can do that once we have transactions implemented (see suggestion in bug 48 comment 10)

as far as i can tell, transactions are a huge gain. i'm sorry we didn't start with them earlier, though i understand that there were more pressing issues

[ghost]

Comment by @cristianbogdan on 23 Sep 2003 14:22 UTC i was just thinking that i don't have much to say/think of this bug when i realised that i managed to corrupt all my innodb databases by doing one stupid thing: deleting the innodb working files... even when mysqld is stopped, deleting those leads to disaster.

in my case, i just wanted to resize those files (by modifying my.cnf, see below) and innodb complained at startup. i thought that deleting them (to be re- created) will do, but it seems that i deleted one file too much or so. not shure which is to blame but doing rm data\ib* is certainly not something to do :). if you know which file is to blame, please post sth here...

conclusion: read carefully the manuals before passing to innodb

the innodb files look like C:>ls -l \utils\mysql\data\ib* -rw-rw-rw- 1 user group 25088 Sep 23 16:08 \utils\mysql\data\ib_arch _log_0000000000 -rw-rw-rw- 1 user group 4194304 Sep 23 16:10 \utils\mysql\data\ib_logf ile0 -rw-rw-rw- 1 user group 4194304 Sep 23 16:08 \utils\mysql\data\ib_logf ile1 -rw-rw-rw- 1 user group 18874368 Sep 23 16:10 \utils\mysql\data\ibdata1

files have those sizes after being configured (my.cnf) like below. note that mine is a very small-space configuration so that innodb will not take over all my machine...

innodb_data_home_dir = innodb_data_file_path = ibdata1:10M:autoextend:max:50M

set-variable = innodb_buffer_pool_size=5M set-variable = innodb_additional_mem_pool_size=2M

Set the log file size to about

25 % of the buffer pool size

set-variable = innodb_log_file_size=4M set-variable = innodb_log_buffer_size=3M

Set ..flush_log_at_trx_commit

to 0 if you can afford losing

some last transactions

innodb_flush_log_at_trx_commit=0

[ghost]

Comment by @cristianbogdan on 23 Sep 2003 17:07 UTC with my configuration above, taken the tequila karambasmall for transformation (via ant checkDb, see bug 591), i saw this on the log

[23-Sep-2003 18:36:59 org.makumba.db.sql.mysql.RecordManager alter
[java](java]) INFO: localhost_mysql_karambasmall: ALTER TABLE

best_johnnyApplication TYPE=InnoDB [23-Sep-2003 19:00:34 org.makumba.db.sql.FieldManager manageIndexes java INFO: INDEX DROPPED on best.johnny.Application#Application (Unique index)

basically for a large table, converting to innodb and dropping an index took over 20 minutes!!! it also broke two times because the InnoDb file was not large enough, now the configuration is

innodb_data_file_path = ibdata1:10M:autoextend:max:400M

i know my configuration is damn diskbound (i wanted the thing to take as little RAM as possible to let me work on my stuff) but this should also give you an idea about how InnoDb can be configured wrongly.

now i don't dare to change my other configs because then InnoDb might refuse to let mysqld start, saying that the file x or y don't have the right size

still waiting for my ant checkDb to finish, i think i'll hibernate my machine and go home :)

[ghost]

Comment by @cristianbogdan on 24 Sep 2003 01:10 UTC the InnoDb support on tequila seems to be already installed and configured, with a default configuration which may need a bit of adjustment to tequila's capabilities. so maybe somebody (Priit?) will take a look at that config and learn how to evolve the config without losing data (like i did in comment 3)

[ghost]

Comment by @cristianbogdan on 25 Sep 2003 08:21 UTC since copying mysql databases from one server to another (or even within the same server) is widely used practice in ITC, i think that before passing to InnoDb we will have to know clearly how databases can be copied, and experiment with copying

about the case-sensitivity issue at comment 0, makumba already (for a long time now) is case insensitive in db-level table names (because myIsam has problems with that too). but db-level table names are different from makumba-level type names.

[ghost]

Comment by @stefanb on 25 Sep 2003 13:22 UTC By accident I started my development karamba with latest makumba on TESTkaramba on tequila. On first http access it started converting tables to innodb as expected, but because it probably took longer than karamba wanted it served an error page.

I stopped tomcat, then initiated ant checkDB, monitoring df -k status all the time to stop it if production would be endangered Filesystem 1k-blocks Used Available Use% Mounted on /dev/md0 2104312 1302284 802028 62% / /dev/md1 6297144 5970060 327084 95% /var /dev/md2 1052120 394496 657624 38% /var/log /dev/md3 2104312 676972 1427340 33% /tmp /dev/md4 22249236 11643044 10606192 53% /home

mysqladmin -uroot processlist | 6604 | root | localhost:40493 | TESTkaramba | Query | 203 | copy to tmp table | ALTER TABLE general_archive_Document_content TYPE=InnoDB |

It was copying archive.document.content to a temporary table and /var partition usage climbed from regular 72% to 95% (or more), then it dropped back after table was converted.

All the time i was monitoring the disk use and was in standby to do mysqladmin -uroot kill

As /var usage reached 95% i decided to kill it, but it finished in the meantime (befoure i ran the mysqladmin command), with no appearant problems.

I don't have exact timing, because i also killed "ant checkDB" in the process, but my estimate is that the whole process of conversion took 10 minutes or so.

In later runs ant checkDb just checks every table in 20 seconds or so, with the only errors being the usual ones: extra fields and non-uniqness (as i have some unique dields in my MDDs)

[ghost]

Comment by @stefanb on 25 Sep 2003 13:41 UTC The problem after converting TESTkaramba to innodb is that most of the dynamic pages show an error:

java.sql.SQLException: Column not found, message from server: "Unknown column 'col2' in 'order clause'"

java.sql.SQLException: Column not found, message from server: "Unknown column 'col6' in 'group statement'"

java.sql.SQLException: Column not found, message from server: "Unknown column 'col3' in 'order clause'"

any ideas?

[ghost]

Comment by @gwenael-alizon on 25 Sep 2003 14:00 UTC Nice to have an idea of a realistic time for conversion on tequila However if there is a risk for production DB, I guess it is safer to go on with InnoDB tests on a DB hosted on tallinn's mySQL server? (I interpret like that the "By accident" from comment 7 :))

[ghost]

Comment by @stefanb on 25 Sep 2003 14:22 UTC after i converted some tables in first few seconds of accidental tomcat run i concluded that even bigger tables can be transformed with no harm, as long as mysql doesn't run out of space while doing so (i was checking that). And even if it did run out of space it would probably just halt production for a while, until some space is freed again (as has happened before).

[ghost]

Comment by @stefanb on 25 Sep 2003 15:01 UTC Would love to try al this on mysql on tallinn ... if i could only connect to it from my workplace on tequila.

There I created a new DB karambasmallinnodb for innodb testing purposes, but i keep getting stefan@tequila:~/bundle/sources/karamba$ ant checkDb Buildfile: build.xml

checkDb: [Sep 25, 2003 4:58:44 PM org.makumba.db.sql.Database java INFO: Makumba devel-20030925152542 INIT: jdbc:mysql://tallinn.best.eu.org/karambasmallinnodb [Sep 25, 2003 4:59:06 PM org.makumba.db.sql.Database logException java WARNING: Server connection failure during transaction. [Attemtped reconnect 3 times. Giving up. SQL state: 08001 error code :0

 [java](java]) java.sql.SQLException: Server connection failure during transaction.
 [java] Attemtped reconnect 3 times. Giving up.

[ghost]

Comment by @cristianbogdan on 25 Sep 2003 15:16 UTC comment 9 and comment 10: as Priit indicated in email and i documented in bug 591, tallinn's mysql is not innodb-enabled. a new one, preferably version 4 needs be installed, compiled for innodb support

Priit told me that Kuba said he could do that in some days, but i really think it would be a good idea if somebody else tried this before Kuba has time. mysql binaries are ready for download at mysql.com, there's no need for compilation unless you want super performance, which we don't need on tallinn

[ghost]

Comment by @stefanb on 26 Sep 2003 07:48 UTC strange problem, described in comment 8 was not because of converting to innoDB or any other MySQL problem. It happened because the development version of makumba had a glitch, fixed by cristi soon afterwards.

TESTkaramba is normally operational, in innoDB format.

But still, we do need innodb on tallinn for development.

[ghost]

Comment by @cristianbogdan on 26 Sep 2003 11:05 UTC about Innodb consuming diskspace: it is possible to configure it to store data files on more partitions (btw, innodb seems to have 2 main kinds of files: datafiles, used to store data and log files used at transactions)

http://www.mysql.com/doc/en/InnoDB_start.html

what bothers me most is that data files are not differentiated per mysql database (data from more databases seems to be mixed in the InnoDb datafile(s)). copying one database around becomes much more difficult (it seems that mysqldump or org.makumba.db.copy are the only solutions). this seems very strange to me, so maybe more study needs be done.

copying all databases is easier, simply copy the files...

http://www.mysql.com/doc/en/Moving.html

data files cannot be decreased in size, but things seem to be more flexible with log files. http://www.mysql.com/doc/en/Adding_and_removing.html

there are not so good news about backup: the backup utility is not free, and backing up without that requires mysqld shutdown (i'm not sure even myisam can be correctly backed up without server shutdown, but i think itc does it)

http://www.mysql.com/doc/en/Backing_up.html

[ghost]

Comment by @cristianbogdan on 26 Sep 2003 12:03 UTC about diskspace: InnoDB can also be configured to use "raw partitions" which i guess are dedicated partitions for InnoDB

about backup and transferring dbs: i came to think that copying databases as binary files is a hack anyway... dumping seems a good possibility as dumping production on tequila takes 5-6 minutes

didn't test how much un-dumping takes, but i remember i saw something like set autocommit=0 ... dump file... commit

which is supposed to be much faster as it doesn't commit after each record this will also convert the database to innodb in the process. only the new unique makumba indexes are missing, but those will be added by ant checDb

for the transaction above to work, you need to start mysqld like mysqld --default-table-type=InnoDB which i think is equivalent to putting in my.cnf default-table-type=InnoDB

[ghost]

Comment by @cristianbogdan on 27 Sep 2003 00:19 UTC i really think tests should be done with the database on tequila, to be as realistic as possible. so there's no problem that tallinn's mysql is not yet upgraded

parade's mak-test-k now has the latest makumba code and works with TESTkaramba.

coming to the questions in the bug title:

• how to copy/backup: i suggested via mysqldump. it's safest, platform independent, etc.
• install and change config: data file size cannot be decreased. the rest seems to be changeable. present configuration (the default) is below. can be changed in /etc/my.cnf

i comment a bit below. but maybe more should be found out...

mysql> show variables like 'innodb%'; +---------------------------------+------------------------+ | Variable_name | Value | +---------------------------------+------------------------+ | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | i guess both of the above (and other memory buffers) can be increased.

| innodb_data_file_path | ibdata1:10M:autoextend | this is why the data file grows as large as the partition, only to shrink later on. defining another data file on another partition is a simple way out from the diskspace problem that seems to grow

| innodb_data_home_dir | | | innodb_file_io_threads | 4 | | innodb_force_recovery | 0 | | innodb_thread_concurrency | 8 | | innodb_flush_log_at_trx_commit | 0 | this is unsafe, i think the default is 1 in later mysql versions

| innodb_fast_shutdown | ON | | innodb_flush_method | | | innodb_lock_wait_timeout | 50 | | innodb_log_arch_dir | ./ | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | +---------------------------------+------------------------+

[ghost]

Comment by Vilius@BEST.eu.org on 11 Jun 2004 21:58 UTC Cristi on 9GB inodb structure on "Re: InnoDB datafile size /Re: *** Errors on production" - 23-05-2004 ita@BEST.eu.org

about the current innodb databases/space on tequila, i would simply forget them. drop the 2 dbs, delete the datafile, the arch files and the logfiles, restart mysql, it will complain, then it will re-create innodb files and that's it.

[ghost]

Comment by @manuelbernhardt on 6 Jun 2009 11:00 UTC the issues expressed in this bug seem to be mastered since around 2005 by ITC. also, MySQL's innodb support/stability has improved a lot, in fact all ITC systems now run with INNODB.

about the copying part: as the karamba database takes around 3-4 GB (it used to take up to 12 when the files were still in the DB), the preferred method for backups is to use mysqldump, for replication and/or production server moving however, it is to simply copy the directories. the problem is that when importing such a big dump, some configuration is necessary in my.cnf (don't remember what it is now but the mysql configuration is saved on the BEST CVS)

so I'd say that this issue has FIXED itself in time - maybe also because ITC doesn't use myisam anymore and faces conversion problems.