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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
