On Feb 1, 2006, at 6:35 AM, Grant Giddens wrote:
This is what I'm planning on doing, please let me know if you see
any problems here. This is on my test server, so losing data isn't
the end of the world. This is what I plan on doing in order:
1. Backup the database via mysqldump
We've actually done this several times recently to convert table
spaces fully to innodb_file_per_table. Here's the mysqldump command
we use:
/usr/local/mysql/bin/mysqldump -u username -p --add-drop-table --add-
locks --create-options --single-transaction --disable-keys --extended-
insert --quick --quote-names --flush-logs --all-databases > /volumes/
raid/snapshot.sql
You should read about those options, mysqldump has a ton of them.
For systems with only InnoDB tables this will take a point in time
snapshot. You could probably get away with a simpler command on a
test server, but if you're going to use InnoDB in a real environment
you might as well do it this way. All the options are pretty
straight forward explanations.
2. Purge the master logs via "PURGE MASTER LOGS" command. Maybe I
should just delete them? It's a test server and I don't plan on
going back to a previous snapshot.
Yes, you might as well use e.g.
mysql>show master logs;
+-------------------+------------+
| Log_name | File_size |
+-------------------+------------+
| server-bin.000473 | 1073741985 |
| server-bin.000474 | 1074519940 |
...
| server-bin.000606 | 1074213492 |
| server-bin.000607 | 779776096 |
+-------------------+------------+
135 rows in set (0.01 sec)
to see what you have and then:
mysql>purge master logs to 'server-bin.000607';
Query OK, 0 rows affected (18.78 sec)
to do the purge. Again, might as well test how you'll do it on a
real server.
3. Shut down the mysql server
4. Edit my.cnf to remove "log-bin"
5. Edit my.cnf to add "innodb_file_per_table"
right
6. Move the tablespace ( I guess this is the ib_logfile0,
ib_logfile1, and ibdata1 files)
Yes, though they should be specified in your my.cnf file.
7. Start the mysql server. I think at this point the table space
will be recreated. Am I right?
Right, but before this I would make sure to explicitly define the
tablespace as you want it to be (I'm assuming you'll do that from
your comments from below, but just mentioning it) rather than keep
your old one.
8. Re-import my data from my file at step 1.
yes
This leads me to several questions though:
1. My web host server is running CentOS linux. How do I know it's
maximum filesize? Should I limit the ibdata1 type files such as:
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M
What if my table is larger than 2G?
Our only CentOS machine doesn't run MySQL, so I don't know what the
actual limit is. I suspect it's larger, but you'd want to know. If
you use the above (and I didn't know you could specify a max, but ok)
and the file fils up InnoDB would generate an error. It's the same
issue if you don't have autoextend.
However, you won't be using the shared tablespace for much at all if
you're using innodb_file_per_table. It only holds various shared
data, not the tables themselves. You still could have an issue if an
individual table grows that large, but it's less likely than in a
shared tablespace.
I think the answer is to find out what CentOS (or probably more
specifically your file system's) limit is. We're using OS X and have
a 56 GB table, so InnoDB can certainly handle huge files if the file
system can. Even if you run into the limit I don't believe (though
I've never tested it) you'll have corruption, just a database error
(which might be as bad in some applications).
2. If I change my tables (add/drop index and such) do I need to
do this mysqldump, recreate my tablespace and re-imporrt my data
process each time?
No. In fact a less intrusive way to move tables out of shared
tablespace is to issue:
alter table db.table type=innodb;
statements after adding innodb_file_per_table to the my.cnf and
restarting. This does a meaningless alter that essentially drops and
recreates the table and moves it out of the shared tablespace (b/c
the create occurs with innodb_file_per_table on).
However, thought this frees up space in your shared tablespace (so
other tables can grow without it expanding) you still can't delete it
or shrink it this way. The only way to do that is to delete and
recreate.
3. What if I'm running multiple databases with innodb tables on
one mysql server? I guess if I use innodb_file_per_table it will
help me keep the file sizes down and make it easier to drop and
reload individual databases. Am I correct here?
It will keep you from having a giant file (the shared tablespace)
that you can't delete or shrink. If you drop a table you actually
free up space on the disk.
However, it's still not like myisam where you can grab the files and
move them to another server. InnoDB still needs the shared
tablespace and logfiles in addition to the individual table's .ibd
file. Moving just the ibd file is useless.
But yes, in general innodb_file_per_table gives you some more
flexibility, just not as much as with MyISAM. For what it's worth
we've noticed no stability or speed issues with it and Heikki at one
point said he could think of no downside to using it.
Good luck,
Ware
Thanks,
Grant
Ware Adams <[EMAIL PROTECTED]> wrote: On Jan 31, 2006, at
9:54 PM, Grant Giddens wrote:
Since changing these tables, I've noticed some large files in my /
var/lib/mysql directory. This is on my test server and I'm running
gentoo linux.
The files in this directory look like:
/var/lib/mysql/gentoo1-bin.000001 (1 Gig in size)
/var/lib/mysql/gentoo1-bin.000001 (1 Gig in size)
/var/lib/mysql/gentoo1-bin.000001 (1 Gig in size)
/var/lib/mysql/gentoo1-bin.000001 (1 Gig in size)
...
/var/lib/mysql/gentoo1-bin.000060 (1 Gig in size)
These all look like binary log files, they aren't exclusive to
InnoDB. You must have enabled binary logging in your my.cnf file as
well.
This is covered pretty extensively in the manual. Basically they are
used for replication and recovery. In the latter you can take a
point in time snapshot from a known binary log position and then if
need be apply the logs (which is basically re-running the queries in
them) to that to get back to where you databases was at any point in
time.
If you don't need any of these functions you can either get rid of
the files or turn off binary logging. However, you probably don't
want to just delete them from the file system. Have a look at the
'show master logs' and 'purge master logs' commands. This will
delete the files and keep mysqld's internal index of the binary logs
accurate.
/var/lib/mysql/ibdata1 (10.0 Gig in size)
This is your InnoDB table space, you need it. You also need your
ib_logfile0 etc... files.
3. Can I limit the size of the ibdata1 file?
Only by limiting data in your tables. Also, in the shared table
space (which you are using) you can't shrink this file. You can
switch to innodb_file_per_table (see InnoDB manual) so that when you
drop a table you save it's space (but not on deletion of individual
records). However, even doing this you cannot delete your ibdata1
file or any shared tablespace files. You can recreate your entire
tablespace (see below), but even then you'll need a (small) shared
ibdata file.
Is it too late to resize it?
Yes, but you could use mysqldump to dump all data to text files,
delete (or move) the tablespace, redefine it and then re-import.
4. What can I set to reduce the size of these files?
Use innodb_file_per_table
turn off binary loggin if you don't need it
make sure index and field types are appropriate
My innodb variables are:
These came through poorly spaced, but I think it would help a lot to
read the InnoDB sections of the manual (it's pretty manageable in
size).
InnoDB is really fantastic for certain applications, including heavy
write load to large tables with concurrent reads. We've used it for
several years on several billion records with 170 qps 50% of which is
writes. There's no way to do this in MySQL other than InnoDB,
realistically.
That said, it has it's own learning curve. It's really an entirely
new database engine, so there's lots to learn even if you've used
mysql/myisam for years. In particular the tablespace layout and
dependency on files other than ones linked to a particular table is
a little daunting.
--Ware
---------------------------------
Bring words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
