On Jul 25, 2005, at 5:33 AM, Marvin Wright wrote:
Hi,
Thanks for your reply.
I've only just moved all tables to there own table space so that I
can put
certain databases on different disks.
Right now my shared tablespace does not hold any databases.
I'm aware that I still need the shared table space but I don't need
200gb
now, I just want to decrease it down to 10Gb.
It seems a bit daft that I still have to dump all tables even when
they are
in their own tablespace. I guess this is because the table
definitions are
still stored in the shared space.
Marvin.
Hi! These are good questions... Heikki once told me that if there is
no activity going on AND the innodb status page shows nothing being
processed AND everything is up to date according to the innodb status
page, you could (in theory) shutdown mysql and bring it back with a
new shared table space under these circumstances... That is going to
require that every connection to the database server be idle, or
better still shut off... Depending on how your machines access your
database server that may be easy or hard to do...
We had some character set issues to work on and were (are - it's an
ongoing project) needing to do a dump and an import to do the move
from 4.0 to 4.1 at the same time... So we didn't actually try and
bounce a server into a smaller shared table space live... I have
total control over my client connections to the database server and
can easily prevent them from connecting with a hardware load
balancer, and I'm still not sure I would want to try that though.
Hint if you are going the dump and import route... The fastest way to
dump and for sure the fastest way to import is to use mysqldump --
tab=/var/tmp/somewhere and use mysqlimport to import the tab
delimited data... using --tab on the dump creates two files for each
table.. an sql file with the create table statement, and a txt file
with the tab delimited data... We create our databases using cat /var/
tmp/somewhere/*sql | mysql ourDatabase, and then use mysqlimport
ourDatabase /var/tmp/somewhere/*.txt - mysql import is smart enough
to insert data into tables matching the filename, it's the fastest
way to do the whole dump and import thing by a lot.
On the issue of how much shared space, Heikki told me 200Mbytes would
be far more than we would need if everything is
innodb_file_per_table... but as my old file space was made with 2000M
files I just kept ibdata01 and commented out the rest of the line....
certainly haven't any issues with the 2Gbyte shared table space, I
would think 10G would be overkill (I think my 2G is overkill).
The only other area we discovered was an issue is that if you are
running a 32 bit file system there is likely to be a a problem on any
table that needs more file space than the file system will give a
single file. The solutions here are to use a 64 bit file system which
doesn't care so much, or create a larger shared table space and turn
off innodb_file_per_table and alter the table to innodb (even if it
is already innodb, altering it like this will recreate it new). turn
on innodb_file_per_table again and that table will stay in the shared
table space, the rest will be in their own files. the main problem
here is that once the file reached the OS limit InnoDB thought the
table was full(which technically it was)... so Innodb's autoextending
files don't know how to launch a second file once the File system's
upper limit has been reached.
Best Regards, Bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
