Re: INNODB Questions and Optimization help
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
INNODB Questions and Optimization help
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 ft_stopword_file (built-in) have_bdb NO have_crypt YES have_innodb YES have_isam YES have_openssl NO have_query_cache YES have_raid NO have_symlink YES init_file innodb_additional_mem_pool_size 8388608 innodb_autoextend_increment 8 innodb_buffer_pool_size 2516582400 innodb_data_file_path ibdata1:2G:autoextend innodb_data_home_dir innodb_fast_shutdown ON innodb_file_io_threads 4 innodb_flush_log_at_trx_commit 2 innodb_flush_method O_DIRECT innodb_force_recovery 0
Re: INNODB Questions and Optimization help
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
:~ -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
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
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
innodb questions
Hi, I recently converted some of my tables to innodb from myisam. I don't need transactions or rollback capability, I switched because I needed row-level locking. These are large tables with many rows and lots of INSERTS and UPDATES. 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) /var/lib/mysql/ibdata1(10.0 Gig in size) Since I'm new to innodb tables, I assume that's where these files came from. Based on these file sizes, I need to get rid of them to save some disk space. My questions are: 1. Do I need them? 2. Can I delete them? 3. Can I limit the size of the ibdata1 file? Is it too late to resize it? 4. What can I set to reduce the size of these files? My innodb variables are: Variable Session value Global value innodb additional mem pool size33554432 33554432 innodb autoextend increment8 8 innodb buffer pool awe mem mb 0 0 innodb buffer pool size 536870912 536870912 innodb data file path ibdata1:10M:autoextend ibdata1:10M:autoextend innodb data home dir innodb fast shutdown ON ON innodb file io threads4 4 innodb file per table OFF OFF innodb flush log at trx commit 1 1 innodb flush method innodb force recovery 0 0 innodb lock wait timeout50 50 innodb locks unsafe for binlogOFF OFF innodb log arch dir innodb log archive OFF OFF innodb log buffer size8388608 8388608 innodb log file size5242880 5242880 innodb log files in group 2 2 innodb log group home dir./ ./ innodb max dirty pages pct90 90 innodb max purge lag 0 0 innodb mirrored log groups 1 1 innodb open files300 300 innodb table locksON ON innodb thread concurrency 8 8 Please let me know if I need to post any more variables. Thanks, Grant - Yahoo! Autos. Looking for a sweet ride? Get pricing, reviews, more on new and used cars.
Re: innodb questions
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
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
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
Innodb Questions (was: Mac OS X Table Corruption)
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. So I'm trying a few obscure things: 1.) Move the MySQL data dir The default data dir was /usr/local/mysql/data/ I moved this to /var/mysql/data because subsequent MySQL installs messed with the previous path (i.e. creating a symlink from the latest install to /usr/local/mysql) I have copied the entire mysql data dir to /usr/local/data Just a hunch that it's some issue with being in /var/ On my previous MySQL systems I simply moved the data contents to the new location after each upgrade and restarted the server. I never had any issues here (it was also on MySQL 3.x) 2.) Try Innodb 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. 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 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. Any pointers would be appreciated. Thanks, Dan T On Jul 6, 2005, at 10:26 AM, [EMAIL PROTECTED] wrote: Dan Tappin [EMAIL PROTECTED] wrote on 07/06/2005 11:26:13 AM: I am running into repeatable table corruption with MySQL 4.x on Mac OS X 10.x. 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. 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. As a temporary fix I created a cron job to run: /usr/local/mysql/bin/myisamchk -ov on all my tables every 4 hours. This fixes the data issues but the problem is I am loosing 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 I the fix is flushing my new data down the toilet. The only common thread I can see is that I use MacSQL (an old version 2.6.3) for running test queries etc rather than the CLI. Can a mysql client cause this corruption? 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. Here is the full myisamchk output on the subject table (after adding my data back in): Checking MyISAM file: /var/mysql/data/.MYI Data records: 18 Deleted blocks: 2 - check file-size myisamchk: error: Size of datafile is: 876 Should be: 1160 - check record delete-chain myisamchk: error: record delete-link-chain corrupted - check key delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found key at page 1024 that points to record outside datafile - check record links myisamchk: error: Record-count is not ok; is 15 Should be: 18 myisamchk: warning: Found 0 deleted space. Should be 80 myisamchk: warning: Found 0 deleted blocks Should be: 2 myisamchk: warning: Found 15 partsShould be: 20 parts MyISAM-table '/var/mysql/data/oilfielddepot_r6/subnavbar.MYI' is corrupted Fix it using switch -r or -o so I run: myisamchk -vr /var/mysql/data/xx.MYI - recovering (with sort) MyISAM-table '/var/mysql/data/ xx.MYI' Data records: 18 - Fixing index 1 - Searching for keys, allocating
Re: Innodb Questions (was: Mac OS X Table Corruption)
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)
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)
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)
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)
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]
InnoDB Questions
Need someone with some insight or experience with InnoDB (Heikki?? :-) 1) According to a book I'm reading (High Performance MySQL) InnoDB uses MVCC, effectively allowing readers to not block writers. In Oracle (with which I am more familiar) this is accomplished via rollback segments, and now, undo tablespaces. Occasionally, if the rollback data overwrites itself, you can get a Snapshot too old message. But you can control the size and number of the rollback segments to fit your system. So, my InnoDB questions are: a) Where does InnoDB store all of this information (such as the deleted rows)? In the InnoDB tablespace? b) How long is it stored? c) Can we (users) control any of this? 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? Thanks very much for any help that you can provide. BTW, though I'm not finished, I'm very much enjoying the book and would recommend it for someone like me who is new to MySQL but experienced in other RDBMS's. Lou
RE: InnoDB Questions
-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]
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]
Re: innodb questions (backup and transactions)
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
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
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
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]
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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 -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
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
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
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
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
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
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
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!
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]
InnoDB questions for all!
Hi everyone! My silly questions for today concern the not-silly-at-all InnoDB table backend. 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? 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. Thanks all! Regards, Chris -- 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
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
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. 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. 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. Thx. - 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
MySQL/InnoDB questions from a MyISAM background
We have just migrated a huge installation to InnoDB from MyISAM. While I can feel the power and potential and appreciate this sophistication of what I have at my disposal, I am having some performance issues that are most likely due to my ignorance. Answers to the following questions should help me greatly. Forgive me, I'm sure these are answered in the manual as a whole but I'm having trouble pulling it all together as I am operating without sleep. Let AUTOCOMMIT=1 INSERT INTO foo (id,b,c) VALUES (3,'foo','buh'), (4,'baz','bar'); Separate transaction for each inserted row or one for the entire statement? Likewise: UPDATE foo SET c = 'gwah' WHERE id = 3 OR id = 4 Separate transaction for each updated row or one transaction? I can make good guesses as to what happens, but it'd quell my fears to have a definitive answer. Also, since we handle session management in the mysql database, there are a disproportionately high number of INSERTs which can cause a COMMIT for literally every page hit (of which there can be about 75 per second) With the option: innodb_flush_log_at_trx_commit=1 am I correct in assuming that each COMMIT generates disk I/O and will not return until it's written to the log? Whereas setting: innodb_flush_log_at_trx_commit=0 will cause COMMIT to return immediately, and hit the disk much less frequently with the understanding that in a crash we would be out about a second's worth of data? My thanks in advance. -- Michael Bacarella | Netgraft Corporation | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL/InnoDB questions from a MyISAM background
Hi! Good questions! - Original Message - From: Michael Bacarella [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, May 16, 2002 6:41 PM Subject: MySQL/InnoDB questions from a MyISAM background We have just migrated a huge installation to InnoDB from MyISAM. While I can feel the power and potential and appreciate this sophistication of what I have at my disposal, I am having some performance issues that are most likely due to my ignorance. Answers to the following questions should help me greatly. Forgive me, I'm sure these are answered in the manual as a whole but I'm having trouble pulling it all together as I am operating without sleep. Let AUTOCOMMIT=1 INSERT INTO foo (id,b,c) VALUES (3,'foo','buh'), (4,'baz','bar'); Separate transaction for each inserted row or one for the entire statement? AUTOCOMMIT=1 means that a COMMIT is automatically executed after each SQL statement. It does NOT mean a commit after each row, if the statement happens to modify several rows. Likewise: UPDATE foo SET c = 'gwah' WHERE id = 3 OR id = 4 Separate transaction for each updated row or one transaction? Only one transaction because this a single SQL statement, even if it would update 1000 000 rows. I can make good guesses as to what happens, but it'd quell my fears to have a definitive answer. Also, since we handle session management in the mysql database, there are a disproportionately high number of INSERTs which can cause a COMMIT for literally every page hit (of which there can be about 75 per second) With the option: innodb_flush_log_at_trx_commit=1 am I correct in assuming that each COMMIT generates disk I/O and will not return until it's written to the log? Yes, InnoDB will in this case at a COMMIT call fsync (== physical write to disk) on the log file. The fsync can in some Linux versions take even much longer than the 10 milliseconds required for a disk rotation. Some disks fool the operating system, and consequently fsync may return even in 2 milliseconds, though a physical write to a disk is not possible in that time. Whereas setting: innodb_flush_log_at_trx_commit=0 will cause COMMIT to return immediately, and hit the disk much less frequently with the understanding that in a crash we would be out about a second's worth of data? That is correct. Note that the MySQL binlog is written to a file after every commit, though MySQL never calls fsync for the binlog file. Thus, if the operating system does not crash, the binlog file (physically residing in the operating system file cache in main memory) may contain all the transactions which were committed during that second, and no transaction is really lost if you pipe the remaining binlog into MySQL after a crash. In 3.23.52 it is my intention that InnoDB will write the log buffer to the log file at each COMMIT, regardless of the value of innodb_flush_log_at_trx_commit. But it will only call fsync (a physical disk write) as before. Thus, if the operating system does not crash, the InnoDB automatic crash recovery will normally recover every transaction, regardless of the value of ..._trx_commit. It is also possible that I will make this strategy of using the operating system file cache as 'non-volatile' storage optional, that is, there will be a value 2 which means a compromise between the strategies specified by the current options 0 and 1. We see from the above that making a committed transaction 'durable' is a somewhat gradual process. Since we cannot fully trust disks or operating systems, we cannot with absolute certainty say that at some point the data became permanent on disk. And even if it goes to disk, it is possible that the computer is later destroyed in a fire. One could as well define that a transaction is 'durable' only when there is a backup of the database, or the binlog, stored in a fireproof safe. My thanks in advance. -- Michael Bacarella | Netgraft Corporation | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | [EMAIL PROTECTED] 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 - 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
A couple of InnoDB questions
Hi! I run MySQL 3.23.49 with InnoDB tables on a AlphaServer 4100 with 4 x 600 MHz EV5.6 CPUs with 8 MB cache. The machine has 4 GB memory. There are about 8 tables. The tables are small in size (only 1-5 GB each) but has alot of rows (usually 4 million each). There are only a few concurrent select users. Inserts are done by batch from about 70 systems every 5 minute. The systems are spread out during those 5 minute so the inserst come in a constant flow of about 10 per second in average. bin-log is turned off as the machine is unable to keep up with the insert rate with logging turned on. I have a couple of questions related to the cache settings in my.cnf. I need to tune it to give as good a select performance as possible, and also ensure that insert is as fast as possible. The machine ins't doing much else, so I can use close to all memory. Except for the default MySQL table with users etc., I only have InnoDB tables. In the my-huge.cnf example there are some of the settings : set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M How many of these needs to be big in relation to a InnoDB only setup ? ...and can I reduce some of them without problems ? I assume the key_buffer isn't used at all since InnoDB has its own, right ? ...and then the InnoDB specific. They are equal (and very low) on all example files : set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 What would be a good startup for these given my current hardware database ? The database layout and a online interface is available at http://statdb.dassic.com/ for those who want to see the table index design etc. etc. It's not the mentioned machine, but it runs the same database solution. Recommendations related to the solution are welcome...it's OpenSource. /Jesper - 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
