Ware,
Thanks so much for your help.
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
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.
3. Shut down the mysql server
4. Edit my.cnf to remove "log-bin"
5. Edit my.cnf to add "innodb_file_per_table"
6. Move the tablespace ( I guess this is the ib_logfile0, ib_logfile1, and
ibdata1 files)
7. Start the mysql server. I think at this point the table space will be
recreated. Am I right?
8. Re-import my data from my file at step 1.
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?
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?
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?
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.
