Hello Heikki,
Friday, February 23, 2001, 6:51:33 PM, you wrote:
HT> Peter and Sander,
HT> relevant questions, I will try to answer them.
>>Good questions - I have a few more :)
>>A) why does it seem to use fixed-size storage units. (The files)
HT> I have copied the concept of a tablespace consisting of a small
HT> number of files from Oracle. When the database itself manages
HT> disk space, it can reduce disk fragmentation by using its own
HT> file space allocation algorithm. Innobase uses an algorithm similar
HT> to the Fast File System used in some Unixes. Also, one can use
HT> raw disks to totally eliminate the disk fragmentation caused by the
HT> operating system.
Here is the comming question - can you speak some more about apace
allocation consepts used in innobase. For example how would it like to
work with huge number (10.000+ of the tables) and how space allocation
is done - I know innobase supports clusterisation, but if I'm not
mistaken only for rows from one table. The next thing is is there a
difference made in handling dynamic and static rows and how
fragmentation is avoided for last ones ?
As far as I know some space allocation problems are qute different in
file systems and in databases, so I'm surprising you're speaking about
similar algorithm.
Other thing is - does innobase supports hash indexes internally ?
Other thing - can I not add files but increase it size ? How
innobase will handle if I'll do so ?
>>B) what happens when they ar full?
HT> You have to shut down the database and add a new file to the
HT> configuration file my.cnf.
>>C) can it auto-create new files as demand grows?
HT> Not currently. I think no database currently can auto-create
HT> disk drives either :).
Few words about creation. As I looked at innobase files they are
mostly zero filled while created. I think it would be nice to add a
possibility to create files not by write() but by lseek() this will
make creation faster and files taking less space on file system which
support holes in files - last one is good as you can just create huge
files in the very begining and do not care much about wasting the
space.
>>D) can you safely add new files when there is data in them already?
HT> Yes you can, but you have to shut down the database first.
Shurely special command may be added for this later :)
Other thing is - don't you think it's not quite good idea to store
database configuration in config file. For now it's quite simple and
can be easyly recovered by looking at the files anf their sizes but
then you will have more complex configuration (i.e several tablespaces
with external mirroring) it will be hard to recover.
Other question - files sizes. Does innobase itself support 4GB+ files?
>>Sander> -----Original Message-----> From: Peter Zaitsev [mailto:[EMAIL PROTECTED]]
>> Sent: 22 February 2001 19:52> To: [EMAIL PROTECTED]> Subject: Innobase> >
>> Hello mysql,>
>> Today I got a chance to compile mysql 3.23.34 with innobase, so
>> althought it's not out yet I'll give some of my comments>
>> The version I'm speaking about got from work.mysql.com>
>> 1) It does not even configure then trying to configure
>> --with-innobase-db the problem is autoconf is not called in innobase
>> directory so configure script is not created
HT> In the source distribution there will be the files generated by
HT> autoheader etc., you will not need to generate them in your machine.
Well of course :)
>> 2) innobase_data_home_dir used as prefix, so if we'll not end it
>> with "/" we'll have files prefixed by directory name created in
>> upper level directory. This may be expected behavior but if so it
>> should be described in the manual.
HT> I have to modify the code so that it adds the '/' or '\'.
>> 3) Data files somehow are created with "x" attribute which I think
>> is not quite right
>> drwxr-xr-x 19 root root 366 Feb 22 21:32 ..
>> -rwxrwx--x 1 root root 1073741824 Feb 22 21:36 ibdata1
>> -rwxrwx--x 1 root root 1073741824 Feb 22 21:08 ibdata2
>> -rwxrwx--x 1 root root 1073741824 Feb 22 21:09 ibdata3
>> -rwxrwx--x 1 root root 1073741824 Feb 22 21:09 ibdata4
HT> Sorry, I will fix that. You are not supposed to execute database
HT> data files :).
>> 4) Currently ATIS test fails with innobase table:> Retrieving data
>> Warning: Query 'select
>> city.city_name,state.state_name,city.city_code from state,city
>> where city.state_code=state.state_code' returned 1 rows when it
>> should have returned 11 rows> Got error: when executing select
>> flight.flight_code,aircraft.aircraft_type from flight,aircraft
>> where flight.aircraft_code=aircraft.aircraft_code> got 0 instead of 579 ***>
HT> You may have an outdated version of the MySQL source distribution.
HT> (I am currently exchanging lots of emails with Peter and looking at the
HT> problem.)
I've currently patched a version but still get the same error. Other
tests (i.e big tables) now passes.
>> 5) Then started first time mysql creates innobase datafiles long
>> during startup which I think should be also mentioned in the manual -
>> I thought it's hanged up.>
HT> To avoid file fragmentation done by the operating system, Innobase
HT> physically writes its data files full at database creation, so that
HT> the space is physically occupied.
Yes of couse. But I think this should be mentioned in the manual so
users not surprised mysqld is doing something several minutes after
startup before it's ready.
>> 6) There is currently a serious lack of documentation - is there any
>> additional information about innobase tables ? For example how does
>> tables distributed over data files (I'e if putting them on different
>> disks will improve perfomance) Other thing is what should I do if I
>> somehow lose one file - will I be able to recover data at least
>> partitially ? What is idea ? Is it better to make small files or one
>> big file if you OS supports this ? How may I backup table ? Only
>> with mysqldump ?
HT> I am writing more documentation, forgive me the sparseness. Your
HT> questions will also influence the documentation.
OK. So I'll try not to ask stupid questions :)
HT> Currently, user has no control over how tables are stored in the data files,
HT> because the data files form a single tablespace. In future you will
HT> be able create several tablespaces like in Oracle, and specify the
HT> tablespace where the table is stored.
Do you have plans for external RAID support I.E stripping and
mirroring. At leas in row device configuration I thing this may give
better perfomance then OS/Hardware RAID.
HT> For best performance, you should create big files, so that disk
HT> seeks are minimized.
HT> If you lose a file, you must do an archive recovery from a backup,
HT> using archived log files of MySQL, (not log files of Innobase).
This is a problem. Currently mysql does not have a feature for online
backup (planed for 4.0) - so to make a real consistent snapshot for my
my mysql tables (20GB per server) I need to lock system for about half
an hour which is not acceptable in 7/24 mode.
Other thing is log files - it may take qute long time to process then
(additional problems there is only one log file stream) in my case I
tries to recover with 3 day logs and it took about 3 days :)
HT> Currently, to take a backup you have to shut down the database,
HT> check that it shuts down without errors, and copy your whole data
HT> files to a backup place. There is no incremental backup currently,
HT> nor a hot (online) backup. The support for a hot backup is built in,
HT> but there currently is no user interface for it.
Good. This sounds good.
HT> To backup an individual table you have to dump it. It is a good idea
HT> to take dumps now and then anyway, because a logical text file is
HT> always your safest backup of a database table.
Yes. But it's slow for big tables and even more slow on recover.
>> 7) Currently No check repair tools are provided which would be real
>> showstopper for production usage even if the tables will be quite
>> stable - what should I do if I have file system dammage so I need to
>> check the things up. Here we have even more problems as many tables
>> share one file so we probably can't just check them one by one.
HT> I will have to write CHECK TABLE (in Oracle called ANALYZE). I am not
HT> sure if repair table is a good idea, Oracle DBA manual at least does
HT> not mention such a procedure for Oracle. If the database gets
HT> corrupted, then the safest method is to recover it from a backup which
HT> is old enough so that corruption does not occur in the backup. Or recover
HT> the tables from dumps and MySQL archived log files.
That's bad idea to look at the oracle in this place. Look not only at
Hi End but in low end also. In my case with mysql most dammages are
only affwct indexes so recoverty is possible even without data lost.
Other thing is not everyone have complete backup (data + whole
logfiles) so I think that's not quite a good idea to skip recovery
tool. It's like with filesystems - you usually do backup but I don't
think you would like if you will not be able to fix you file system if
it dammaged.
The problem here is like in file system you can't just check one file
on file system you have to check the whole file system as there could
be cross links or bitmap errors. So You'll probably will have to
divide the physical corect state (allocation and such stuff) which is
done on file level and logically correct (i.e index matches all rows),
there the last one can be fixed on table level. Making the difference
could help as it would allow to recover one tables while others are
online.
Other thing - do you have any flags like MYISAM has - like crashed and
so on ?
>> 8) As I understand innobase works with large files self mannaging
>> usage in them - so Is it planned to support RAW devices with innobase
HT> Yes, Innobase will support raw disks. I have to test it still.
Good. This should really give much perfomance.
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
