[vox-tech] Need your help in recovering database.

[Jeff Newmiller]

On Sat, 26 Apr 2003, Soumyadeep nandi wrote:

> I couldn't retrieve the data from that database, now I
> am planing to set up a new database, as such I would
> request you if you could guide me in making the my.cnf
> file for better stability.

I have read the manual.  I have used InnoDB for small (50MB) databases. I
have NOT managed high-capacity InnoDB databases.  If you want more
reassurance than what the manual says, talk to someone else.

> I am using system of 256M memory, the alloted space
> for the database is 20GB, 32-bit processor. The server
> version is mysql-4.0.12(max) OS linux(redHat-9).

It seems odd to me that RH9 wouldn't have large file support enabled.  
Perhaps the mysqld binary you are using just isn't compiled to take
advantage of it.  Maybe you should install from source and the configure
script will recognize that LFS is available?

There is an interesting user comment regarding 4.0.12 at
http://www.mysql.com/doc/en/Full_table.html.

> Considering the above parameter I wrote the my.cnf
> file:
> 
> [mysqld]

[...]

> #                     Data files must be able to
> #                     hold your data and indexes
> innodb_data_file_path =
> /ibdata/ibdata1:100M:max:1990M:max:1990M;/ibdata/ibdata2:100M:max:1990M;/ibdata/ibdata3:100M:max:1990M:max:1990M;

This syntax sure doesn't look like the examples in the manual.

>From the manual:

: Starting from versions 3.23.50 and 4.0.2 InnoDB allows the last data
: file on the innodb_data_file_path line to be specified as
: auto-extending. The : syntax for innodb_data_file_path is then the
: following:
:
:  pathtodatafile:sizespecification;pathtodatafile:sizespec;...
:    ...;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]

So, it seems to me that

 /ibdata/ibdata1:1990M;/ibdata/ibdata2:1990M;/ibdata/ibdata3:1990M

is closer to what you are looking for.

They have an example for people concerned about filesize:

: Be cautious on file systems where the maximum file size is 2 GB! InnoDB
: is not aware of the OS maximum file size. On those file systems you
: might want to specify the max size for the data file: 
:   innodb_data_home_dir =
:     innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

but if you want more than 2GB of data then only the LAST file can be
autoextending... which isn't much of a big help.  (Gee, you can have 19GB
of file on disk instead of 20GB when you have 18.5GB of data... big deal.)

> # I want to make multiple file rather 
> # autoextending the file, also I want give the upper
> # limit here. Please advice am I right here.
> 
> #                      Set buffer pool size to
> #                      50 - 80 % of your computer's
> #                      memory
> set-variable = innodb_buffer_pool_size=140M
> set-variable = innodb_additional_mem_pool_size=20M

>From the manual:

: Warning: on Linux x86 you must be careful you do not set memory usage
: too high. glibc will allow the process heap to grow over thread stacks,
: which will crash your server. It is a risk if the value of 
:    innodb_buffer_pool_size + key_buffer +
:    max_connections * (sort_buffer + record_buffer) +
:    max_connections * 2MB
: is close to 2 GB or exceeds 2 GB. Each thread will use a stack (often
: 2MB, but in MySQL AB binaries only 256 kB) and in the worst case also
: sort_buffer + record_buffer additional memory. 

You might want to estimate your worst-case memory image usage from this. I
think you are still okay with respect to the heap corruption, but you may
be looking at a lot of swap space usage (do you even have enough?) unless
you lower your max_connections or raise your installed memory.

[...]

---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil@dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...2k
---------------------------------------------------------------------------