Re: INNODB Questions and Optimization help

[Gleb Paharenko]

Hello.

Have a look here:
  http://lists.mysql.com/mysql/194596
  http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html

If you feel uncomfortable with 10G ibdata size, you may want to
switch to per-file tablespace:
  http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html
  http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html


Shaun Adams wrote:
 I currently have a customer who we switched over to INNODB from MyISM.  It's
 a Dell Server with 4GB or Memory and RHEL4 64-bit.  It's a fairly big
 database the size of the MyISM folders (before we converted to INNODB)
 was about 2-3Gigs.
  
 Questions:
  
 1. The ibdata1 file size is 10GB.  Does that sound right?  Should this file
 be this big?  
  
 2. Once a week, I have to perform HUGE insert imports into the database.
 What is the recommended procedure for doing this?  Clearing out memory with
 a FLUSH before I start the import, setting innodb_flush_log_at_trx_commit to
 0... any other suggestions
  
 3. Could you all recommend any tips you have used to increase performance
 using INNODB (from a system admin's perspective).
  
 4. Finally, could you take a look at my settings below as well as the system
 variables and recommend any changes.
  
 Thanks.  I would appreciate as many responses as possible.
  
 Shaun
  
 =
 060207 19:04:03 INNODB MONITOR OUTPUT
 =
 Per second averages calculated from the last 16 seconds
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 11216, signal count 11214
 Mutex spin waits 31985, rounds 62213, OS waits 135
 RW-shared spins 21945, OS waits 10884; RW-excl spins 61, OS waits 14
 
 TRANSACTIONS
 
 Trx id counter 0 110703900
 Purge done for trx's n:o  0 110703871 undo n:o  0 0
 Total number of lock structs in row lock hash table 0
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 0 0, not started, process no 6297, OS thread id 1147570528
 MySQL thread id 66, query id 10060 localhost root
 show INNODB status
 ---TRANSACTION 0 0, not started, process no 6297, OS thread id 1147771232
 MySQL thread id 2, query id 2305 10.200.60.34 admin
 
 FILE I/O
 
 I/O thread 0 state: waiting for i/o request (insert buffer thread)
 I/O thread 1 state: waiting for i/o request (log thread)
 I/O thread 2 state: waiting for i/o request (read thread)
 I/O thread 3 state: waiting for i/o request (write thread)
 Pending normal aio reads: 0, aio writes: 0,
  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
 Pending flushes (fsync) log: 0; buffer pool: 0
 27717 OS file reads, 2528 OS file writes, 339 OS fsyncs
 47.93 reads/s, 18477 avg bytes/read, 87.81 writes/s, 14.19 fsyncs/s
 -
 INSERT BUFFER AND ADAPTIVE HASH INDEX
 -
 Ibuf for space 0: size 1, free list len 68, seg size 70,
 755 inserts, 793 merged recs, 733 merges
 Hash table size 4980539, used cells 263653, node heap has 401 buffer(s)
 6790.51 hash searches/s, 852.82 non-hash searches/s
 ---
 LOG
 ---
 Log sequence number 5 1118786578
 Log flushed up to   5 1118786578
 Last checkpoint at  5 1118786578
 0 pending log writes, 0 pending chkp writes
 553 log i/o's done, 9.31 log i/o's/second
 --
 BUFFER POOL AND MEMORY
 --
 Total memory allocated 2761248304; in additional pool allocated 6053120
 Buffer pool size   153600
 Free buffers   94978
 Database pages 58221
 Modified db pages  0
 Pending reads 0
 Pending writes: LRU 0, flush list 0, single page 0
 Pages read 58209, created 12, written 1996
 55.00 reads/s, 0.00 creates/s, 76.31 writes/s
 Buffer pool hit rate 998 / 1000
 --
 ROW OPERATIONS
 --
 0 queries inside InnoDB, 0 queries in queue
 Main thread process no. 6297, id 1147169120, state: waiting for server
 activity
 Number of rows inserted 171, updated 235, deleted 0, read 1793591
 0.87 inserts/s, 1.00 updates/s, 0.00 deletes/s, 9788.39 reads/s
 
 END OF INNODB MONITOR OUTPUT
 
  
  
  
 
 SHOW VARIABLES
 
 
 Variable_name Value 
 back_log 50 
 basedir /usr/local/mysql-standard-4.0.26-unknown-linux-gnu-x86_64-glibc23/ 
 binlog_cache_size 32768 
 bulk_insert_buffer_size 8388608 
 character_set latin1 
 character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620
 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew
 win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257
 latin5 
 concurrent_insert ON 
 connect_timeout 5 
 convert_character_set   
 datadir /var/lib/mysql/ 
 default_week_format 0 
 delay_key_write ON 
 delayed_insert_limit 100 
 delayed_insert_timeout 300 
 delayed_queue_size 1000 
 flush OFF 
 flush_time 0 
 ft_boolean_syntax + -()~*:| 
 ft_max_word_len 254 
 ft_max_word_len_for_sort 20 
 ft_min_word_len 4 
 

Re: INNODB Questions and Optimization help

[Brandon Ooi]

Shaun Adams wrote:

1. The ibdata1 file size is 10GB.  Does that sound right?  Should this file
be this big?  
  
That sounds right. Innodb seems to incur large space overheads. but with 
the cost of diskspace nowadays...
 
2. Once a week, I have to perform HUGE insert imports into the database.

What is the recommended procedure for doing this?  Clearing out memory with
a FLUSH before I start the import, setting innodb_flush_log_at_trx_commit to
0... any other suggestions 
  
Yeah the flush log at trx_commit is good. remember that if the machine 
crashes you gotta retry the last second of txns before the crash. Also, 
turn of autocommit and surround your query(s) with SET AUTCOMMIT=0 and 
COMMIT;  If the db crashes while you're doing a lot of inserts, the 
rollback can take FOREVER. and i do mean forever. check the mysql manual 
on how to avoid this.


In general try not to do mass inserts at any given time. Perhaps build a 
queue and stream rows into the database as you need them.



3. Could you all recommend any tips you have used to increase performance
using INNODB (from a system admin's 
look into replication if you need a lot of performance. otherwise just a 
lot of memory (if you have more than 4GB you'll need a 64bit OS). scsi 
(or otherwise high rotational) drives tend to work faster. If the 
database is 99% reads and you need all the performance you can get, i 
think MyISAM is a better option.
 
4. Finally, could you take a look at my settings below as well as the system

variables and recommend any changes.
  

Looks alright. i think the biggest thing is having a large innodb log file.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: INNODB Questions and Optimization help

[Dathan V. Pattishall]

:~ -Original Message-
:~ From: Shaun Adams [mailto:[EMAIL PROTECTED]
:~ Sent: Tuesday, February 07, 2006 4:32 PM
:~ To: mysql@lists.mysql.com
:~ Subject: INNODB Questions and Optimization help
:~
:~ Questions:
:~ 
:~ 1. The ibdata1 file size is 10GB.  Does that sound right?  Should this
:~ file
:~ be this big?

Yes, the ibdata file contains the index and the data in the same
space (unless you use 4.1+ and use separate table spaces).
Text/blob data is doubled the actual size in most cases, so expect to see
innodb use a lot of disk space if you continue to store blob data in innodb.


:~ 
:~ 2. Once a week, I have to perform HUGE insert imports into the database.
:~ What is the recommended procedure for doing this?  Clearing out memory
:~ with
:~ a FLUSH before I start the import, setting
:~ innodb_flush_log_at_trx_commit to
:~ 0... any other suggestions

Use LOAD DATA INFILE increase your INNODB BUFFER POOL to about 2-3GB if you
can afford it, and disable keys on the table.

Also LOAD DATA in the ORDER that the table is ordered. INNODB supports
CLUSTERED indexes. That means if your table has a PRIMARY KEY of 

A,B

The data should be sorted A,B before using LOAD DATA


:~ 
:~ 3. Could you all recommend any tips you have used to increase
:~ performance
:~ using INNODB (from a system admin's perspective).

Best perf improvements is to look at your indexes and see if queries
are using them correctly. Other then that from a sysadmin point of view, use
O_DIRECT, turn on noatime, and build the drive using these options

/sbin/mkfs.ext3 -i 131072 -m 1 -L MYSQL /dev/.../ your drive

Also set swappiness to 0 in /proc/sys/vm/
 

:~ 
:~ 4. Finally, could you take a look at my settings below as well as the
:~ system
:~ variables and recommend any changes.

 Set innodb_buffer_pool_size=2G
 Set innodb_thread_concurrency=32 // this is good if you do a lot of small
fast queries
 Set innodb_log_file_size=512M
 
Your doing a bunch of table scans check you sql, and make sure your using
indexes.

:~ Handler_read_rnd 2025997
:~ Handler_read_rnd_next 487643


This is how I was able to tell.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: innodb questions

[Grant Giddens]

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.01  (1 Gig in size)
 /var/lib/mysql/gentoo1-bin.01  (1 Gig in size)
 /var/lib/mysql/gentoo1-bin.01  (1 Gig in size)
 /var/lib/mysql/gentoo1-bin.01  (1 Gig in size)
 ...
 /var/lib/mysql/gentoo1-bin.60  (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.

Re: innodb questions

[Ware Adams]

On Feb 1, 2006, at 6:35 AM, Grant Giddens wrote:

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


We've actually done this several times recently to convert table  
spaces fully to innodb_file_per_table.  Here's the mysqldump command  
we use:


/usr/local/mysql/bin/mysqldump -u username -p --add-drop-table --add- 
locks --create-options --single-transaction --disable-keys --extended- 
insert --quick --quote-names --flush-logs --all-databases  /volumes/ 
raid/snapshot.sql


You should read about those options, mysqldump has a ton of them.   
For systems with only InnoDB tables this will take a point in time  
snapshot.  You could probably get away with a simpler command on a  
test server, but if you're going to use InnoDB in a real environment  
you might as well do it this way.  All the options are pretty  
straight forward explanations.


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.


Yes, you might as well use e.g.

mysqlshow master logs;
+---++
| Log_name  | File_size  |
+---++
| server-bin.000473 | 1073741985 |
| server-bin.000474 | 1074519940 |

...

| server-bin.000606 | 1074213492 |
| server-bin.000607 |  779776096 |
+---++
135 rows in set (0.01 sec)

to see what you have and then:

mysqlpurge master logs to 'server-bin.000607';
Query OK, 0 rows affected (18.78 sec)

to do the purge.  Again, might as well test how you'll do it on a  
real server.



3.  Shut down the mysql server
4.  Edit my.cnf to remove log-bin
5.  Edit my.cnf to add innodb_file_per_table


right

6. Move the tablespace ( I guess this is the ib_logfile0,  
ib_logfile1, and ibdata1 files)


Yes, though they should be specified in your my.cnf file.

7.  Start the mysql server.  I think at this point the table space  
will be recreated.  Am I right?


Right, but before this I would make sure to explicitly define the  
tablespace as you want it to be (I'm assuming you'll do that from   
your comments from below, but just mentioning it) rather than keep  
your old one.



8.  Re-import my data from my file at step 1.


yes


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?


Our only CentOS machine doesn't run MySQL, so I don't know what the  
actual limit is.  I suspect it's larger, but you'd want to know.  If  
you use the above (and I didn't know you could specify a max, but ok)  
and the file fils up InnoDB would generate an error.  It's the same  
issue if you don't have autoextend.


However, you won't be using the shared tablespace for much at all if  
you're using innodb_file_per_table.  It only holds various shared  
data, not the tables themselves.  You still could have an issue if an  
individual table grows that large, but it's less likely than in a  
shared tablespace.


I think the answer is to find out what CentOS (or probably more  
specifically your file system's) limit is.  We're using OS X and have  
a 56 GB table, so InnoDB can certainly handle huge files if the file  
system can.  Even if you run into the limit I don't believe (though  
I've never tested it) you'll  have corruption, just a database error  
(which might be as bad in some applications).


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?


No.  In fact a less intrusive way to move tables out of shared  
tablespace is to issue:


alter table db.table type=innodb;

statements after adding innodb_file_per_table to the my.cnf and  
restarting.  This does a meaningless alter that essentially drops and  
recreates the table and moves it out of the shared tablespace (b/c  
the create occurs with innodb_file_per_table on).


However, thought this frees up space in your shared tablespace (so  
other tables can grow without it expanding) you still can't delete it  
or shrink it this way.  The only way to do that is to delete and  
recreate.


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?


It will keep you from having a giant file (the shared tablespace)  
that you can't delete or shrink.  If you drop a table you actually  
free up space on the disk.


However, it's still not like myisam where you can grab the files and  

Re: innodb questions

[Ware Adams]

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.01  (1 Gig in size)
/var/lib/mysql/gentoo1-bin.01  (1 Gig in size)
/var/lib/mysql/gentoo1-bin.01  (1 Gig in size)
/var/lib/mysql/gentoo1-bin.01  (1 Gig in size)
...
/var/lib/mysql/gentoo1-bin.60  (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]

Re: Innodb Questions

[Michael Stassen]

Dan Tappin wrote (quotes from several posts, my thoughts interspersed):

 I am running into repeatable table corruption with MySQL 4.x on Mac OS X
 10.x.

Many people, myself included, are running mysql 4.x on OS X without reporting 
this sort of problem, so the trick is to find out what your installations have 
that the rest of us don't have.


 All my systems have different OS's, versions of MySQL and PHP.  They all
 have UPS's.  The old 10.2 system (soon to be retired) has only a single
 drive.  The new 10.4 system has mirrored drives on a RAID set- up.  The
 10.3 install (recently wiped) had the same set-up.  I think I can rule
 out a hardware issue and a OS / MySQL issue.  It even show up on tables
 I never interact with directly i.e. my Moveable Type db for my blogs.  I
 create an entry and the next day it's dropped out of the database.

 The part that keeps scaring me is that I have seen this on 10.2, 10.3
 and 10.4 all with separate hardware.  My recent build has 2 drive on a
 mirror.  I find it hard to believe that I have been that unlucky to
 have drive failures / failed filesystems on ALL these machines.

Repeated table corruption often points to an underlying hardware or OS issue, 
as others have pointed out, but as you have the same behavior on a variety of 
platforms, I think you are right that this is unlikely in your case.  The 
question is, what do your systems have in common?  In particular, what do they 
have in common that working systems don't have?


 I previously had a MySQL install on 10.2 Client under 3.x and never had
 an issue or any major problems at all.  I upgraded to MySQL 4.x and
 have subsequently installed MySQL 4.x (from the supplied pkg's) on my
 10.2 client, a 10.3 Server and 10.4 Server (Tiger) install.

 Well Apple does not really have a front end for MySQL at all. I've gone
 away from Apple's updates and install my own version from the source
 packages provided by MySQL.

You've said you install using supplied pkg's, but you've also said you install 
from the source.  Let's be precise: Are you installing MySQL's pre-compiled 
binary distribution for OS X (the installer package which comes as a .dmg), or 
are you compiling from source?  I expect you are using the installer package, 
but if you are building from source, you should post your configure options. 
Both methods work, but if you are compiling it yourself, it could be something 
different in your configuration which is causing your problem.


 The typical application here was standalone servers running MySQL,
 Apache and PHP 4/5 running a hand full of small websites.  Things run
 along fine until with out warning my PHP / MySQL queries fail returning
 no data when there should be.

To be clear, your queries succeed, in that they do not return errors from 
mysql, but they do not return expected data, correct?  Do the same queries 
produce the same result from the mysql client?  What makes you think that is 
table corruption, rather than missing data?  Corrupt tables usually produce 
error messages to that effect.  (See the manual for a list 
http://dev.mysql.com/doc/mysql/en/repair.html.  Also see 
http://dev.mysql.com/doc/mysql/en/corrupted-myisam-tables.html.)


 As a temporary fix I created a cron job to run:

 /usr/local/mysql/bin/myisamchk -ov

 on all my tables every 4 hours.

You are shutting down mysqld every 4 hours?  Or are you running myisamchk 
while the server is running?


 This fixes the data issues but the problem is I am losing data:

 - recovering (with keycache) MyISAM-table '/var/mysql/data/xxx.MYI'
 Data records: 17
 Data records: 14

 Now this was last night at midnight.  I recall manually adding the 3
 records that evening and the previous cron job had only 14 records.  It
 seems that the fix is flushing my new data down the toilet.

 Here is the full myisamchk output on the subject table (after adding my
 data back in):
snip
 Poof!... gone again.

So, myisamchk is dropping *recently added* data (rows inserted since the 
previous invocation of myisamchk)?


 I then re-inserted my data again, ran a 'REPAIR
 TABLE...' directly from the client and the data seems to stick.

If I understand correctly, REPAIR TABLE finds no problems?

 I jump through these hoops each time and the problem seems to go away and
 then out of the blue this comes back.  It's driving my crazy.

 Any ideas at all out there?  Flush tables?  A different repair system?

The recommended method is to run CHECK TABLE in the client, then REPAIR TABLE 
if necessary http://dev.mysql.com/doc/mysql/en/repair.html, If you are 
using MySQL 3.23.16 and above, you can (and should) use the CHECK TABLE and 
REPAIR TABLE statements to check and repair MyISAM tables.


That same page also states, If you are going to repair a table from the 
command line [using myisamchk], you must first stop the mysqld server. Note 
that when you do mysqladmin shutdown on a remote server, the mysqld server is 
still alive for a while after 

Re: Innodb Questions

[Dan Tappin]

On Jul 24, 2005, at 9:13 AM, Michael Stassen wrote:

Dan Tappin wrote (quotes from several posts, my thoughts  
interspersed):


 I am running into repeatable table corruption with MySQL 4.x on  
Mac OS X

 10.x.

Many people, myself included, are running mysql 4.x on OS X without  
reporting this sort of problem, so the trick is to find out what  
your installations have that the rest of us don't have.


This is also part of the frustration.  If Macintouch or the OS X  
server admin list was full of MySQL issues I would actually feel better.


Repeated table corruption often points to an underlying hardware or  
OS issue, as others have pointed out, but as you have the same  
behavior on a variety of platforms, I think you are right that this  
is unlikely in your case.  The question is, what do your systems  
have in common?  In particular, what do they have in common that  
working systems don't have?


That's the big frustration.  I'm also thinking now that I am the  
common thread i.e operator error.


You've said you install using supplied pkg's, but you've also said  
you install from the source.  Let's be precise: Are you installing  
MySQL's pre-compiled binary distribution for OS X (the installer  
package which comes as a .dmg), or are you compiling from source?   
I expect you are using the installer package, but if you are  
building from source, you should post your configure options. Both  
methods work, but if you are compiling it yourself, it could be  
something different in your configuration which is causing your  
problem.


Sorry to be clear I'm using the installer package - 4.1.13 as of  
today on both suspect systems.


To be clear, your queries succeed, in that they do not return  
errors from mysql, but they do not return expected data, correct?   
Do the same queries produce the same result from the mysql client?   
What makes you think that is table corruption, rather than missing  
data?  Corrupt tables usually produce error messages to that  
effect.  (See the manual for a list http://dev.mysql.com/doc/mysql/ 
en/repair.html.  Also see http://dev.mysql.com/doc/mysql/en/ 
corrupted-myisam-tables.html.)


 As a temporary fix I created a cron job to run:

 /usr/local/mysql/bin/myisamchk -ov

 on all my tables every 4 hours.

You are shutting down mysqld every 4 hours?  Or are you running  
myisamchk while the server is running?


While running.


 This fixes the data issues but the problem is I am losing data:

 - recovering (with keycache) MyISAM-table '/var/mysql/data/ 
xxx.MYI'

 Data records: 17
 Data records: 14

 Now this was last night at midnight.  I recall manually adding the 3
 records that evening and the previous cron job had only 14  
records.  It

 seems that the fix is flushing my new data down the toilet.

 Here is the full myisamchk output on the subject table (after  
adding my

 data back in):
snip
 Poof!... gone again.

So, myisamchk is dropping *recently added* data (rows inserted  
since the previous invocation of myisamchk)?


Yes... even when I wasn't running a regular myisamchk.  For example:  
I have the movable type blog installed on my 10.4 Server system.  I  
create a new blog entry or a new category.  Later that hour, day or  
week subsequent queries will fail and I'll get the ever wonderfull  
'got error 127 from storage engine' error.  Manual SELECT * FROM  
mt_category etc will trigger this.  A myisamchk will only seem to  
work once I run a REPAIR TABLE mt_category etc.


The recommended method is to run CHECK TABLE in the client, then  
REPAIR TABLE if necessary http://dev.mysql.com/doc/mysql/en/ 
repair.html, If you are using MySQL 3.23.16 and above, you can  
(and should) use the CHECK TABLE and REPAIR TABLE statements to  
check and repair MyISAM tables.


That same page also states, If you are going to repair a table  
from the command line [using myisamchk], you must first stop the  
mysqld server. Note that when you do mysqladmin shutdown on a  
remote server, the mysqld server is still alive for a while after  
mysqladmin returns, until all queries are stopped and all keys have  
been flushed to disk.


Another manual page http://dev.mysql.com/doc/mysql/en/crash- 
recovery.html says, If you run mysqld with --skip-external- 
locking..., the default on OS X, ...you can't reliably use  
myisamchk to check a table when mysqld is using the same table.   
It goes on to say, If you use myisamchk to repair or optimize  
tables, you must always ensure that the mysqld server is not using  
the table (this also applies if you are using --skip-external- 
locking). If you don't take down mysqld, you should at least do a  
mysqladmin flush-tables before you run myisamchk.  Your tables may  
become corrupted if the server and myisamchk access the tables  
simultaneously.


I am suspicious that you are creating your own problem.  You report  
that mysqld is not crashing, nor is it complaining of crashed/ 
corrupted tables. Your only symptom is 

Re: Innodb Questions (was: Mac OS X Table Corruption)

[Ware Adams]

On Jul 23, 2005, at 11:56 AM, Dan Tappin wrote:

I am all most ready to give up on MySQL at this point.  I'm still  
getting regular table corruption on multiple installs of OS X.


I went as far as reporting it as a bug:

http://bugs.mysql.com/bug.php?id=12066

They seem to want more info but my requests for clarification have  
gone unanswered.


Has your mysql crashed or have your restarted the machine without  
first shutting down mysql manually?  We only saw this error when  
mysql was not shut down normally.  You can look in your .err file  
which should be in your data directory.  Every mysql sartup sequence  
should be preceded by a line saying mysqld ended.  If not it's  
crashing and that's the issue to address.



2.) Try Innodb


This is a MyISAM error, but if mysqld is crashing or being shutdown  
abnormally InnoDB too will have to go through a crash recovery at  
startup (it's just automatic, but you'll see it in the .err file).


I have changed one of the tables that consistently gives my trouble  
to Innodb.  I will continue to watch for corruption.


Given that can anyone point out any big things to note about innodb  
tables.  I've read that they are in general better than myisam  
tables but you loose the fulltext index option.


I think it's more that they are different.  InnoDB has some  
complexities related to backups and slightly different functionality,  
and the number of tips on the Internet are much fewer than for  
MyISAM.  However, if you are doing lots of inserts with simultaneous  
selects you really have to use InnoDB to get reasonable performance.


  Is there a typical work around for this??  I typically only have  
the odd tables that needs a full text search capability.  I think  
what I have seen was to create a dummy myisam table with just the  
key and fields from the innodb table and add a fulltext index to  
search against.  It would just be a matter of keeping the tables  
synced.


I think that's what most people do.

I am also wondering about back-ups.  The docs seem to indicate that  
you can still run mysqldump... but how does this effect restoring.   
In my case I can afford to shutdown the server in the wee hours of  
the morning and back-up the data dir manually.


1)  Shut down and manually copy, but make sure you get all the InnoDB  
parts...data files and transaction logs.  One is pretty much useless  
without the other.
2)  mysqldump, look at the options.  This with InnoDB is pretty nice  
b/c you can use --single-transaction and get a point in time copy of  
the table without disrupting other users.  In particular the comments  
on the mysql documentation page for mysqldump detail a good set of  
flags for InnoDB.
3)  The (non-free) ibbackup which takes a backup while running  
without disturbing users


I don't think there's any fundamental issue with MySQL (either myisam  
or innodb) on OS X, but I do think that MySQL is something that  
doesn't do well when run only through Apple's front end.  It has two  
many options, configuration details, etc... if you are doing anything  
more than a few small tables.  Plus, if you are relying on it all  
you'll want to be able to take advantage of bug fixes faster than  
Apple's MySQL updates allow you too.


Fortunately, it's easy enough to download the mysql binary and use/ 
configure it independently.  We've had quite good luck with MySQL on  
OS X with a pretty big install, but there are a lot of details to  
learn so it can seem tough to work out at times.


Good luck,
Ware

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Innodb Questions (was: Mac OS X Table Corruption)

[Dan Tappin]

On Jul 23, 2005, at 10:14 AM, Ware Adams wrote:

Has your mysql crashed or have your restarted the machine without  
first shutting down mysql manually?  We only saw this error when  
mysql was not shut down normally.  You can look in your .err file  
which should be in your data directory.  Every mysql sartup  
sequence should be preceded by a line saying mysqld ended.  If  
not it's crashing and that's the issue to address.


Nope... nothing like that... just lots of:

[ERROR] Got error 127 when reading table

No crashes at all.


2.) Try Innodb



This is a MyISAM error, but if mysqld is crashing or being shutdown  
abnormally InnoDB too will have to go through a crash recovery at  
startup (it's just automatic, but you'll see it in the .err file).


so the repair is manual on MyISAM and auto on start-up for Innodb?

I am also wondering about back-ups.  The docs seem to indicate  
that you can still run mysqldump... but how does this effect  
restoring.  In my case I can afford to shutdown the server in the  
wee hours of the morning and back-up the data dir manually.




1)  Shut down and manually copy, but make sure you get all the  
InnoDB parts...data files and transaction logs.  One is pretty much  
useless without the other.
2)  mysqldump, look at the options.  This with InnoDB is pretty  
nice b/c you can use --single-transaction and get a point in time  
copy of the table without disrupting other users.  In particular  
the comments on the mysql documentation page for mysqldump detail a  
good set of flags for InnoDB.
3)  The (non-free) ibbackup which takes a backup while running  
without disturbing users


Im my situation I'm ok with shutting everyone out (via PHP which the  
only access to the db) and running either type of back-up.


So if I read you correctly mysqldump locks every one out and dumps.   
But with Innodb you can add '--single-transaction' and the dump the  
data at that point in time which users continue to work with the  
database?


I don't think there's any fundamental issue with MySQL (either  
myisam or innodb) on OS X, but I do think that MySQL is something  
that doesn't do well when run only through Apple's front end.  It  
has two many options, configuration details, etc... if you are  
doing anything more than a few small tables.  Plus, if you are  
relying on it all you'll want to be able to take advantage of bug  
fixes faster than Apple's MySQL updates allow you too.


Fortunately, it's easy enough to download the mysql binary and use/ 
configure it independently.  We've had quite good luck with MySQL  
on OS X with a pretty big install, but there are a lot of details  
to learn so it can seem tough to work out at times.


Well Apple does not really have a front end for MySQL at all.  I've  
gone away from Apple's updates and install my own version from the  
source packages provided by MySQL.


Dan T

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Innodb Questions (was: Mac OS X Table Corruption)

[Ware Adams]

On Jul 23, 2005, at 1:58 PM, Dan Tappin wrote:



On Jul 23, 2005, at 10:14 AM, Ware Adams wrote:


Has your mysql crashed or have your restarted the machine without  
first shutting down mysql manually?  We only saw this error when  
mysql was not shut down normally.  You can look in your .err file  
which should be in your data directory.  Every mysql sartup  
sequence should be preceded by a line saying mysqld ended.  If  
not it's crashing and that's the issue to address.




Nope... nothing like that... just lots of:

[ERROR] Got error 127 when reading table

No crashes at all.


Then I don't know what to say about the MyISAM error, though I'd  
watch InnoDB pretty carefully as I think MyISAM is pretty robust on  
OS X too.  We did also see these when a disk is failing, but I assume  
you've run disk utility.  I guess it could be failing and not showing  
up in disk utility.  If it's an XServe the SMART data might show you  
that, or if you have DiskWarrior I think it might monitor SMART data  
for you.



2.) Try Innodb




This is a MyISAM error, but if mysqld is crashing or being  
shutdown abnormally InnoDB too will have to go through a crash  
recovery at startup (it's just automatic, but you'll see it in  
the .err file).




so the repair is manual on MyISAM and auto on start-up for Innodb?


Yes, but that's after a crash.  When you start InnoDB after a crash  
you'll see:


050722 16:26:38  mysqld started
050722 16:26:40  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050722 16:27:09  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 2875 3750200540.
InnoDB: Doing recovery: scanned up to log sequence number 2875  
3755443200

...
InnoDB: Doing recovery: scanned up to log sequence number 2875  
3878800255
050722 16:27:18  InnoDB: Starting an apply batch of log records to  
the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15  
16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38  
39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61  
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84  
85 86 87 88 89 90 91 92 93 94 95 96 97 98 99

InnoDB: Apply batch completed
050722 16:30:11  InnoDB: Flushing modified pages from the buffer pool...
050722 16:30:33  InnoDB: Started; log sequence number 2875 3878800255
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.12-standard-log'  socket: '/tmp/mysql.sock'  port:  
3306  MySQL Community Edition - Standard (GPL)


But that's after a power failure when and InnoDB could tell it had  
issues when we restarted.  The thing I would be concerned about in  
your case is what's going to happen if the same thing that causes  
issues for MyISAM has an impact on InnoDB.  If it were crashes InnoDB  
can handle that pretty well, but if it's some underlying disk issue  
then InnoDB could wind up with it's table space corrupt just like  
MyISAM.


The issue with InnoDB is that the tablespace files are more dependent  
on each other.  So if you have a corrupt MyISAM table you could  
literally shut mysqld down, delete the files for that table and  
everything else is fine.  You'd lose the data for that table but not  
anything else.


With InnoDB you have the transaction logs, the shared tablespace  
files and if you are using file_per_table a tablespace (.ibd) file  
for each table.  At least the first two of these are completely  
dependent on each other.  If you wind up with a corrupted shared  
tablespace file due to a disk error you can potentially lose  
everything.  The transaction logs can be replaced, but only if there  
are no uncommitted transactions in them.  If you lose a .ibd file in  
file_per_table you would just lose that table.


I probably didn't explain that very well, you might want to read the  
sections on InnoDB's various files in the InnoDB manual


http://www.innodb.com/ibman.php

In short, InnoDB deals better than MyISAM with crashes of the mysqld  
server or the OS, but I don't think it's necessarily any better (nor  
maybe could it be) with dealing with an underlying disk corruption  
issue that keeps occurring.


I am also wondering about back-ups.  The docs seem to indicate  
that you can still run mysqldump... but how does this effect  
restoring.  In my case I can afford to shutdown the server in the  
wee hours of the morning and back-up the data dir manually.





1)  Shut down and manually copy, but make sure you get all the  
InnoDB parts...data files and transaction logs.  One is pretty  
much useless without the other.
2)  mysqldump, look at the options.  This with InnoDB is pretty  
nice b/c you can use --single-transaction and get a point in time  
copy of the table without disrupting other users.  In particular  
the comments on 

Re: Innodb Questions (was: Mac OS X Table Corruption)

[Dan Tappin]

On Jul 23, 2005, at 12:49 PM, Ware Adams wrote:

Then I don't know what to say about the MyISAM error, though I'd  
watch InnoDB pretty carefully as I think MyISAM is pretty robust on  
OS X too.  We did also see these when a disk is failing, but I  
assume you've run disk utility.  I guess it could be failing and  
not showing up in disk utility.  If it's an XServe the SMART data  
might show you that, or if you have DiskWarrior I think it might  
monitor SMART data for you.


The part that keeps scaring me is that I have seen this on 10.2, 10.3  
nd 10.4 all with separate hardware.  My recent build has 2 drive on a  
mirror.  I find it hard to believe that I have been that unlucky to  
have drive failures / failed filesystems on ALL these machines.


My set-up ran rock solid on MySQL 3.x for years... never an issue.   
The problem came when I went of from Apple's default install and  
switched to 4.x.


I was once asked about the shared libraies and if I had removed  
them.  I cleared off the Apple 3.x build of MySQL but I'm wondering  
if I missed something that is conflicting with the new 4.1.x install  
and causing the conflicts.


Dan T


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Innodb Questions (was: Mac OS X Table Corruption)

[Dan Tappin]

So I've been poking around my system and I found

/usr/lib/mysql/
/usr/share/mysql/

The mod dates on these are really old.  Can I delete these safely?   
I'm guessing the are from the Apple install.


These files seem to be in the /usr/local/mysql/ path also.  Hmmm... I  
looked at my old Mac OS X 10.2 system with the same issues and these  
files are not there so I'm guessing this is not likely the issue.


Dan T

On Jul 23, 2005, at 1:35 PM, Dan Tappin wrote:

I was once asked about the shared libraies and if I had removed  
them.  I cleared off the Apple 3.x build of MySQL but I'm wondering  
if I missed something that is conflicting with the new 4.1.x  
install and causing the conflicts.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: InnoDB Questions

[Dathan Vance Pattishall]

 -Original Message-
 From: Lou Olsten [mailto:[EMAIL PROTECTED]
 Sent: Thursday, May 13, 2004 6:45 AM
 To: [EMAIL PROTECTED]
 Subject: InnoDB Questions
 
 Need someone with some insight or experience with InnoDB (Heikki?? :-)
 
 a) Where does InnoDB store all of this information (such as the deleted
 rows)?  In the InnoDB tablespace?

In the innodb transaction logs, usually 2 of them are created with an
archive log


 b) How long is it stored?

It's flushed on commit and/or every few seconds

 c) Can we (users) control any of this?
 
Sort of but not as fine grain as it seem your indicating.

 2) Another question I have is surrounding fragmentation.  Is there a way I
 can monitor my InnoDB tables to see the level of fragmentation on the
 table and/or indexes?

No, other then looking at show table status and looking at pages free.

 
Look up innodb start up options on mysql.com






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: innodb questions (backup and transactions)

[Heikki Tuuri]

Russ,

you can also use

mysqldump --single-transaction

to back up InnoDB type tables. The advantage of InnoDB Hot Backup over that
method is that InnoDB Hot Backup takes binary backups of the ibdata files.
Restoring a binary backup is much faster than a table dump.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables

Order MySQL technical support from https://order.mysql.com/

- Original Message - 
From: Russ [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, December 22, 2003 1:22 PM
Subject: innodb questions (backup and transactions)


 Hey Guys,

 I have just a couple of questions regarding the innodb database type
 that I'd appreciate any advice with. I'm currently developing a custom
 shopping cart / backend and, due to the nature of such a system, would
 like to take advantage of transactions - so innodb seems the logical
choice.

 However, I have a couple of reservations...

 I realise innodb does not have fulltext support built in - is the only
 way round this to have any data that needs to be fulltext-searched in a
 myISAM table? I've read that a ROLLBACK after a transaction will not
 rollback the myISAM tables - so I guess some app logic is required for
 these special cases. Is my thinking right on this issue?

 And secondly, I'm a bit worried about backing up innodb tables - more
 specifically a live backup. I've seen a product innodb sell specifically
 to do this but I can't really justify the licensing costs.  Are there
 any other options for a live backup? The only others means of backup
 would be to shutdown the server and copy the data manually I assume?

 Any pointers to the above two queries greatly appreciated!

 Russ



 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Leo Huang]

MySQL doesn't work.

I tried to modified the line:
innodb_data_file_path = ibdata1:10M:autoextend

to
innodb_data_file_path = ibdata1:500M
or
innodb_data_file_path = ibdata1:500M:autoextend
or
innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend

they all gave me the same error below.


InnoDB: Error: data file ./ibdata1 is of a different size
InnoDB: than specified in the .cnf file!
InnoDB: Could not open data files
031105 9:42:56 Can't init databases
031105 09:42:56 mysqld ended

The last one really does the matter!! That's if I run out of the space
on the current directory, I won't be able to put another file anywhere
else!?

Leo



Nitin wrote:

You're right, it wont decrease the physical size, but only free up the space
within file to optimize the tablespace, in case, you want to check the size
of this data file, you can remove autoextend from:

innodb_data_file_path = ibdata1:10M:autoextend

and specify the size limit in the place of 10M, but i guess, if you specify
the size to less than 790M (which is the current size of your datafile), to
say 500M and the space is free in that file, it will resize it. That's the
behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it,
and let me know as i dont have my database on innodb yet.

'Tablespace is part of your database. database consists of at least one
tablespace. it's basically used to restrict users from seeing other user's
data. like, you can assign a tablespace to a user and none else (ofcourse
other than root) can see the data.

For more info, have a look at:
http://www.mysql.com/doc/en/InnoDB_File_space.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 8:00 PM
Subject: Re: InnoDB Questions


  

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database.
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a tablespace?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



- - Original Message - 
From: Nitin [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions




Hello,

first things first, you cann't resize your datafiles without
  

shutting down


your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data
  

dir or


in /etc dir, for the default options specified there fo the datafile
  

with:


innodb_data_file_path

Yes, you can add data file, just add another entry to above option.
  

option


entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store
  

actual


data. one or more of these files are attached to one tablespace and
  

one file


cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql
  

statements


applied to database. these files are used to restore data in case of
  

any


crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


  

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into


InnoDB a


few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44


ib_logfile0


-rw-rw1 mysqlmysql10485760 Nov  4 20:44


ib_logfile1


-rw-rw1 mysqlmysql10485760 Nov  3 00:02


ib_logfile2


But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2


to


get higher performance. Can I do that now, after

RE: InnoDB Questions

[Marvin Wright]

Hi,

From my experience, once you have created a datafile it doesn't work when
you try to make it larger.
If you dont care about the data thats already in it then delete the current
ibdata files and restart mysql.
If you do then put the size back to how it was, dump the data, shutdown the
server, change the cfg file and remove the ibdata file.
At startup it should create the new larger file, then you can import the
dumped data.

Marvin.

-Original Message-
From: Leo Huang [mailto:[EMAIL PROTECTED]
Sent: 05 November 2003 07:40
To: Nitin
Cc: [EMAIL PROTECTED]
Subject: Re: InnoDB Questions



MySQL doesn't work.

I tried to modified the line:
innodb_data_file_path = ibdata1:10M:autoextend

to
innodb_data_file_path = ibdata1:500M
or
innodb_data_file_path = ibdata1:500M:autoextend
or
innodb_data_file_path = ibdata1:500M;ibdata2:1000M:autoextend

they all gave me the same error below.


InnoDB: Error: data file ./ibdata1 is of a different size
InnoDB: than specified in the .cnf file!
InnoDB: Could not open data files
031105 9:42:56 Can't init databases
031105 09:42:56 mysqld ended

The last one really does the matter!! That's if I run out of the space
on the current directory, I won't be able to put another file anywhere
else!?

Leo



Nitin wrote:

You're right, it wont decrease the physical size, but only free up the
space
within file to optimize the tablespace, in case, you want to check the size
of this data file, you can remove autoextend from:

innodb_data_file_path = ibdata1:10M:autoextend

and specify the size limit in the place of 10M, but i guess, if you specify
the size to less than 790M (which is the current size of your datafile), to
say 500M and the space is free in that file, it will resize it. That's the
behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it,
and let me know as i dont have my database on innodb yet.

'Tablespace is part of your database. database consists of at least one
tablespace. it's basically used to restrict users from seeing other user's
data. like, you can assign a tablespace to a user and none else (ofcourse
other than root) can see the data.

For more info, have a look at:
http://www.mysql.com/doc/en/InnoDB_File_space.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 8:00 PM
Subject: Re: InnoDB Questions


  

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database.
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a tablespace?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



- - Original Message - 
From: Nitin [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions




Hello,

first things first, you cann't resize your datafiles without
  

shutting down


your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data
  

dir or


in /etc dir, for the default options specified there fo the datafile
  

with:


innodb_data_file_path

Yes, you can add data file, just add another entry to above option.
  

option


entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store
  

actual


data. one or more of these files are attached to one tablespace and
  

one file


cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql
  

statements


applied to database. these files are used to restore data in case of
  

any


crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


  

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into


InnoDB a


few days ago.
I

Re: InnoDB Questions

[Nitin]

Hello,

first things first, you cann't resize your datafiles without shutting down
your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data dir or
in /etc dir, for the default options specified there fo the datafile with:

innodb_data_file_path

Yes, you can add data file, just add another entry to above option. option
entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store actual
data. one or more of these files are attached to one tablespace and one file
cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
applied to database. these files are used to restore data in case of any
crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


 Hello,

 I have a few questions about InnoDB.
 I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
 few days ago.
 I notice that it generates these files

 -rw-rw1 mysqlmysql2560 Nov  2 13:07
 ib_arch_log_00
 -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
 -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
 -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
 -rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2

 But how can those files work together?

 In InnoDB documentation, it suggests to add another file ibdata2 to
 get higher performance. Can I do that now, after I have created the
 ibdata1 and used it for a while?

 The most important thing is I deleted a 300M database, but the ibdata1
 remains the same size. MyPHPAdmin says 330,000KB free. How can I make
 the data file smaller?

 I will be really appreciated if someone can briefly describe what's
 happening to those files or point me to some articles.

 Thanks a lot,
 Leo


 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Leo Huang]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Thank you very much for your reply, Nitin.

I did read the Adding_and_removing in the manual, but it says
Currently you cannot remove a datafile from InnoDB. To decrease the
size of your database you have to use `mysqldump' to dump all your
tables, create a new database, and import your tables to the new
database.
It does reduce the size of the database(similar to optimize for
MyISAM), but it doesn't reduce the size of the file.

Could you explain a bit what is a tablespace?

Thanks,
Leo

/etc/my.cnf:
[mysqld]
datadir=/var/lib/mysql
set-variable=max_connections=300
innodb_data_file_path = ibdata1:10M:autoextend
default-table-type=InnoDB
set-variable = innodb_buffer_pool_size=512M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1


[mysql.server]
user=mysql
basedir=/usr

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



- - Original Message - 
From: Nitin [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 1:01 AM
Subject: Re: InnoDB Questions


 Hello,

 first things first, you cann't resize your datafiles without
shutting down
 your database. if it's ok with you, have a look at
 http://www.mysql.com/doc/en/Adding_and_removing.html

 you may want to have a look at you my.cnf file, stored in mysql data
dir or
 in /etc dir, for the default options specified there fo the datafile
with:

 innodb_data_file_path

 Yes, you can add data file, just add another entry to above option.
option
 entry is self-explainatory.

 At last, ibdata1, ibdata2 are actual data files used to store
actual
 data. one or more of these files are attached to one tablespace and
one file
 cant span across tablespaces.

 ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
 applied to database. these files are used to restore data in case of
any
 crash or mishap.

 for further info, have a look at:
 http://www.mysql.com/doc/en/InnoDB_start.html

 Enjoy
 Nitin


 - Original Message - 
 From: Leo Huang [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, November 04, 2003 6:28 PM
 Subject: InnoDB Questions


  Hello,
 
  I have a few questions about InnoDB.
  I am new to InnoDB, and just converted my MyISAM tables into
InnoDB a
  few days ago.
  I notice that it generates these files
 
  -rw-rw1 mysqlmysql2560 Nov  2 13:07
  ib_arch_log_00
  -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
  -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile0
  -rw-rw1 mysqlmysql10485760 Nov  4 20:44
ib_logfile1
  -rw-rw1 mysqlmysql10485760 Nov  3 00:02
ib_logfile2
 
  But how can those files work together?
 
  In InnoDB documentation, it suggests to add another file ibdata2
to
  get higher performance. Can I do that now, after I have created
the
  ibdata1 and used it for a while?
 
  The most important thing is I deleted a 300M database, but the
ibdata1
  remains the same size. MyPHPAdmin says 330,000KB free. How can I
make
  the data file smaller?
 
  I will be really appreciated if someone can briefly describe
what's
  happening to those files or point me to some articles.
 
  Thanks a lot,
  Leo
 
 
  -- 
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 



-BEGIN PGP SIGNATURE-
Version: PGP 8.0.2

iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN
h21IQZ8ozOUeELhvWSpznyTI
=H/2E
-END PGP SIGNATURE-


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Nitin]

You're right, it wont decrease the physical size, but only free up the space
within file to optimize the tablespace, in case, you want to check the size
of this data file, you can remove autoextend from:

innodb_data_file_path = ibdata1:10M:autoextend

and specify the size limit in the place of 10M, but i guess, if you specify
the size to less than 790M (which is the current size of your datafile), to
say 500M and the space is free in that file, it will resize it. That's the
behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it,
and let me know as i dont have my database on innodb yet.

'Tablespace is part of your database. database consists of at least one
tablespace. it's basically used to restrict users from seeing other user's
data. like, you can assign a tablespace to a user and none else (ofcourse
other than root) can see the data.

For more info, have a look at:
http://www.mysql.com/doc/en/InnoDB_File_space.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 8:00 PM
Subject: Re: InnoDB Questions



 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 Thank you very much for your reply, Nitin.

 I did read the Adding_and_removing in the manual, but it says
 Currently you cannot remove a datafile from InnoDB. To decrease the
 size of your database you have to use `mysqldump' to dump all your
 tables, create a new database, and import your tables to the new
 database.
 It does reduce the size of the database(similar to optimize for
 MyISAM), but it doesn't reduce the size of the file.

 Could you explain a bit what is a tablespace?

 Thanks,
 Leo

 /etc/my.cnf:
 [mysqld]
 datadir=/var/lib/mysql
 set-variable=max_connections=300
 innodb_data_file_path = ibdata1:10M:autoextend
 default-table-type=InnoDB
 set-variable = innodb_buffer_pool_size=512M
 set-variable = innodb_additional_mem_pool_size=10M
 set-variable = innodb_log_file_size=10M
 set-variable = innodb_log_files_in_group=3
 set-variable = innodb_log_buffer_size=8M
 innodb_flush_log_at_trx_commit=1


 [mysql.server]
 user=mysql
 basedir=/usr

 [safe_mysqld]
 err-log=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid



 - - Original Message - 
 From: Nitin [EMAIL PROTECTED]
 To: Leo Huang [EMAIL PROTECTED]; [EMAIL PROTECTED]
 Sent: Wednesday, November 05, 2003 1:01 AM
 Subject: Re: InnoDB Questions


  Hello,
 
  first things first, you cann't resize your datafiles without
 shutting down
  your database. if it's ok with you, have a look at
  http://www.mysql.com/doc/en/Adding_and_removing.html
 
  you may want to have a look at you my.cnf file, stored in mysql data
 dir or
  in /etc dir, for the default options specified there fo the datafile
 with:
 
  innodb_data_file_path
 
  Yes, you can add data file, just add another entry to above option.
 option
  entry is self-explainatory.
 
  At last, ibdata1, ibdata2 are actual data files used to store
 actual
  data. one or more of these files are attached to one tablespace and
 one file
  cant span across tablespaces.
 
  ib_logfile0, ib_logfile1 are log files, which are used to log sql
 statements
  applied to database. these files are used to restore data in case of
 any
  crash or mishap.
 
  for further info, have a look at:
  http://www.mysql.com/doc/en/InnoDB_start.html
 
  Enjoy
  Nitin
 
 
  - Original Message - 
  From: Leo Huang [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Tuesday, November 04, 2003 6:28 PM
  Subject: InnoDB Questions
 
 
   Hello,
  
   I have a few questions about InnoDB.
   I am new to InnoDB, and just converted my MyISAM tables into
 InnoDB a
   few days ago.
   I notice that it generates these files
  
   -rw-rw1 mysqlmysql2560 Nov  2 13:07
   ib_arch_log_00
   -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
   -rw-rw1 mysqlmysql10485760 Nov  4 20:44
 ib_logfile0
   -rw-rw1 mysqlmysql10485760 Nov  4 20:44
 ib_logfile1
   -rw-rw1 mysqlmysql10485760 Nov  3 00:02
 ib_logfile2
  
   But how can those files work together?
  
   In InnoDB documentation, it suggests to add another file ibdata2
 to
   get higher performance. Can I do that now, after I have created
 the
   ibdata1 and used it for a while?
  
   The most important thing is I deleted a 300M database, but the
 ibdata1
   remains the same size. MyPHPAdmin says 330,000KB free. How can I
 make
   the data file smaller?
  
   I will be really appreciated if someone can briefly describe
 what's
   happening to those files or point me to some articles.
  
   Thanks a lot,
   Leo
  
  
   -- 
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:
  http://lists.mysql.com/[EMAIL PROTECTED]
  
 
 
 
 -BEGIN PGP SIGNATURE-
 Version: PGP 8.0.2

 iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN
 h21IQZ8ozOUeELhvWSpznyTI
 =H/2E

Re: InnoDB Questions

[Gabriel Ricard]

On Tuesday, November 4, 2003, at 07:58  AM, Leo Huang wrote:

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?
How exactly does this increase performance? Will InnoDB store some data 
in one data file and some in another (somewhat like RAID 1)?

- Gabriel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Leo Huang]

I don't know.

I will get some time this week, shutdown MySQL, backup my binary files, 
have a go as what Nitin said and see what's going on there.

Leo

Gabriel Ricard wrote:

On Tuesday, November 4, 2003, at 07:58  AM, Leo Huang wrote:

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?


How exactly does this increase performance? Will InnoDB store some 
data in one data file and some in another (somewhat like RAID 1)?

- Gabriel




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Leo Huang]

Hello Nitin,

From the timestamp of the log files, it seems that the first two files
works together while the last one seems just sitting there, doesn't do
anything.

Also, will the log files getting bigger and bigger in the future?? If so
how should I deal with them?

For your last suggestion, what will Oracle do if I specify a datafile
size less than the database size, e.g. I specify 20M in the my.cnf while
there are actually 400M of data in my database?

Leo

Nitin wrote:

Hello,

first things first, you cann't resize your datafiles without shutting down
your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html

you may want to have a look at you my.cnf file, stored in mysql data dir or
in /etc dir, for the default options specified there fo the datafile with:

innodb_data_file_path

Yes, you can add data file, just add another entry to above option. option
entry is self-explainatory.

At last, ibdata1, ibdata2 are actual data files used to store actual
data. one or more of these files are attached to one tablespace and one file
cant span across tablespaces.

ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
applied to database. these files are used to restore data in case of any
crash or mishap.

for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html

Enjoy
Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions


  

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files

-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2

But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?

The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?

I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.

Thanks a lot,
Leo


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:


http://lists.mysql.com/[EMAIL PROTECTED]
  



  




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Chris Nolan]

The last one you're referring to - could it be the error log?

The log files will only grow to a pre-determined limit. These log files 
are used to ensure that transactions maintain their durability.

With Oracle, you'd want to be careful. Oracle gets very, very picky 
about the stuff underneath it when it's running. If it gets to a 
configuration limit, the results can be very, very depressing. Having to 
extend tablespaces by hand is a very common Oracle DBA task.

Regards,

Chris

Leo Huang wrote:

Hello Nitin,

From the timestamp of the log files, it seems that the first two files
works together while the last one seems just sitting there, doesn't do
anything.
Also, will the log files getting bigger and bigger in the future?? If so
how should I deal with them?
For your last suggestion, what will Oracle do if I specify a datafile
size less than the database size, e.g. I specify 20M in the my.cnf while
there are actually 400M of data in my database?
Leo

Nitin wrote:

 

Hello,

first things first, you cann't resize your datafiles without shutting down
your database. if it's ok with you, have a look at
http://www.mysql.com/doc/en/Adding_and_removing.html
you may want to have a look at you my.cnf file, stored in mysql data dir or
in /etc dir, for the default options specified there fo the datafile with:
innodb_data_file_path

Yes, you can add data file, just add another entry to above option. option
entry is self-explainatory.
At last, ibdata1, ibdata2 are actual data files used to store actual
data. one or more of these files are attached to one tablespace and one file
cant span across tablespaces.
ib_logfile0, ib_logfile1 are log files, which are used to log sql statements
applied to database. these files are used to restore data in case of any
crash or mishap.
for further info, have a look at:
http://www.mysql.com/doc/en/InnoDB_start.html
Enjoy
Nitin
- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 04, 2003 6:28 PM
Subject: InnoDB Questions



   

Hello,

I have a few questions about InnoDB.
I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
few days ago.
I notice that it generates these files
-rw-rw1 mysqlmysql2560 Nov  2 13:07
ib_arch_log_00
-rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
-rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
-rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
But how can those files work together?

In InnoDB documentation, it suggests to add another file ibdata2 to
get higher performance. Can I do that now, after I have created the
ibdata1 and used it for a while?
The most important thing is I deleted a 300M database, but the ibdata1
remains the same size. MyPHPAdmin says 330,000KB free. How can I make
the data file smaller?
I will be really appreciated if someone can briefly describe what's
happening to those files or point me to some articles.
Thanks a lot,
Leo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
  

 

http://lists.mysql.com/[EMAIL PROTECTED]





   



 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Nitin]

not all the log files will work together, i mean, these files work in cyclic
fashion, one fills up, second one gets used, it fills up, second one, and
then back to first one. so it wont keep getting bigger, it will overwrite
the previous data when it comes back to that file.

in oracle, if you have data more than the size you're specifying, it'll
through error and wont do nothing.

Nitin


- Original Message - 
From: Leo Huang [EMAIL PROTECTED]
To: Nitin [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 5:12 AM
Subject: Re: InnoDB Questions


 Hello Nitin,

 From the timestamp of the log files, it seems that the first two files
 works together while the last one seems just sitting there, doesn't do
 anything.

 Also, will the log files getting bigger and bigger in the future?? If so
 how should I deal with them?

 For your last suggestion, what will Oracle do if I specify a datafile
 size less than the database size, e.g. I specify 20M in the my.cnf while
 there are actually 400M of data in my database?

 Leo

 Nitin wrote:

 Hello,
 
 first things first, you cann't resize your datafiles without shutting
down
 your database. if it's ok with you, have a look at
 http://www.mysql.com/doc/en/Adding_and_removing.html
 
 you may want to have a look at you my.cnf file, stored in mysql data dir
or
 in /etc dir, for the default options specified there fo the datafile
with:
 
 innodb_data_file_path
 
 Yes, you can add data file, just add another entry to above option.
option
 entry is self-explainatory.
 
 At last, ibdata1, ibdata2 are actual data files used to store actual
 data. one or more of these files are attached to one tablespace and one
file
 cant span across tablespaces.
 
 ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
 applied to database. these files are used to restore data in case of any
 crash or mishap.
 
 for further info, have a look at:
 http://www.mysql.com/doc/en/InnoDB_start.html
 
 Enjoy
 Nitin
 
 
 - Original Message - 
 From: Leo Huang [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, November 04, 2003 6:28 PM
 Subject: InnoDB Questions
 
 
 
 
 Hello,
 
 I have a few questions about InnoDB.
 I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
 few days ago.
 I notice that it generates these files
 
 -rw-rw1 mysqlmysql2560 Nov  2 13:07
 ib_arch_log_00
 -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
 -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
 -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
 -rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
 
 But how can those files work together?
 
 In InnoDB documentation, it suggests to add another file ibdata2 to
 get higher performance. Can I do that now, after I have created the
 ibdata1 and used it for a while?
 
 The most important thing is I deleted a 300M database, but the ibdata1
 remains the same size. MyPHPAdmin says 330,000KB free. How can I make
 the data file smaller?
 
 I will be really appreciated if someone can briefly describe what's
 happening to those files or point me to some articles.
 
 Thanks a lot,
 Leo
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 
 
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
 
 



 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB Questions

[Nitin]

yea, he's right, it may be error log file

Nitin

- Original Message - 
From: Chris Nolan [EMAIL PROTECTED]
To: Leo Huang [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, November 05, 2003 5:53 AM
Subject: Re: InnoDB Questions


 The last one you're referring to - could it be the error log?

 The log files will only grow to a pre-determined limit. These log files
 are used to ensure that transactions maintain their durability.

 With Oracle, you'd want to be careful. Oracle gets very, very picky
 about the stuff underneath it when it's running. If it gets to a
 configuration limit, the results can be very, very depressing. Having to
 extend tablespaces by hand is a very common Oracle DBA task.

 Regards,

 Chris

 Leo Huang wrote:

 Hello Nitin,
 
 From the timestamp of the log files, it seems that the first two files
 works together while the last one seems just sitting there, doesn't do
 anything.
 
 Also, will the log files getting bigger and bigger in the future?? If so
 how should I deal with them?
 
 For your last suggestion, what will Oracle do if I specify a datafile
 size less than the database size, e.g. I specify 20M in the my.cnf while
 there are actually 400M of data in my database?
 
 Leo
 
 Nitin wrote:
 
 
 
 Hello,
 
 first things first, you cann't resize your datafiles without shutting
down
 your database. if it's ok with you, have a look at
 http://www.mysql.com/doc/en/Adding_and_removing.html
 
 you may want to have a look at you my.cnf file, stored in mysql data dir
or
 in /etc dir, for the default options specified there fo the datafile
with:
 
 innodb_data_file_path
 
 Yes, you can add data file, just add another entry to above option.
option
 entry is self-explainatory.
 
 At last, ibdata1, ibdata2 are actual data files used to store actual
 data. one or more of these files are attached to one tablespace and one
file
 cant span across tablespaces.
 
 ib_logfile0, ib_logfile1 are log files, which are used to log sql
statements
 applied to database. these files are used to restore data in case of any
 crash or mishap.
 
 for further info, have a look at:
 http://www.mysql.com/doc/en/InnoDB_start.html
 
 Enjoy
 Nitin
 
 
 - Original Message - 
 From: Leo Huang [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Tuesday, November 04, 2003 6:28 PM
 Subject: InnoDB Questions
 
 
 
 
 
 
 Hello,
 
 I have a few questions about InnoDB.
 I am new to InnoDB, and just converted my MyISAM tables into InnoDB a
 few days ago.
 I notice that it generates these files
 
 -rw-rw1 mysqlmysql2560 Nov  2 13:07
 ib_arch_log_00
 -rw-rw1 mysqlmysql790626304 Nov  4 20:44 ibdata1
 -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile0
 -rw-rw1 mysqlmysql10485760 Nov  4 20:44 ib_logfile1
 -rw-rw1 mysqlmysql10485760 Nov  3 00:02 ib_logfile2
 
 But how can those files work together?
 
 In InnoDB documentation, it suggests to add another file ibdata2 to
 get higher performance. Can I do that now, after I have created the
 ibdata1 and used it for a while?
 
 The most important thing is I deleted a 300M database, but the ibdata1
 remains the same size. MyPHPAdmin says 330,000KB free. How can I make
 the data file smaller?
 
 I will be really appreciated if someone can briefly describe what's
 happening to those files or point me to some articles.
 
 Thanks a lot,
 Leo
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 
 
 
 
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
 
 
 
 
 
 
 
 
 



 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB questions for all!

[Heikki Tuuri]

Chris,

From: Chris Nolan ([EMAIL PROTECTED])
Subject: InnoDB questions for all!
This is the only article in this thread
View: Original Format
Newsgroups: mailing.database.myodbc
Date: 2003-08-28 16:16:53 PST

 Hi everyone!

 My silly questions for today concern the not-silly-at-all InnoDB table
 backend.

not silly questions at all.

 1. If I do an ALTER  TABLE tbl_name TYPE=InnoDB on an InnoDB table,
 Heikki has
 indicated that a defrag of that table should happen. Does anyone have
 any comments regarding the
 result of a power failure while this operation is in progress?

MySQL performs an ALTER TABLE tablename by

(1) first creating a temporary table with the new definition, and then
copying all rows to it. Then it
(2) renames the old table,
(3) renames the temporary table to 'tablename', and
(4) drops the old table.

This should ensure the data is never lost, even in a power failure. If the
failure happens at a bad time, you may end up with 2 renamed tables and no
table of the original name 'tablename'. But no one has ever reported this
situation. InnoDB also contains a special mechanism by which you can rename
a temporary table #sql.., see
http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict.

 2. If I do a SELECT *  on an InnoDB table and dump the output to a
 file inside a single transaction,
 will INSERT statements still complete correctly? I've completed a 3rd
 year Database course at a decent uni
 and know the theory as well as the practice, but admittedly I don't know
 enough about InnoDB's innards to
 answer this question for myself. My gut feeling says that INSERTs will
 complete successfully while this process
 is going on and that UPDATEs may complete, depending on InnoDB's method
 of multiversioning.

A SELECT ... INTO OUTFILE ... does not set any locks or disturb INSERTs or
UPDATEs. The only limitation is that InnoDB cannot purge its history while
the SELECT is transaction is active. If the SELECT transaction lasts long,
say for hours or days, you might run out of space in the tablespace.

 Thanks all!

 Regards,

 Chris

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for MySQL



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: innodb questions about message board apps

[Heikki Tuuri]

Hi!

- Original Message -
From: PR [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Thursday, September 05, 2002 5:31 PM
Subject: innodb questions about message board apps


 Hi all, I've been reading a bit on the innodb table type for mysql here
and
 on the site and some other board sites and just wanted to ask a quick
 question or two in general about it, as it relates mostly to message
boards
 and compared to myisam. Is the only gotcha in forum code generally the
 select count(*) queries? It's the only code I've seemed to see here that
 has to be dealt with differently by setting a field for a counter or
 something similar. Or are there other things in general I need to watch
out
 for.

There has been discussion about InnoDB tables in vBulletin discussion
forums. vBulletin runs on MySQL.

COUNT(*) is the main problem which can be partially solved using SHOW TABLE
STATUS to determine an approximate number of rows. Also deadlocks can
happen. Jelsoft should tune vBulletin code so that it would be better suited
for transactional tables.

 Second, the only general thing I can see is an apparent limit on the
record
 size of, I think, something like 32k in fairly recent versions. If there's
 a post as part of a record that exceeds this limit, does it get truncated
 (and what fields would get truncated - that specific fields or the last
 ones in the table), will it crash the server, anyone have any idea of what
 happens. Or am I reading this all wrong.

InnoDB supports BLOBs up to 4 GB in size.

 I'm thinking about trying it out on a friends board for him to take
 advantage of the row level locking, so I'm also wondering, has anyone run
 into problems converting back to myisam if it didn't work out to really
 improve performance.

Not that I heard of.

 Thx.

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, hot backup, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com

sql query



-
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