Re: MyISAM to InnoDB
What is the basic functionality of the MyISAM, InnoDB etc ? http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MyISAM to InnoDB conversion help
Great, Thank you for your help Rolando, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:41 AM Cc: mysql@lists.mysql.com; Mikhail Berman Subject: Re: MyISAM to InnoDB conversion help I just noticed your innodb_data_file_path You have a shared InnoDB tablespace That can be murder on a MySQL Server You may want to separate each InnoDB into a separate file Here are the steps needed to separate InnoDB tables. 1) Do a mysqldump on your database to mydata.sql. 2) Shutdown MySQL 3) Goto my.cnf and add 'innodb_file_per_table' in the [mysqld] section 4) Change in my.cnf : innodb_data_file_path to ibdata1:10M:autoextend Note: You may want add this too : bulk_insert_buffer_size = 256M 5) Delete ibdata1, ibdata2, and the ib_logfile* files 6) Restart MySQL (the innodb data files and log will regenerate) 7) Run MySQL using the script mydata.sql All InnoDB data will be sitting in separate .ibd files in the database folder. Only the data dictionary info for all InnoDB tables will be sitting in the ibdata1 file. Give it a try. - Original Message - From: Rolando Edwards <[EMAIL PROTECTED]> To: Mikhail Berman <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:24:00 AM GMT-0500 US/Eastern Subject: Re: MyISAM to InnoDB conversion help Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing "INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Ha
Re: MyISAM to InnoDB conversion help
I just noticed your innodb_data_file_path You have a shared InnoDB tablespace That can be murder on a MySQL Server You may want to separate each InnoDB into a separate file Here are the steps needed to separate InnoDB tables. 1) Do a mysqldump on your database to mydata.sql. 2) Shutdown MySQL 3) Goto my.cnf and add 'innodb_file_per_table' in the [mysqld] section 4) Change in my.cnf : innodb_data_file_path to ibdata1:10M:autoextend Note: You may want add this too : bulk_insert_buffer_size = 256M 5) Delete ibdata1, ibdata2, and the ib_logfile* files 6) Restart MySQL (the innodb data files and log will regenerate) 7) Run MySQL using the script mydata.sql All InnoDB data will be sitting in separate .ibd files in the database folder. Only the data dictionary info for all InnoDB tables will be sitting in the ibdata1 file. Give it a try. - Original Message - From: Rolando Edwards <[EMAIL PROTECTED]> To: Mikhail Berman <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:24:00 AM GMT-0500 US/Eastern Subject: Re: MyISAM to InnoDB conversion help Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing "INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/>uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql> status; -- mysql Ver 14.12 Distrib 5
Re: MyISAM to InnoDB conversion help
Check these variable bulk_insert_buffer_size (Default usually 8M) innodb_buffer_pool_size (Default usually 8M) - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 11:13:44 AM GMT-0500 US/Eastern Subject: RE: MyISAM to InnoDB conversion help Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing "INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/>uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql> status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL
RE: MyISAM to InnoDB conversion help
Hi Rolando, Thank you for your help. I am on MySQL 5, and I have tried to do the conversion using ALTER TABLE command. With the same very slow result. Do you by any chance have specific suggestions how to tweak variables related to this? Here is what I got: +-+- -+ | Variable_name | Value | +-+- -+ | innodb_additional_mem_pool_size | 52428800 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 1073741824 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend | | innodb_data_home_dir| /export/home/mysqldata/ibdata | | innodb_doublewrite | ON | | innodb_fast_shutdown| 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout| 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | /export/home/mysqldata/ibdata | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | /export/home/mysqldata/ibdata | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag| 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 20 | | innodb_thread_sleep_delay | 1 | +-+- -+ Best, Mikhail Berman -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 01, 2006 11:05 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB conversion help If you are do this in MySQL 5, try this: ALTER TABLE ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing "INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/>uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql> status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY
Re: MyISAM to InnoDB conversion help
If you are do this in MySQL 5, try this: ALTER TABLE ENGINE = InnoDB; That's all. Let MySQL worry about conversion. You may also want to tweek the innodb system variables (show variables like 'innodb%) for better InnoDB performance prior to trying this. - Original Message - From: Mikhail Berman <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: MyISAM to InnoDB conversion help Hi everyone, I am hoping to get help with extremely slow performance of MyISAM to InnoDB conversion. Or find out if this type of performance is usual I have MyISAM table that contains - 3,299,509 rows and I am trying to convert it to InnoDB for the use with row-level locking, and I am getting insertion speed of 0.243 of a record a second while I am doing "INSERT INTO InnoDB_table SELECT * FROM MyISAM_Table. Your help is appreciated. Here is what my environment looks like. Hardware: SunBlade 2000 with 2GB processor connected to StorEdge A5200 with RAID5 on it. OS: [EMAIL PROTECTED]/>uname -a SunOS * 5.9 Generic_118558-19 sun4u sparc SUNW,Sun-Blade-1000 MySQL: mysql> status; -- mysql Ver 14.12 Distrib 5.0.18, for sun-solaris2.9 (sparc) using readline 5.0 InnoDB tables structure: DAILY_EDGAR_INNODB |CREATE TABLE `DAILY_EDGAR_INNODB` ( `ftp_file_name_key` char(80) NOT NULL default '', `history_record` int(1) NOT NULL default '0', `description` char(100) NOT NULL default '', `company_fkey` char(10) NOT NULL default '', `company_name` char(100) NOT NULL default '', `subject_company_fkey` char(10) NOT NULL default '', `filer_description` char(10) NOT NULL default '', `form_fkey` char(20) NOT NULL default '', `file_accepted` char(20) NOT NULL default '', `been_evaluated` char(20) NOT NULL default '', `uport_evaluated` int(1) NOT NULL default '0', `file_date` char(10) NOT NULL default '', `file_size` char(10) NOT NULL default '50 KB', `accession_number` char(24) NOT NULL default '', `http_file_name_html` char(100) NOT NULL default '', `http_file_name_text` char(100) NOT NULL default '', `create_date` date NOT NULL default '-00-00', `change_date` date NOT NULL default '-00-00', PRIMARY KEY (`ftp_file_name_key`), KEY `company_idx` (`company_fkey`), KEY `filaccdx` (`file_accepted`), KEY `beendx` (`been_evaluated`), KEY `fidadx` (`file_date`), KEY `upevdx` (`uport_evaluated`), KEY `crdadx` (`create_date`), KEY `hiredx` (`history_record`), KEY `accession_number` (`accession_number`), KEY `fofkdx` (`form_fkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Procedure used to execute conversion: root 27686 0.0 0.2 5840 3224 ?S 14:08:23 0:00 mysql -pxx xxx -e insert into DAILY_EDGAR_INNODB select * from DAILY_EDGAR my.cnf InnoDB section: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /export/home/mysqldata/ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend innodb_log_group_home_dir = /export/home/mysqldata/ibdata innodb_log_arch_dir = /export/home/mysqldata/ibdata # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 50M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Best, Mikhail Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
> -Original Message-
> From: Devananda [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 29, 2005 14:56
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: MyISAM to InnoDB
>
>
> Jeff wrote:
> >
> > Well the applications with persistant connections is a
> touchy subject.
> > Our apps send and rec data over satelite links which are very
> > expensive. The shorter the duration of the link the less it
> costs us.
> > So the pervailing theory is that with persistant
> connections the apps
> > will spend less time re-connecting/dis-connecting from the
> db. Even
> > fractions of a second counts when you're talking about thousands of
> > connections a day and we are charged by the second for airtime.
> > That's the whole driving force behind wanting to switch over to
> > InnoDB. The thought is it would give us faster writes when
> we have a
> > hundred apps trying to write at or very near the same time
> because of
> > the record level locking as opposed to the MyISAM Table
> level locking
> > during writes and updates.
> >
> > Now, the question is, if we need to drop the persistant
> connections in
> > order to move to an InnoDB engine, will the speed benefit of record
> > level locking outweigh what is lost by not having persistant
> > connections?
>
> The only way to know is to test it in your environment. I
> don't believe
> anyone on the list could answer that question with certainty.
>
> Just out of curiosity, I wrote a couple scripts in perl to
> very loosely
> test this.
> --
> [EMAIL PROTECTED] - test]# cat loop.sh
> #!/bin/bash
>
> for x in `seq 1 10`; do
> $1
> done
> --
> [EMAIL PROTECTED] - test]# cat con.pl
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> require DBI;
>
> print "Start\n";
> my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
> or die("Failed to connect!");
> print "Connected!\n";
> exit;
> --
> [EMAIL PROTECTED] - test]# cat nocon.pl
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> require DBI;
>
> print "Start\n";
> #my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
> # or die("Failed to connect!");
> print "Skipped Connecting!\n";
> exit;
> ---
> time ./loop.sh ; time ./loop.sh ./nocon.pl >/dev/null; time ./loop.sh
> ./con.pl >/dev/null
>
> ((( bash script overhead )))
> real0m0.004s
> user0m0.002s
> sys 0m0.002s
> ((( perl script with no connection )))
> real0m0.595s
> user0m0.520s
> sys 0m0.057s
> ((( same perl script with connection )))
> real0m0.781s
> user0m0.682s
> sys 0m0.064s
>
> Now, I know this is *far* from an accurate test, and doesn't
> demonstrate
> any of the specifics of your servers, but it does show that, on my
> servers, with perl, there is roughly a 0.02sec real and 0.007sec sys
> overhead to make and close the connection. Take that for what
> you will.
> >
> > That being said and having just looked at our connections
> for the past
> > 60 minutes during what is our roughly our peak time I only
> see about
> > 350 which is roughly one every 10 seconds with a rough avg
> connection
> > time of about 28 seconds most of which is transfer of data
> and not db
> > read/write/updates. So, I believe, from that information I
> can make
> > an educated guess that the MyISAM table locking is not the real
> > bottleneck here and therefore it's probably not going to do
> us a lot
> > of good to switch to InnoDB, especially with our current
> hardware and
> > application behavior. Thoughts?
>
> With one connection every 10 seconds, I don't understand how
> table lock
> contention is a concern, unless your queries are so large
> that they lock
> the table for *that* long. If so, are they properly indexed?
>
> It doesn't sound like that is your problem though, so that's not a
> reason to move to InnoDB.
>
> >
> > At some point however, as our traffic grows we probably will hit a
> > point where the db read/write/updates will start to become a
> > bottleneck and we'll need to look at moving to a 64bit
> arch, >2gig ram
> > and the InnoDB engine. What status variables should I be
> looking at
> > to see if we have a lot of read/write/updates being delayed?
>
Re: MyISAM to InnoDB
Jeff wrote:
Well the applications with persistant connections is a touchy subject.
Our apps send and rec data over satelite links which are very expensive.
The shorter the duration of the link the less it costs us. So the
pervailing theory is that with persistant connections the apps will
spend less time re-connecting/dis-connecting from the db. Even
fractions of a second counts when you're talking about thousands of
connections a day and we are charged by the second for airtime. That's
the whole driving force behind wanting to switch over to InnoDB. The
thought is it would give us faster writes when we have a hundred apps
trying to write at or very near the same time because of the record
level locking as opposed to the MyISAM Table level locking during writes
and updates.
Now, the question is, if we need to drop the persistant connections in
order to move to an InnoDB engine, will the speed benefit of record
level locking outweigh what is lost by not having persistant
connections?
The only way to know is to test it in your environment. I don't believe
anyone on the list could answer that question with certainty.
Just out of curiosity, I wrote a couple scripts in perl to very loosely
test this.
--
[EMAIL PROTECTED] - test]# cat loop.sh
#!/bin/bash
for x in `seq 1 10`; do
$1
done
--
[EMAIL PROTECTED] - test]# cat con.pl
#!/usr/bin/perl
use strict;
use warnings;
require DBI;
print "Start\n";
my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
or die("Failed to connect!");
print "Connected!\n";
exit;
--
[EMAIL PROTECTED] - test]# cat nocon.pl
#!/usr/bin/perl
use strict;
use warnings;
require DBI;
print "Start\n";
#my $con = DBI->connect("DBI:mysql::db3-p", 'user', 'pass')
# or die("Failed to connect!");
print "Skipped Connecting!\n";
exit;
---
time ./loop.sh ; time ./loop.sh ./nocon.pl >/dev/null; time ./loop.sh
./con.pl >/dev/null
((( bash script overhead )))
real0m0.004s
user0m0.002s
sys 0m0.002s
((( perl script with no connection )))
real0m0.595s
user0m0.520s
sys 0m0.057s
((( same perl script with connection )))
real0m0.781s
user0m0.682s
sys 0m0.064s
Now, I know this is *far* from an accurate test, and doesn't demonstrate
any of the specifics of your servers, but it does show that, on my
servers, with perl, there is roughly a 0.02sec real and 0.007sec sys
overhead to make and close the connection. Take that for what you will.
That being said and having just looked at our connections for the past
60 minutes during what is our roughly our peak time I only see about 350
which is roughly one every 10 seconds with a rough avg connection time
of about 28 seconds most of which is transfer of data and not db
read/write/updates. So, I believe, from that information I can make an
educated guess that the MyISAM table locking is not the real bottleneck
here and therefore it's probably not going to do us a lot of good to
switch to InnoDB, especially with our current hardware and application
behavior. Thoughts?
With one connection every 10 seconds, I don't understand how table lock
contention is a concern, unless your queries are so large that they lock
the table for *that* long. If so, are they properly indexed?
It doesn't sound like that is your problem though, so that's not a
reason to move to InnoDB.
At some point however, as our traffic grows we probably will hit a point
where the db read/write/updates will start to become a bottleneck and
we'll need to look at moving to a 64bit arch, >2gig ram and the InnoDB
engine. What status variables should I be looking at to see if we have
a lot of read/write/updates being delayed?
See http://dev.mysql.com/doc/mysql/en/internal-locking.html
and http://dev.mysql.com/doc/mysql/en/show-status.html
"Table_locks_immediate
The number of times that a table lock was acquired immediately. This
variable was added as of MySQL 3.23.33.
Table_locks_waited
The number of times that a table lock could not be acquired immediately
and a wait was needed. If this is high, and you have performance
problems, you should first optimize your queries, and then either split
your table or tables or use replication. This variable was added as of
MySQL 3.23.33."
For example, this is from our MyISAM server (uptime 200days, 7% selects,
very un-optimized but still performs well enough),
mysql> show status like 'table%';
Table_locks_immediate 12810013
Table_locks_waited 306450
Hope that helps!
Devananda vdv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
Hi, 2005/9/23, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: > "Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 09:57:06 AM: > > > Yes, I meant exactly that. Within each MySQL server is a "special" > database called `mysql`. That is the database that contains the tables of > all of the user login and permission information for the server (and > several other important bits of system-wide metadata). None of the tables > in that database can be converted to InnoDB. That would be a "bad thing". > > The tables of every OTHER database on the server (including yours) are > eligible for InnoDB conversion so long as you do not want to use fulltext > searching. If you need a FT index, you have to keep that table as MyISAM > for now (they are working on enabling FT indexes in InnoDB but there is no > release date yet) > And GIS as well, IIRC: http://dev.mysql.com/doc/mysql/en/spatial-extensions-in-mysql.html (just to be picky) -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
> "Jeff" <[EMAIL PROTECTED]> wrote on 09/29/2005 08:47:52 AM: > > > > > > Jeff wrote: > > > > > > > > Ugh... > > > > mysqladmin -uroot -ptelaurus processlist | grep -c > "Sleep" And it > > > > returned 200 sleeping connections, all persistant > > > connections > > > > from our app servers and 4 threads_running > > > > Also a show status gave me a max_used_connections of 236. If > > > > that's the case then I can probably only set it to about > > > 250 which > > > > means if I set my innodb_buffer_pool_size = 100M and > dropping my > > > > key_buffer_size to 250, I'll need 1884M of ram according to the > > > > formula above, which is dangerously close to the 2G limit > > > specified in > > > > the warning on the link above. > > > > > > > > Currently the key_reads to Key_reads_requests is about > > > 1:1970 with the > > > > key_buffer_size of 384M, so I guess I can safely drop > this to 250M > > > > > > > > Even if I changed the entire DB over to InnoDB, and pushed the > > > > key_buffer_size down really low it wouldn't drop the > total memory > > > > usage below 1600M. > > > > > > > > So what is this telling me? I need more ram or less > > > connections or I > > > > should just stay with MyISAM? > > > > > > > > Thanks, > > > > > > > > Jeff > > > > > > > > > > I would suggest taking a hard look at why your application > > > servers are > > > creating 200 sleeping connections, and if that is > necessary. You may > > > also be able to reduce sort_ and read_buffer_size to 1M > each, but I > > > couldn't tell you how that might affect your application, so > > > you may not > > > want to do that. (Does anyone on the list have experience > > > modifying these?) > > > > > > I think the biggest issue will be the system's RAM - the > 2G limit on > > > MySQL's total allocated RAM is a per-process hard limit on 32-bit > > > architecture, but most 32-bit systems benefit greatly from > > > having more > > > than 2G total RAM (the OS may use the rest for disk caching, > > > etc). If, > > > say, your server had 4G RAM, then you could safely > configure MySQL to > > > use very close to 2G, and performance should fly. With > only 2G in the > > > system, setting MySQL to use as much RAM as possible would > > > leave next to > > > nothing for the OS or other processes, and that is the > > > problem (as I see > > > it). > > > > > > However, that said, more RAM is not always the answer. You > > > may get much > > > more of a performance increase by modifying your > application code so > > > that it doesn't "waste" so many connections (thus allowing you to > > > allocate plenty of RAM to the innodb_buffer_pool). > > > > > > Of course, you can do both (just to play it safe, right?). ;) > > > > > > > Well the applications with persistant connections is a > touchy subject. > > Our apps send and rec data over satelite links which are very > > expensive. The shorter the duration of the link the less it > costs us. > > So the pervailing theory is that with persistant > connections the apps > > will spend less time re-connecting/dis-connecting from the > db. Even > > fractions of a second counts when you're talking about thousands of > > connections a day and we are charged by the second for airtime. > > And all of those sleeping connections are costing you how > much in unused > air time? I think there's a missunderstanding here. The applications run on servers in our datacenter and wait for client connections to call in. The "client" which is another server on the other end of the sat link, transfers the data to our apps and our apps send data to it (depending on whether or not data is waiting for it, one of the db queries tells the local app this) and the local apps in turn write the connection information to the database. >Compared with many other databases, the cost (time > and data) of > making and breaking a MySQL connection is cheap. Try a small > set of test > cases and see for yourself. Maybe you could move 10 of your > normal clients > from using your persistent connections into a > connect-as-needed model and > see what that does to your air-time, sleeping connection > counts, and total > throughput. > > The only way to know for certain is to try it in your > environment but I > know that in the world of web development (where connections are also > precious and throughput is king) that being connected only > when necessary > usually works much better than trying to stay connected all > of the time. > By minimizing the communications overhead imposed on the server by > maintaining unused open connections, the server should be > able to respond > better. You should not only have less "dead air" but each connection > itself will take less time as the server will be more responsive. > > Remember, I recommend making and breaking connections around > blocks of > execution not per-statement. Let's say you have a "lookup" > routine tha
RE: MyISAM to InnoDB
"Jeff" <[EMAIL PROTECTED]> wrote on 09/29/2005 08:47:52 AM: > > > Jeff wrote: > > > > > > Ugh... > > > mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" > > > And it returned 200 sleeping connections, all persistant > > connections > > > from our app servers and 4 threads_running > > > Also a show status gave me a max_used_connections of 236. > > > If that's the case then I can probably only set it to about > > 250 which > > > means if I set my innodb_buffer_pool_size = 100M and dropping my > > > key_buffer_size to 250, I'll need 1884M of ram according to the > > > formula above, which is dangerously close to the 2G limit > > specified in > > > the warning on the link above. > > > > > > Currently the key_reads to Key_reads_requests is about > > 1:1970 with the > > > key_buffer_size of 384M, so I guess I can safely drop this to 250M > > > > > > Even if I changed the entire DB over to InnoDB, and pushed the > > > key_buffer_size down really low it wouldn't drop the total memory > > > usage below 1600M. > > > > > > So what is this telling me? I need more ram or less > > connections or I > > > should just stay with MyISAM? > > > > > > Thanks, > > > > > > Jeff > > > > > > > I would suggest taking a hard look at why your application > > servers are > > creating 200 sleeping connections, and if that is necessary. You may > > also be able to reduce sort_ and read_buffer_size to 1M each, but I > > couldn't tell you how that might affect your application, so > > you may not > > want to do that. (Does anyone on the list have experience > > modifying these?) > > > > I think the biggest issue will be the system's RAM - the 2G limit on > > MySQL's total allocated RAM is a per-process hard limit on 32-bit > > architecture, but most 32-bit systems benefit greatly from > > having more > > than 2G total RAM (the OS may use the rest for disk caching, > > etc). If, > > say, your server had 4G RAM, then you could safely configure MySQL to > > use very close to 2G, and performance should fly. With only 2G in the > > system, setting MySQL to use as much RAM as possible would > > leave next to > > nothing for the OS or other processes, and that is the > > problem (as I see > > it). > > > > However, that said, more RAM is not always the answer. You > > may get much > > more of a performance increase by modifying your application code so > > that it doesn't "waste" so many connections (thus allowing you to > > allocate plenty of RAM to the innodb_buffer_pool). > > > > Of course, you can do both (just to play it safe, right?). ;) > > > > Well the applications with persistant connections is a touchy subject. > Our apps send and rec data over satelite links which are very expensive. > The shorter the duration of the link the less it costs us. So the > pervailing theory is that with persistant connections the apps will > spend less time re-connecting/dis-connecting from the db. Even > fractions of a second counts when you're talking about thousands of > connections a day and we are charged by the second for airtime. And all of those sleeping connections are costing you how much in unused air time? Compared with many other databases, the cost (time and data) of making and breaking a MySQL connection is cheap. Try a small set of test cases and see for yourself. Maybe you could move 10 of your normal clients from using your persistent connections into a connect-as-needed model and see what that does to your air-time, sleeping connection counts, and total throughput. The only way to know for certain is to try it in your environment but I know that in the world of web development (where connections are also precious and throughput is king) that being connected only when necessary usually works much better than trying to stay connected all of the time. By minimizing the communications overhead imposed on the server by maintaining unused open connections, the server should be able to respond better. You should not only have less "dead air" but each connection itself will take less time as the server will be more responsive. Remember, I recommend making and breaking connections around blocks of execution not per-statement. Let's say you have a "lookup" routine that uses 6 queries and massages the data into something useful client-side. It makes no sense to flip a connection 6 times for those 6 queries as they are all part of one larger process. Prepare your SQL statements as much as possible, make one connection, run the 6 queries, cache the results, drop the connection, process the results from cache. Another trick to maximizing connection usage is to make a few trips to the server as necessary. Using the same scenario I just described, if 4 of those queries did not contain data useful to the user but were used primarily to build the results of the final 2 queries, you may be able to cache the results of the first queries server-side, minimizing the
RE: MyISAM to InnoDB
> Jeff wrote: > > > > Ugh... > > > > mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" > > > > And it returned 200 sleeping connections, all persistant > connections > > from our app servers and 4 threads_running > > > > Also a show status gave me a max_used_connections of 236. > > > > If that's the case then I can probably only set it to about > 250 which > > means if I set my innodb_buffer_pool_size = 100M and dropping my > > key_buffer_size to 250, I'll need 1884M of ram according to the > > formula above, which is dangerously close to the 2G limit > specified in > > the warning on the link above. > > > > Currently the key_reads to Key_reads_requests is about > 1:1970 with the > > key_buffer_size of 384M, so I guess I can safely drop this to 250M > > > > Even if I changed the entire DB over to InnoDB, and pushed the > > key_buffer_size down really low it wouldn't drop the total memory > > usage below 1600M. > > > > So what is this telling me? I need more ram or less > connections or I > > should just stay with MyISAM? > > > > Thanks, > > > > Jeff > > > > I would suggest taking a hard look at why your application > servers are > creating 200 sleeping connections, and if that is necessary. You may > also be able to reduce sort_ and read_buffer_size to 1M each, but I > couldn't tell you how that might affect your application, so > you may not > want to do that. (Does anyone on the list have experience > modifying these?) > > I think the biggest issue will be the system's RAM - the 2G limit on > MySQL's total allocated RAM is a per-process hard limit on 32-bit > architecture, but most 32-bit systems benefit greatly from > having more > than 2G total RAM (the OS may use the rest for disk caching, > etc). If, > say, your server had 4G RAM, then you could safely configure MySQL to > use very close to 2G, and performance should fly. With only 2G in the > system, setting MySQL to use as much RAM as possible would > leave next to > nothing for the OS or other processes, and that is the > problem (as I see > it). > > However, that said, more RAM is not always the answer. You > may get much > more of a performance increase by modifying your application code so > that it doesn't "waste" so many connections (thus allowing you to > allocate plenty of RAM to the innodb_buffer_pool). > > Of course, you can do both (just to play it safe, right?). ;) > Well the applications with persistant connections is a touchy subject. Our apps send and rec data over satelite links which are very expensive. The shorter the duration of the link the less it costs us. So the pervailing theory is that with persistant connections the apps will spend less time re-connecting/dis-connecting from the db. Even fractions of a second counts when you're talking about thousands of connections a day and we are charged by the second for airtime. That's the whole driving force behind wanting to switch over to InnoDB. The thought is it would give us faster writes when we have a hundred apps trying to write at or very near the same time because of the record level locking as opposed to the MyISAM Table level locking during writes and updates. Now, the question is, if we need to drop the persistant connections in order to move to an InnoDB engine, will the speed benefit of record level locking outweigh what is lost by not having persistant connections? That being said and having just looked at our connections for the past 60 minutes during what is our roughly our peak time I only see about 350 which is roughly one every 10 seconds with a rough avg connection time of about 28 seconds most of which is transfer of data and not db read/write/updates. So, I believe, from that information I can make an educated guess that the MyISAM table locking is not the real bottleneck here and therefore it's probably not going to do us a lot of good to switch to InnoDB, especially with our current hardware and application behavior. Thoughts? At some point however, as our traffic grows we probably will hit a point where the db read/write/updates will start to become a bottleneck and we'll need to look at moving to a 64bit arch, >2gig ram and the InnoDB engine. What status variables should I be looking at to see if we have a lot of read/write/updates being delayed? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
On Sep 28, 2005, at 5:21 PM, Devananda wrote: Jeff wrote: Lots of stuff goes in here... So without going into specifics here... your sort_buffer and read_buffer become pretty much unimportant if you move everything to InnoDB... keeping in mind the earlier advice to leave the mysql database intact in myisam. So if you went that route you can set them to like.. 32k or something trivial and even the 250 x multiplier doesn't hurt you much here. Once you do that of course you should give InnoDB most of the memory. On the disk side the one comment I didn't see is that if you have 2.2G of data already, you need 4.5G of InnoDB table space to put it all together. InnoDB needs (as a rough guideline) twice as much table space as there is data, so it can do things like indexes, and undo logs and so on... Your 2 x 2G files isn't going to cut it as a place to put your 2.2G of data. In your case for these guys I'd throw 3 x 2G files at it and a fourth auto extending file - more if you move your other tables... most of our database instances had 20 x 2G files in their InnoDB table space before we moved to innodb_file_per_table where it stopped being an issue. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
Jeff wrote: Ugh... mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" And it returned 200 sleeping connections, all persistant connections from our app servers and 4 threads_running Also a show status gave me a max_used_connections of 236. If that's the case then I can probably only set it to about 250 which means if I set my innodb_buffer_pool_size = 100M and dropping my key_buffer_size to 250, I'll need 1884M of ram according to the formula above, which is dangerously close to the 2G limit specified in the warning on the link above. Currently the key_reads to Key_reads_requests is about 1:1970 with the key_buffer_size of 384M, so I guess I can safely drop this to 250M Even if I changed the entire DB over to InnoDB, and pushed the key_buffer_size down really low it wouldn't drop the total memory usage below 1600M. So what is this telling me? I need more ram or less connections or I should just stay with MyISAM? Thanks, Jeff I would suggest taking a hard look at why your application servers are creating 200 sleeping connections, and if that is necessary. You may also be able to reduce sort_ and read_buffer_size to 1M each, but I couldn't tell you how that might affect your application, so you may not want to do that. (Does anyone on the list have experience modifying these?) I think the biggest issue will be the system's RAM - the 2G limit on MySQL's total allocated RAM is a per-process hard limit on 32-bit architecture, but most 32-bit systems benefit greatly from having more than 2G total RAM (the OS may use the rest for disk caching, etc). If, say, your server had 4G RAM, then you could safely configure MySQL to use very close to 2G, and performance should fly. With only 2G in the system, setting MySQL to use as much RAM as possible would leave next to nothing for the OS or other processes, and that is the problem (as I see it). However, that said, more RAM is not always the answer. You may get much more of a performance increase by modifying your application code so that it doesn't "waste" so many connections (thus allowing you to allocate plenty of RAM to the innodb_buffer_pool). Of course, you can do both (just to play it safe, right?). ;) Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
"Jeff" <[EMAIL PROTECTED]> wrote on 09/28/2005 02:24:48 PM: > > -Original Message- > > From: Devananda [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, September 28, 2005 13:06 > > To: Jeff > > Cc: mysql@lists.mysql.com > > Subject: Re: MyISAM to InnoDB > > > > > > Jeff wrote: > > >> Cut orignal thread because it was too long > > > > > > > > > The system is only used as a database server, it's a dual processor > > > system with 2gig of ram. > > > > > > As you can see, Table1's MyISAM data file is quite large at > > 2.1 gig. > > > Taking this into account what size InnoDB data files should I > > > configure in my my.cnf file? > > > > > > I was thinking of this: > > > > > > My.cnf > > > > > > > > > > > > [mysqld] > > > > > > datadir=/var/lib/mysql > > > socket=/var/lib/mysql/mysql.sock > > > log-bin > > > server-id=70 > > > port = 3306 > > > skip-locking > > > key_buffer = 384M > > > max_allowed_packet = 1M > > > table_cache = 512 > > > sort_buffer_size = 2M > > > read_buffer_size = 2M > > > myisam_sort_buffer_size = 64M > > > thread_cache = 8 > > > query_cache_size = 32M > > > # Try number of CPU's*2 for thread_concurrency > > thread_concurrency = 4 > > > set-variable= max_connections=500 > > > > > > ### InnoDB setup ### > > > > > > # use default data directory for database innodb_data_home_dir = > > > /DATA/dbdata/ innodb_data_file_path = > > > /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G > > > innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs > > > > > > innodb_buffer_pool_size = 1G > > > innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 3 > > > innodb_log_file_size = 500M > > > innodb_log_buffer_size = 8M > > > innodb_buffer_pool_size = 1.5G > > ((( duplicate setting, later-occurring one will take precedence ))) > > > innodb_additional_mem_pool_size = 2M > > > innodb_file_io_threads = 4 > > > > > > > > > > > > But what happens if the ibdata2 fills up to the max of 2G? > > I've got 50 > > > gig available on the partition where the db data is stored. > > > > > > Is there anything else here that looks incorrect? > > > > > > Thanks, > > > > > > Jeff > > > > > > > I agree with what Sujay suggested: you can set the > > innodb_log_file_size > > much smaller, and will get the same performance with better start-up > > time. 100M x 3 log_files_in_group should be fine. Also I recommend > > setting up your ibdata files large enough to anticipate need > > initially. > > If, or once, they are full, you will not be able to write to > > tables in > > InnoDB, so make sure that does not happen! > > > > However, I see a potential problem - you said your system only has 2G > > RAM. Here's the formula for how much RAM MySQL can (worst case) use, > > taken from http://dev.mysql.com/doc/mysql/en/innodb-configuration.html > > > > innodb_buffer_pool_size > > + key_buffer_size > > + > > max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) > > + max_connections*2MB > > > > 1024M ((( assuming you meant 1G and not 1.5G ))) > > + 384M > > + 500 * (2M + 2M + ??) > > + 500 * 2M > > > > According to your config, this results in a minimum of 1408M + 6M * > > current_connections. That doesn't leave much RAM for the > > underlying OS > > and any other processes running. And, far worse, if your application > > servers attempted to establish more than 100 connections, MySQL could > > not allocate enough memory for them, and would either crash > > or deny new > > connections. > > > > You need to adjust something in the formula - reduce > > max_connections if > > that is possible, or reduce the key_buffer_size if you do not > > need to be > > working with MyISAM tables on this server, or allocate less memory to > > innodb_buffer_pool_size. > > > > > > Best Regards, > > Devananda > > > > Ugh... > > mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" > > And it returned 200 sleeping connections, all persistant connections > from our app servers and 4
RE: MyISAM to InnoDB
> -Original Message- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 13:06 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > >> Cut orignal thread because it was too long > > > > > > The system is only used as a database server, it's a dual processor > > system with 2gig of ram. > > > > As you can see, Table1's MyISAM data file is quite large at > 2.1 gig. > > Taking this into account what size InnoDB data files should I > > configure in my my.cnf file? > > > > I was thinking of this: > > > > My.cnf > > > > > > > > [mysqld] > > > > datadir=/var/lib/mysql > > socket=/var/lib/mysql/mysql.sock > > log-bin > > server-id=70 > > port = 3306 > > skip-locking > > key_buffer = 384M > > max_allowed_packet = 1M > > table_cache = 512 > > sort_buffer_size = 2M > > read_buffer_size = 2M > > myisam_sort_buffer_size = 64M > > thread_cache = 8 > > query_cache_size = 32M > > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 4 > > set-variable= max_connections=500 > > > > ### InnoDB setup ### > > > > # use default data directory for database innodb_data_home_dir = > > /DATA/dbdata/ innodb_data_file_path = > > /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G > > innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs > > > > innodb_buffer_pool_size = 1G > > innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 3 > > innodb_log_file_size = 500M > > innodb_log_buffer_size = 8M > > innodb_buffer_pool_size = 1.5G > ((( duplicate setting, later-occurring one will take precedence ))) > > innodb_additional_mem_pool_size = 2M > > innodb_file_io_threads = 4 > > > > > > > > But what happens if the ibdata2 fills up to the max of 2G? > I've got 50 > > gig available on the partition where the db data is stored. > > > > Is there anything else here that looks incorrect? > > > > Thanks, > > > > Jeff > > > > I agree with what Sujay suggested: you can set the > innodb_log_file_size > much smaller, and will get the same performance with better start-up > time. 100M x 3 log_files_in_group should be fine. Also I recommend > setting up your ibdata files large enough to anticipate need > initially. > If, or once, they are full, you will not be able to write to > tables in > InnoDB, so make sure that does not happen! > > However, I see a potential problem - you said your system only has 2G > RAM. Here's the formula for how much RAM MySQL can (worst case) use, > taken from http://dev.mysql.com/doc/mysql/en/innodb-configuration.html > > innodb_buffer_pool_size > + key_buffer_size > + > max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) > + max_connections*2MB > > 1024M ((( assuming you meant 1G and not 1.5G ))) > + 384M > + 500 * (2M + 2M + ??) > + 500 * 2M > > According to your config, this results in a minimum of 1408M + 6M * > current_connections. That doesn't leave much RAM for the > underlying OS > and any other processes running. And, far worse, if your application > servers attempted to establish more than 100 connections, MySQL could > not allocate enough memory for them, and would either crash > or deny new > connections. > > You need to adjust something in the formula - reduce > max_connections if > that is possible, or reduce the key_buffer_size if you do not > need to be > working with MyISAM tables on this server, or allocate less memory to > innodb_buffer_pool_size. > > > Best Regards, > Devananda > Ugh... mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" And it returned 200 sleeping connections, all persistant connections from our app servers and 4 threads_running Also a show status gave me a max_used_connections of 236. If that's the case then I can probably only set it to about 250 which means if I set my innodb_buffer_pool_size = 100M and dropping my key_buffer_size to 250, I'll need 1884M of ram according to the formula above, which is dangerously close to the 2G limit specified in the warning on the link above. Currently the key_reads to Key_reads_requests is about 1:1970 with the key_buffer_size of 384M, so I guess I can safely drop this to 250M Even if I changed the entire DB over to InnoDB, and pushed the key_buffer_size down really low it wouldn't drop the total memory usage below 1600M. So what is this telling me? I need more ram or less connections or I should just stay with MyISAM? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
> -Original Message- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 13:06 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > >> Cut orignal thread because it was too long > > > > > > The system is only used as a database server, it's a dual processor > > system with 2gig of ram. > > > > As you can see, Table1's MyISAM data file is quite large at > 2.1 gig. > > Taking this into account what size InnoDB data files should I > > configure in my my.cnf file? > > > > I was thinking of this: > > > > My.cnf > > > > > > > > [mysqld] > > > > datadir=/var/lib/mysql > > socket=/var/lib/mysql/mysql.sock > > log-bin > > server-id=70 > > port = 3306 > > skip-locking > > key_buffer = 384M > > max_allowed_packet = 1M > > table_cache = 512 > > sort_buffer_size = 2M > > read_buffer_size = 2M > > myisam_sort_buffer_size = 64M > > thread_cache = 8 > > query_cache_size = 32M > > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 4 > > set-variable= max_connections=500 > > > > ### InnoDB setup ### > > > > # use default data directory for database innodb_data_home_dir = > > /DATA/dbdata/ innodb_data_file_path = > > /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G > > innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs > > > > innodb_buffer_pool_size = 1G > > innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 3 > > innodb_log_file_size = 500M > > innodb_log_buffer_size = 8M > > innodb_buffer_pool_size = 1.5G > ((( duplicate setting, later-occurring one will take precedence ))) > > innodb_additional_mem_pool_size = 2M > > innodb_file_io_threads = 4 > > > > > > > > But what happens if the ibdata2 fills up to the max of 2G? > I've got 50 > > gig available on the partition where the db data is stored. > > > > Is there anything else here that looks incorrect? > > > > Thanks, > > > > Jeff > > > > I agree with what Sujay suggested: you can set the > innodb_log_file_size > much smaller, and will get the same performance with better start-up > time. 100M x 3 log_files_in_group should be fine. Also I recommend > setting up your ibdata files large enough to anticipate need > initially. > If, or once, they are full, you will not be able to write to > tables in > InnoDB, so make sure that does not happen! > > However, I see a potential problem - you said your system only has 2G > RAM. Here's the formula for how much RAM MySQL can (worst case) use, > taken from http://dev.mysql.com/doc/mysql/en/innodb-configuration.html > > innodb_buffer_pool_size > + key_buffer_size > + > max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) > + max_connections*2MB > > 1024M ((( assuming you meant 1G and not 1.5G ))) > + 384M > + 500 * (2M + 2M + ??) > + 500 * 2M > > According to your config, this results in a minimum of 1408M + 6M * > current_connections. That doesn't leave much RAM for the > underlying OS > and any other processes running. And, far worse, if your application > servers attempted to establish more than 100 connections, MySQL could > not allocate enough memory for them, and would either crash > or deny new > connections. > > You need to adjust something in the formula - reduce > max_connections if > that is possible, or reduce the key_buffer_size if you do not > need to be > working with MyISAM tables on this server, or allocate less memory to > innodb_buffer_pool_size. > > > Best Regards, > Devananda > Since this server will have InnoDB and MyISAM tables, 100+ connections, I'll need to reduce the innodb_buffer_pool_size. I can possibly drop the max_connections to 250 as well. Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
Jeff wrote: Cut orignal thread because it was too long The system is only used as a database server, it's a dual processor system with 2gig of ram. As you can see, Table1's MyISAM data file is quite large at 2.1 gig. Taking this into account what size InnoDB data files should I configure in my my.cnf file? I was thinking of this: My.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin server-id=70 port = 3306 skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 set-variable= max_connections=500 ### InnoDB setup ### # use default data directory for database innodb_data_home_dir = /DATA/dbdata/ innodb_data_file_path = /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 3 innodb_log_file_size = 500M innodb_log_buffer_size = 8M innodb_buffer_pool_size = 1.5G ((( duplicate setting, later-occurring one will take precedence ))) innodb_additional_mem_pool_size = 2M innodb_file_io_threads = 4 But what happens if the ibdata2 fills up to the max of 2G? I've got 50 gig available on the partition where the db data is stored. Is there anything else here that looks incorrect? Thanks, Jeff I agree with what Sujay suggested: you can set the innodb_log_file_size much smaller, and will get the same performance with better start-up time. 100M x 3 log_files_in_group should be fine. Also I recommend setting up your ibdata files large enough to anticipate need initially. If, or once, they are full, you will not be able to write to tables in InnoDB, so make sure that does not happen! However, I see a potential problem - you said your system only has 2G RAM. Here's the formula for how much RAM MySQL can (worst case) use, taken from http://dev.mysql.com/doc/mysql/en/innodb-configuration.html innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB 1024M ((( assuming you meant 1G and not 1.5G ))) + 384M + 500 * (2M + 2M + ??) + 500 * 2M According to your config, this results in a minimum of 1408M + 6M * current_connections. That doesn't leave much RAM for the underlying OS and any other processes running. And, far worse, if your application servers attempted to establish more than 100 connections, MySQL could not allocate enough memory for them, and would either crash or deny new connections. You need to adjust something in the formula - reduce max_connections if that is possible, or reduce the key_buffer_size if you do not need to be working with MyISAM tables on this server, or allocate less memory to innodb_buffer_pool_size. Best Regards, Devananda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
One more thing, noneed to give so much space for each logfile. The combined size of all log files should be around 25-50% of innodb_buffer_pool size. So you can reduce the size of each log file to 100M. You can specify that for storing log files. sujay -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 9:04 PM To: 'Sujay Koduri'; mysql@lists.mysql.com Subject: RE: MyISAM to InnoDB > -Original Message- > From: Sujay Koduri [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 11:04 > To: Jeff; mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: RE: MyISAM to InnoDB > > > > If you think your storage requiremnets will increase in future, try to > estimate how much you will be needing in the future in the worst case > and try allocating that much of disk space now itself (Any way you > have good amount of disk space left). > Try creating a different partition for storing the log files. > This will increase the performance > Well currently MySQL is set up in the default dir of /var/lib/mysql and soft links to the database data residing on another partition /DATA/. Should I maybe specify: innodb_log_group_home_dir = /var/lib/mysql/iblogs/ I have about 9 gig available on /var so 1.5 gig of logs shouldn't be too bad. > Even if you don't do this and run out of space, you just have > to add more add data files and a restart the server. > > And for 2G RAM, its better to limit the > innodb_bufferpool_size to 1G. You can also look at the > query_cache_size parameter and try tuning that by running > some load tests. > > Apart from that everything is looking fine for me > > sujay > > -Original Message- > From: Jeff [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 8:22 PM > To: mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: RE: MyISAM to InnoDB > > > Cut orignal thread because it was too long > > Ok so I'm about to convert two tables in my database from > MyISAM to InnoDB. They are currently: > > 14K Sep 15 13:15 Table1.frm > 2.1G Sep 28 14:15 Table1.MYD > 198M Sep 28 14:15 Table1.MYI > > 11K Sep 20 08:45 Table2.frm > 424K Sep 28 14:15 Table2.MYD > 110K Sep 28 14:15 Table2.MYI > > The system is only used as a database server, it's a dual > processor system with 2gig of ram. > > As you can see, Table1's MyISAM data file is quite large at > 2.1 gig. Taking this into account what size InnoDB data files > should I configure in my my.cnf file? > > I was thinking of this: > > My.cnf > > > > [mysqld] > > datadir=/var/lib/mysql > socket=/var/lib/mysql/mysql.sock > log-bin > server-id=70 > port = 3306 > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 4 set-variable= max_connections=500 > > ### InnoDB setup ### > > # use default data directory for database > innodb_data_home_dir = /DATA/dbdata/ > innodb_data_file_path = /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G > innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs > > innodb_buffer_pool_size = 1G > innodb_additional_mem_pool_size = 20M > innodb_log_files_in_group = 3 > innodb_log_file_size = 500M > innodb_log_buffer_size = 8M > innodb_buffer_pool_size = 1.5G > innodb_additional_mem_pool_size = 2M > innodb_file_io_threads = 4 > > > > But what happens if the ibdata2 fills up to the max of 2G? > I've got 50 gig available on the partition where the db data > is stored. > > Is there anything else here that looks incorrect? > > Thanks, > > Jeff > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
> -Original Message- > From: Sujay Koduri [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 11:04 > To: Jeff; mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: RE: MyISAM to InnoDB > > > > If you think your storage requiremnets will increase in > future, try to estimate how much you will be needing in the > future in the worst case and try allocating that much of disk > space now itself (Any way you have good amount of disk space left). > Try creating a different partition for storing the log files. > This will increase the performance > Well currently MySQL is set up in the default dir of /var/lib/mysql and soft links to the database data residing on another partition /DATA/. Should I maybe specify: innodb_log_group_home_dir = /var/lib/mysql/iblogs/ I have about 9 gig available on /var so 1.5 gig of logs shouldn't be too bad. > Even if you don't do this and run out of space, you just have > to add more add data files and a restart the server. > > And for 2G RAM, its better to limit the > innodb_bufferpool_size to 1G. You can also look at the > query_cache_size parameter and try tuning that by running > some load tests. > > Apart from that everything is looking fine for me > > sujay > > -Original Message- > From: Jeff [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 8:22 PM > To: mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: RE: MyISAM to InnoDB > > > Cut orignal thread because it was too long > > Ok so I'm about to convert two tables in my database from > MyISAM to InnoDB. They are currently: > > 14K Sep 15 13:15 Table1.frm > 2.1G Sep 28 14:15 Table1.MYD > 198M Sep 28 14:15 Table1.MYI > > 11K Sep 20 08:45 Table2.frm > 424K Sep 28 14:15 Table2.MYD > 110K Sep 28 14:15 Table2.MYI > > The system is only used as a database server, it's a dual > processor system with 2gig of ram. > > As you can see, Table1's MyISAM data file is quite large at > 2.1 gig. Taking this into account what size InnoDB data files > should I configure in my my.cnf file? > > I was thinking of this: > > My.cnf > > > > [mysqld] > > datadir=/var/lib/mysql > socket=/var/lib/mysql/mysql.sock > log-bin > server-id=70 > port = 3306 > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 4 set-variable= max_connections=500 > > ### InnoDB setup ### > > # use default data directory for database > innodb_data_home_dir = /DATA/dbdata/ > innodb_data_file_path = /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G > innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs > > innodb_buffer_pool_size = 1G > innodb_additional_mem_pool_size = 20M > innodb_log_files_in_group = 3 > innodb_log_file_size = 500M > innodb_log_buffer_size = 8M > innodb_buffer_pool_size = 1.5G > innodb_additional_mem_pool_size = 2M > innodb_file_io_threads = 4 > > > > But what happens if the ibdata2 fills up to the max of 2G? > I've got 50 gig available on the partition where the db data > is stored. > > Is there anything else here that looks incorrect? > > Thanks, > > Jeff > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
If you think your storage requiremnets will increase in future, try to estimate how much you will be needing in the future in the worst case and try allocating that much of disk space now itself (Any way you have good amount of disk space left). Try creating a different partition for storing the log files. This will increase the performance Even if you don't do this and run out of space, you just have to add more add data files and a restart the server. And for 2G RAM, its better to limit the innodb_bufferpool_size to 1G. You can also look at the query_cache_size parameter and try tuning that by running some load tests. Apart from that everything is looking fine for me sujay -Original Message- From: Jeff [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 28, 2005 8:22 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: RE: MyISAM to InnoDB > Cut orignal thread because it was too long Ok so I'm about to convert two tables in my database from MyISAM to InnoDB. They are currently: 14K Sep 15 13:15 Table1.frm 2.1G Sep 28 14:15 Table1.MYD 198M Sep 28 14:15 Table1.MYI 11K Sep 20 08:45 Table2.frm 424K Sep 28 14:15 Table2.MYD 110K Sep 28 14:15 Table2.MYI The system is only used as a database server, it's a dual processor system with 2gig of ram. As you can see, Table1's MyISAM data file is quite large at 2.1 gig. Taking this into account what size InnoDB data files should I configure in my my.cnf file? I was thinking of this: My.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin server-id=70 port = 3306 skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 set-variable= max_connections=500 ### InnoDB setup ### # use default data directory for database innodb_data_home_dir = /DATA/dbdata/ innodb_data_file_path = /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 3 innodb_log_file_size = 500M innodb_log_buffer_size = 8M innodb_buffer_pool_size = 1.5G innodb_additional_mem_pool_size = 2M innodb_file_io_threads = 4 But what happens if the ibdata2 fills up to the max of 2G? I've got 50 gig available on the partition where the db data is stored. Is there anything else here that looks incorrect? Thanks, Jeff -- 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: MyISAM to InnoDB
> Cut orignal thread because it was too long Ok so I'm about to convert two tables in my database from MyISAM to InnoDB. They are currently: 14K Sep 15 13:15 Table1.frm 2.1G Sep 28 14:15 Table1.MYD 198M Sep 28 14:15 Table1.MYI 11K Sep 20 08:45 Table2.frm 424K Sep 28 14:15 Table2.MYD 110K Sep 28 14:15 Table2.MYI The system is only used as a database server, it's a dual processor system with 2gig of ram. As you can see, Table1's MyISAM data file is quite large at 2.1 gig. Taking this into account what size InnoDB data files should I configure in my my.cnf file? I was thinking of this: My.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin server-id=70 port = 3306 skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 set-variable= max_connections=500 ### InnoDB setup ### # use default data directory for database innodb_data_home_dir = /DATA/dbdata/ innodb_data_file_path = /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 3 innodb_log_file_size = 500M innodb_log_buffer_size = 8M innodb_buffer_pool_size = 1.5G innodb_additional_mem_pool_size = 2M innodb_file_io_threads = 4 But what happens if the ibdata2 fills up to the max of 2G? I've got 50 gig available on the partition where the db data is stored. Is there anything else here that looks incorrect? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
I see that the thread is getting too long, so I'm cutting out parts that I'm not responding to. Hope no one minds. As far as I'm concerned, simplicity and ease would be to leave it as is but we're looking to eek out as much speed in our transactions >>> >>>as possible >>> so simplicity, ease and performance don't always go together. MySQL allocates memory to MyISAM and InnoDB separately, so if you want to 'eek out' as much performance as possible from the InnoDB side of things, you will probably want to reduce the amount of memory allocated to MyISAM (on that server). For this reason, I would recommend changing all tables (except those in the 'mysql' database, as Shawn explained) to InnoDB, and essentially dedicating that server to InnoDB. You do have to leave some memory for MyISAM (8M is enough if your only tables in MyISAM are the ones in the 'mysql' database). The configuration settings which determine memory allocation are still somewhat confusing to me (I'm sure others on the list have a clearer understanding than I), but the main ones are key_buffer_size for MyISAM and innodb_buffer_pool_size for InnoDB. links to the docs: http://dev.mysql.com/doc/mysql/en/server-system-variables.html http://dev.mysql.com/doc/mysql/en/innodb-start.html Is that the only diff (other than the "select count(*)" thing) between InnoDB and MyISAM? Aren't select statements faster from MyISAM tables than from InnoDB's? There are cases when each one performs better than the other. I'll go over a couple examples from my own experiences... If your table is being written to very frequently, then InnoDB will yield faster reads because of table locking restrictions on MyISAM tables. And, if you have commonly repeated queries, turning on (or turning up) the query cache will have a more noticeable difference on speed than anything else. However, for very large tables that are primarily read from (without repeating the same questions such that they could be cached), I do believe MyISAM will give better performance. There's also been a statement from our lead developer that having a db with mixed tables (some InnoDB and some MyISAM) will make life harder on them because it makes development of application more difficult. I do quite a bit of php and some perl programming that interacts with MySQL and I can't think of any major problems created by a mixed engine type environment. Sure "select count(*) from table" won't be as fast in an InnoDB table and obviously full text indexes won't be there but other than that, I don't see this as causing any "difficulties" for a programmer. I'm also under the belief that it's not the world's job to make the programmer's life easier, it's the programmers job to make the world's life easier. Does anyone have any input on that? If your developers need to use transactions to modify multiple tables at once, then all those tables must be in InnoDB for the transaction to work. However, besides the few points already mentioned, it really is transparent to the programmers what storage engine you use. I'd ask them how they think it will make life "harder on them"; chances are they are basing this on misconceptions. While I was writing this, I see that Shawn has responded to this particular question with a lot more clarity than I would have, so I'll just agree with what he wrote and stop here :) Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
"Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 11:36:01 AM: <> > > Is that the only diff (other than the "select count(*)" thing) between > InnoDB and MyISAM? Aren't select statements faster from MyISAM tables > than from InnoDB's? > > There's also been a statement from our lead developer that having a db > with mixed tables (some InnoDB and some MyISAM) will make life harder on > them because it makes development of application more difficult. I do > quite a bit of php and some perl programming that interacts with MySQL > and I can't think of any major problems created by a mixed engine type > environment. Sure "select count(*) from table" won't be as fast in an > InnoDB table and obviously full text indexes won't be there but other > than that, I don't see this as causing any "difficulties" for a > programmer. I'm also under the belief that it's not the world's job to > make the programmer's life easier, it's the programmers job to make the > world's life easier. > > Does anyone have any input on that? > > > > > I would think that life would become easier on the developers because now, in order to provide transactional security, they will no longer need to issue LOCK TABLE and UNLOCK TABLE statements but rather START TRANSACTION and either COMMIT or ROLLBACK statements. Much friendlier from the developer's standpoint. I guess if you were using a mixed-mode table (part of one record is held in an InnoDB table while the fields that needed FT indexes were in a MyISAM table) that would be a bit harder to work with but the overall performance gains and the stability provided by the transactional structure should outweigh any developmental overhead. The data structure should be relatively independent from the application design. Your data needs to be stored in a manner that is both logically correct and efficient to access and maintain. The application needs to work with that design, not the other way around. It's only when the database design is so complex that practical factors (like memory size or a really large or complex join) begin to limit the speed of certain SQL statements that you need to consider compromising between a theoretically correct design and performance. For instance, it is possible to normalize a data structure to the point that it becomes slower to manage than one that is slightly denormalized. However, start from theory and work backwards. Break an optimal design only if it creates a significant or required performance increase. Most of the times, you can gain performance by small shifts in the application layer (use two smaller queries instead of one larger, more complex one, use equality matching rather than LIKE,...) or by tuning your index structures (watch the slow query log and look for patterns of unindexed column usage; create indexes to fit, consider building covering indexes for some of your most frequently executed query patterns,...). IMHO, a good logical data design will promote better code design and will enhance overall performance by improving the performance of your data persistence layer. I think your developers need to have their code reviewed if switching to InnoDB is going to be a big hassle for them. A complaint like that sends up a red flag for me. I have to wonder what are they currently doing that isn't going to be supported by the new format Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: MyISAM to InnoDB
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, September 23, 2005 10:25 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: RE: MyISAM to InnoDB > > > "Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 09:57:06 AM: > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > Sent: Friday, September 23, 2005 09:40 > > > To: Jeff > > > Cc: mysql@lists.mysql.com > > > Subject: RE: MyISAM to InnoDB > > > > > > > > > Sorry to butt in but I wanted to make sure you didn't do > > > actually do what > > > you proposed to do, yet. More responses interspersed... > > > > > > > Nope nothing yet, I don't rush things when I'm unsure... ;o) > > > > > "Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 08:32:57 AM: > > > > > > > > -Original Message- > > > > > From: Devananda [mailto:[EMAIL PROTECTED] > > > > > Sent: Thursday, September 22, 2005 19:03 > > > > > To: Jeff > > > > > Cc: mysql@lists.mysql.com > > > > > Subject: Re: MyISAM to InnoDB > > > > > > > > > > > > > > > Jeff wrote: > > > > > >>-Original Message- > > > > > >>From: Devananda [mailto:[EMAIL PROTECTED] > > > > > >>Sent: Thursday, September 22, 2005 16:14 > > > > > >>To: Jeff > > > > > >>Cc: mysql@lists.mysql.com > > > > > >>Subject: Re: MyISAM to InnoDB > > > > > >> > > > > > >> > > > > > >>Jeff wrote: > > > > > >> > > > > > >>>True, is there a way to tell a slave to not replicate > > > > > >> > > > > > >>certain queries > > > > > >> > > > > > >>>like alter table or would I need to get creative and stop > > > > > >> > > > > > >>replication > > > > > >> > > > > > >>>and all writes to the main database, then issue the > > > alter table > > > > > >>>statement, then restart replication with a set global > > > > > >>>slave_sql_skip_counter=1 so that it skips the > alter statemtent? > > > > > >> > > > > > >>There's a much easier way - issue the statement "SET > > > SQL_LOG_BIN = > > > > > >>0;" before issuing any ALTER TABLE statements. This > > > will cause all > > > > > >>statements for the duration of that session to not be > > > > > written to the > > > > > >>binlog. See > http://dev.mysql.com/doc/mysql/en/set-option.html > > > > > >>for more > > > > > >>information. > > > > > >> > > > > > > > > > > > > > > > > > > First off, thanks for the help to you and Bruce both! > > > > > > > > > > > You're quite welcome, Jeff :) > > > > > > > > > > > When you say here, "for the duration of that session" does > > > > > that mean > > > > > > that only queries I issue with my connection skip the > > > > > binlog? Or do > > > > > > all queries during that time skip the binlog. In other > > > > > words, when I > > > > > > SET SQL_LOG_BIN = 0; should I first stop all applications > > > > > writing to > > > > > > the database to prevent missing data in the slaves? > > > > > > > > > > > > > > > > > > > > > It only affects that connection. Bruce wrote a response > > > at about the > > > > > same time I did; his covers this topic as well. SQL_LOG_BIN > > > > > is a session > > > > > variable, meaning that it only affects the current session > > > > > (connection). > > > > > So, any applications running at the same time will not be > > > > > affected by a > > > > > change to this variable, and if you close your client and > > > > > reconnect, you > > > > > will have to set the variable again. As Bruce suggested, > > > it's best to > > > &g
RE: MyISAM to InnoDB
"Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 09:57:06 AM: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > Sent: Friday, September 23, 2005 09:40 > > To: Jeff > > Cc: mysql@lists.mysql.com > > Subject: RE: MyISAM to InnoDB > > > > > > Sorry to butt in but I wanted to make sure you didn't do > > actually do what > > you proposed to do, yet. More responses interspersed... > > > > Nope nothing yet, I don't rush things when I'm unsure... ;o) > > > "Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 08:32:57 AM: > > > > > > -Original Message- > > > > From: Devananda [mailto:[EMAIL PROTECTED] > > > > Sent: Thursday, September 22, 2005 19:03 > > > > To: Jeff > > > > Cc: mysql@lists.mysql.com > > > > Subject: Re: MyISAM to InnoDB > > > > > > > > > > > > Jeff wrote: > > > > >>-Original Message- > > > > >>From: Devananda [mailto:[EMAIL PROTECTED] > > > > >>Sent: Thursday, September 22, 2005 16:14 > > > > >>To: Jeff > > > > >>Cc: mysql@lists.mysql.com > > > > >>Subject: Re: MyISAM to InnoDB > > > > >> > > > > >> > > > > >>Jeff wrote: > > > > >> > > > > >>>True, is there a way to tell a slave to not replicate > > > > >> > > > > >>certain queries > > > > >> > > > > >>>like alter table or would I need to get creative and stop > > > > >> > > > > >>replication > > > > >> > > > > >>>and all writes to the main database, then issue the > > alter table > > > > >>>statement, then restart replication with a set global > > > > >>>slave_sql_skip_counter=1 so that it skips the alter statemtent? > > > > >> > > > > >>There's a much easier way - issue the statement "SET > > SQL_LOG_BIN = > > > > >>0;" before issuing any ALTER TABLE statements. This > > will cause all > > > > >>statements for the duration of that session to not be > > > > written to the > > > > >>binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html > > > > >>for more > > > > >>information. > > > > >> > > > > > > > > > > > > > > > First off, thanks for the help to you and Bruce both! > > > > > > > > > You're quite welcome, Jeff :) > > > > > > > > > When you say here, "for the duration of that session" does > > > > that mean > > > > > that only queries I issue with my connection skip the > > > > binlog? Or do > > > > > all queries during that time skip the binlog. In other > > > > words, when I > > > > > SET SQL_LOG_BIN = 0; should I first stop all applications > > > > writing to > > > > > the database to prevent missing data in the slaves? > > > > > > > > > > > > > > > > > It only affects that connection. Bruce wrote a response > > at about the > > > > same time I did; his covers this topic as well. SQL_LOG_BIN > > > > is a session > > > > variable, meaning that it only affects the current session > > > > (connection). > > > > So, any applications running at the same time will not be > > > > affected by a > > > > change to this variable, and if you close your client and > > > > reconnect, you > > > > will have to set the variable again. As Bruce suggested, > > it's best to > > > > set it only when you need it and unset it immediately > > > > afterwards (as a > > > > precaution against operator error, not because it affects > > the server). > > > > > > > > I do want to point out that while the commands you issue > > > > (after setting > > > > SQL_LOG_BIN to 0) will not be written to the binlog (thus > > > > will not run > > > > on any slave reading from this server), they may affect > > other running > > > > processes on the server. If, for example, you run an > > ALTER TABLE on a > > > > table currently
RE: MyISAM to InnoDB
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, September 23, 2005 09:40 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: RE: MyISAM to InnoDB > > > Sorry to butt in but I wanted to make sure you didn't do > actually do what > you proposed to do, yet. More responses interspersed... > Nope nothing yet, I don't rush things when I'm unsure... ;o) > "Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 08:32:57 AM: > > > > -Original Message- > > > From: Devananda [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, September 22, 2005 19:03 > > > To: Jeff > > > Cc: mysql@lists.mysql.com > > > Subject: Re: MyISAM to InnoDB > > > > > > > > > Jeff wrote: > > > >>-Original Message- > > > >>From: Devananda [mailto:[EMAIL PROTECTED] > > > >>Sent: Thursday, September 22, 2005 16:14 > > > >>To: Jeff > > > >>Cc: mysql@lists.mysql.com > > > >>Subject: Re: MyISAM to InnoDB > > > >> > > > >> > > > >>Jeff wrote: > > > >> > > > >>>True, is there a way to tell a slave to not replicate > > > >> > > > >>certain queries > > > >> > > > >>>like alter table or would I need to get creative and stop > > > >> > > > >>replication > > > >> > > > >>>and all writes to the main database, then issue the > alter table > > > >>>statement, then restart replication with a set global > > > >>>slave_sql_skip_counter=1 so that it skips the alter statemtent? > > > >> > > > >>There's a much easier way - issue the statement "SET > SQL_LOG_BIN = > > > >>0;" before issuing any ALTER TABLE statements. This > will cause all > > > >>statements for the duration of that session to not be > > > written to the > > > >>binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html > > > >>for more > > > >>information. > > > >> > > > > > > > > > > > > First off, thanks for the help to you and Bruce both! > > > > > > > You're quite welcome, Jeff :) > > > > > > > When you say here, "for the duration of that session" does > > > that mean > > > > that only queries I issue with my connection skip the > > > binlog? Or do > > > > all queries during that time skip the binlog. In other > > > words, when I > > > > SET SQL_LOG_BIN = 0; should I first stop all applications > > > writing to > > > > the database to prevent missing data in the slaves? > > > > > > > > > > > > > It only affects that connection. Bruce wrote a response > at about the > > > same time I did; his covers this topic as well. SQL_LOG_BIN > > > is a session > > > variable, meaning that it only affects the current session > > > (connection). > > > So, any applications running at the same time will not be > > > affected by a > > > change to this variable, and if you close your client and > > > reconnect, you > > > will have to set the variable again. As Bruce suggested, > it's best to > > > set it only when you need it and unset it immediately > > > afterwards (as a > > > precaution against operator error, not because it affects > the server). > > > > > > I do want to point out that while the commands you issue > > > (after setting > > > SQL_LOG_BIN to 0) will not be written to the binlog (thus > > > will not run > > > on any slave reading from this server), they may affect > other running > > > processes on the server. If, for example, you run an > ALTER TABLE on a > > > table currently in MyISAM format, the table will be > locked and all > > > processes running on that server that read from / write > to that table > > > will wait until that ALTER finishes. Setting SQL_LOG_BIN > to 0 doesn't > > > affect this in any way - it _only_ affects whether statements > > > from that > > > specific session are recorded in the binary log. > > > > > > > Thanks, that answer my question regarding SQL_LOG_BIN varialbe. > > > > > Side question - you've stated that you are pla
RE: MyISAM to InnoDB
Sorry to butt in but I wanted to make sure you didn't do actually do what you proposed to do, yet. More responses interspersed... "Jeff" <[EMAIL PROTECTED]> wrote on 09/23/2005 08:32:57 AM: > > -Original Message- > > From: Devananda [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 22, 2005 19:03 > > To: Jeff > > Cc: mysql@lists.mysql.com > > Subject: Re: MyISAM to InnoDB > > > > > > Jeff wrote: > > >>-Original Message- > > >>From: Devananda [mailto:[EMAIL PROTECTED] > > >>Sent: Thursday, September 22, 2005 16:14 > > >>To: Jeff > > >>Cc: mysql@lists.mysql.com > > >>Subject: Re: MyISAM to InnoDB > > >> > > >> > > >>Jeff wrote: > > >> > > >>>True, is there a way to tell a slave to not replicate > > >> > > >>certain queries > > >> > > >>>like alter table or would I need to get creative and stop > > >> > > >>replication > > >> > > >>>and all writes to the main database, then issue the alter table > > >>>statement, then restart replication with a set global > > >>>slave_sql_skip_counter=1 so that it skips the alter statemtent? > > >> > > >>There's a much easier way - issue the statement "SET > > >>SQL_LOG_BIN = 0;" > > >>before issuing any ALTER TABLE statements. This will cause all > > >>statements for the duration of that session to not be > > written to the > > >>binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html > > >>for more > > >>information. > > >> > > > > > > > > > First off, thanks for the help to you and Bruce both! > > > > > You're quite welcome, Jeff :) > > > > > When you say here, "for the duration of that session" does > > that mean > > > that only queries I issue with my connection skip the > > binlog? Or do > > > all queries during that time skip the binlog. In other > > words, when I > > > SET SQL_LOG_BIN = 0; should I first stop all applications > > writing to > > > the database to prevent missing data in the slaves? > > > > > > > > > It only affects that connection. Bruce wrote a response at about the > > same time I did; his covers this topic as well. SQL_LOG_BIN > > is a session > > variable, meaning that it only affects the current session > > (connection). > > So, any applications running at the same time will not be > > affected by a > > change to this variable, and if you close your client and > > reconnect, you > > will have to set the variable again. As Bruce suggested, it's best to > > set it only when you need it and unset it immediately > > afterwards (as a > > precaution against operator error, not because it affects the server). > > > > I do want to point out that while the commands you issue > > (after setting > > SQL_LOG_BIN to 0) will not be written to the binlog (thus > > will not run > > on any slave reading from this server), they may affect other running > > processes on the server. If, for example, you run an ALTER TABLE on a > > table currently in MyISAM format, the table will be locked and all > > processes running on that server that read from / write to that table > > will wait until that ALTER finishes. Setting SQL_LOG_BIN to 0 doesn't > > affect this in any way - it _only_ affects whether statements > > from that > > specific session are recorded in the binary log. > > > > Thanks, that answer my question regarding SQL_LOG_BIN varialbe. > > > Side question - you've stated that you are planning to migrate to > > InnoDB, but you haven't said anything to the list about how much data > > you have. Just be aware that it can take a lot of time and disk space > > for MySQL to transfer all your data from one format to the other (of > > course depending on how much data you have) and if anything > > goes wrong > > during that time, the results will probably not be what you > > expect, or > > want. I would advise you to at least investigate an alternate > > approach > > if you have a lot of data - take the server you are going to > > migrate out > > of the 'cluster' and make sure it is not processing any data / no > > clients are connecting to it; dump all your data to text fi
RE: MyISAM to InnoDB
> -Original Message- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 22, 2005 19:03 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > >>-Original Message- > >>From: Devananda [mailto:[EMAIL PROTECTED] > >>Sent: Thursday, September 22, 2005 16:14 > >>To: Jeff > >>Cc: mysql@lists.mysql.com > >>Subject: Re: MyISAM to InnoDB > >> > >> > >>Jeff wrote: > >> > >>>True, is there a way to tell a slave to not replicate > >> > >>certain queries > >> > >>>like alter table or would I need to get creative and stop > >> > >>replication > >> > >>>and all writes to the main database, then issue the alter table > >>>statement, then restart replication with a set global > >>>slave_sql_skip_counter=1 so that it skips the alter statemtent? > >> > >>There's a much easier way - issue the statement "SET > >>SQL_LOG_BIN = 0;" > >>before issuing any ALTER TABLE statements. This will cause all > >>statements for the duration of that session to not be > written to the > >>binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html > >>for more > >>information. > >> > > > > > > First off, thanks for the help to you and Bruce both! > > > You're quite welcome, Jeff :) > > > When you say here, "for the duration of that session" does > that mean > > that only queries I issue with my connection skip the > binlog? Or do > > all queries during that time skip the binlog. In other > words, when I > > SET SQL_LOG_BIN = 0; should I first stop all applications > writing to > > the database to prevent missing data in the slaves? > > > > > It only affects that connection. Bruce wrote a response at about the > same time I did; his covers this topic as well. SQL_LOG_BIN > is a session > variable, meaning that it only affects the current session > (connection). > So, any applications running at the same time will not be > affected by a > change to this variable, and if you close your client and > reconnect, you > will have to set the variable again. As Bruce suggested, it's best to > set it only when you need it and unset it immediately > afterwards (as a > precaution against operator error, not because it affects the server). > > I do want to point out that while the commands you issue > (after setting > SQL_LOG_BIN to 0) will not be written to the binlog (thus > will not run > on any slave reading from this server), they may affect other running > processes on the server. If, for example, you run an ALTER TABLE on a > table currently in MyISAM format, the table will be locked and all > processes running on that server that read from / write to that table > will wait until that ALTER finishes. Setting SQL_LOG_BIN to 0 doesn't > affect this in any way - it _only_ affects whether statements > from that > specific session are recorded in the binary log. > Thanks, that answer my question regarding SQL_LOG_BIN varialbe. > Side question - you've stated that you are planning to migrate to > InnoDB, but you haven't said anything to the list about how much data > you have. Just be aware that it can take a lot of time and disk space > for MySQL to transfer all your data from one format to the other (of > course depending on how much data you have) and if anything > goes wrong > during that time, the results will probably not be what you > expect, or > want. I would advise you to at least investigate an alternate > approach > if you have a lot of data - take the server you are going to > migrate out > of the 'cluster' and make sure it is not processing any data / no > clients are connecting to it; dump all your data to text files, > preferably separating your data definition statements (ie > CREATE TABLE > statements) from your actual data; modify the CREATE statements to > specify the InnoDB engine; lastly load all the data from the > text files > into MySQL, and bring this server back into the 'cluster'. > > If you don't have a _lot_ of data, then it may not be worth all that > work. Of course, "a lot" is subjective; I'd say, based purely > on my own > experiences with this, that if you are going to migrate 1G of > data, you > will probably be better off exporting / alter the text files / > importing. If you have 10's or 100's of G of data, I
Re: MyISAM to InnoDB
Jeff wrote: -Original Message- From: Devananda [mailto:[EMAIL PROTECTED] Sent: Thursday, September 22, 2005 16:14 To: Jeff Cc: mysql@lists.mysql.com Subject: Re: MyISAM to InnoDB Jeff wrote: True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? There's a much easier way - issue the statement "SET SQL_LOG_BIN = 0;" before issuing any ALTER TABLE statements. This will cause all statements for the duration of that session to not be written to the binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html for more information. First off, thanks for the help to you and Bruce both! You're quite welcome, Jeff :) When you say here, "for the duration of that session" does that mean that only queries I issue with my connection skip the binlog? Or do all queries during that time skip the binlog. In other words, when I SET SQL_LOG_BIN = 0; should I first stop all applications writing to the database to prevent missing data in the slaves? It only affects that connection. Bruce wrote a response at about the same time I did; his covers this topic as well. SQL_LOG_BIN is a session variable, meaning that it only affects the current session (connection). So, any applications running at the same time will not be affected by a change to this variable, and if you close your client and reconnect, you will have to set the variable again. As Bruce suggested, it's best to set it only when you need it and unset it immediately afterwards (as a precaution against operator error, not because it affects the server). I do want to point out that while the commands you issue (after setting SQL_LOG_BIN to 0) will not be written to the binlog (thus will not run on any slave reading from this server), they may affect other running processes on the server. If, for example, you run an ALTER TABLE on a table currently in MyISAM format, the table will be locked and all processes running on that server that read from / write to that table will wait until that ALTER finishes. Setting SQL_LOG_BIN to 0 doesn't affect this in any way - it _only_ affects whether statements from that specific session are recorded in the binary log. Side question - you've stated that you are planning to migrate to InnoDB, but you haven't said anything to the list about how much data you have. Just be aware that it can take a lot of time and disk space for MySQL to transfer all your data from one format to the other (of course depending on how much data you have) and if anything goes wrong during that time, the results will probably not be what you expect, or want. I would advise you to at least investigate an alternate approach if you have a lot of data - take the server you are going to migrate out of the 'cluster' and make sure it is not processing any data / no clients are connecting to it; dump all your data to text files, preferably separating your data definition statements (ie CREATE TABLE statements) from your actual data; modify the CREATE statements to specify the InnoDB engine; lastly load all the data from the text files into MySQL, and bring this server back into the 'cluster'. If you don't have a _lot_ of data, then it may not be worth all that work. Of course, "a lot" is subjective; I'd say, based purely on my own experiences with this, that if you are going to migrate 1G of data, you will probably be better off exporting / alter the text files / importing. If you have 10's or 100's of G of data, I would strongly recommend that you do it this way. And regardless of how much data you have, it is, IMHO, safer to export/import. If you're interested, I would be happy to talk more about a method to automate this process over many tables / lots of data. Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
> -Original Message- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 22, 2005 16:14 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > > True, is there a way to tell a slave to not replicate > certain queries > > like alter table or would I need to get creative and stop > replication > > and all writes to the main database, then issue the alter table > > statement, then restart replication with a set global > > slave_sql_skip_counter=1 so that it skips the alter statemtent? > > There's a much easier way - issue the statement "SET > SQL_LOG_BIN = 0;" > before issuing any ALTER TABLE statements. This will cause all > statements for the duration of that session to not be written to the > binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html > for more > information. > First off, thanks for the help to you and Bruce both! When you say here, "for the duration of that session" does that mean that only queries I issue with my connection skip the binlog? Or do all queries during that time skip the binlog. In other words, when I SET SQL_LOG_BIN = 0; should I first stop all applications writing to the database to prevent missing data in the slaves? > > You may want to look at a few pages in the docs, for > information about > InnoDB / MyISAM differences. If your code relies on one table > type (or > features only available with that table type, like transactions for > InnoDB or "SELECT COUNT(*)" for MyISAM), you may run into > some problems. > Here are a couple links to try to help. > http://dev.mysql.com/doc/mysql/en/innodb-and-mysql-replication .html http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html http://dev.mysql.com/doc/mysql/en/replication-features.html (towards the bottom it talks about replication of transactions and MyISAM engine) Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
Jeff wrote: True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? There's a much easier way - issue the statement "SET SQL_LOG_BIN = 0;" before issuing any ALTER TABLE statements. This will cause all statements for the duration of that session to not be written to the binlog. See http://dev.mysql.com/doc/mysql/en/set-option.html for more information. You may want to look at a few pages in the docs, for information about InnoDB / MyISAM differences. If your code relies on one table type (or features only available with that table type, like transactions for InnoDB or "SELECT COUNT(*)" for MyISAM), you may run into some problems. Here are a couple links to try to help. http://dev.mysql.com/doc/mysql/en/innodb-and-mysql-replication.html http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html http://dev.mysql.com/doc/mysql/en/replication-features.html (towards the bottom it talks about replication of transactions and MyISAM engine) Best Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
On Sep 22, 2005, at 11:46 AM, Jeff wrote: True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? On a single connection use: SET SQL_LOG_BIN = 0 It's a connection variable, the default is 1, which means queries altering the data get written to the bin log... changing this to 0 means data altering commands from this specific connection do not get written to the binary log... It's best not to leave a connection lying around with this setting because it's the sort of thing you forget about and later end up with data inconsistencies. However short term use by turning it off, doing your thing, and turning it on again usually works without trouble... eg: SET SQL_LOG_BIN = 0; ALTER TABLE some stuff here; SET SQL_LOG_BIN = 1; Not all users have permission to issue such a command. If I understand what you're saying here, some MySQL front end gui software will add onto any "Alter table" statement you submit a statement specifying the type of table like myisam automatically. So if you used that gui and tried to issue an alter statement to say add an index to a InnoDB table it would add on a table type = MyISAM and cause havoc? Normally I don't rely on gui tools to do my serious quiries like altering tables or adding indexes etc. I'll do them logging directly into mysql server on the linux box itself. In this case there shouldn't be a problem correct? Some GUI's take simple steps and write them out into their full long SQL format... whereas adding a table's engine or type to an alter table is optional in MySQL, officially it is suppose to be there... so some GUI's put it there... typically if you haven't told it to change the table type it will just use whatever table type it is now... but the end result in the binary log will still go to the other server and potentially change something there. There shouldn't be a problem using the mysql command line client... but I'm going to emphasize "shouldn't" here... when you have two different table types on master and slave you need to be **really** sure you don't mess that up. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM to InnoDB
> -Original Message- > From: Bruce Dembecki [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 22, 2005 11:41 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > You will need to make sure you have innodb configured in the my.cnf > file and you have enough space built for it in the shared table > space. InnoDB also needs it's own memory pool, so make sure you give > it enough memory. For day to day issues there is no problem doing > innodb/myisam replication, with a couple of small caveats... an > "ALTER TABLE" would replicate and thus... may change the table type > from myisam to innodb or vice versa depending on which server the > ALTER TABLE came from. To go with that the original conversion from > myisam to InnoDB would also need to be done in such a way as to not > be replicated. > True, is there a way to tell a slave to not replicate certain queries like alter table or would I need to get creative and stop replication and all writes to the main database, then issue the alter table statement, then restart replication with a set global slave_sql_skip_counter=1 so that it skips the alter statemtent? > > Remember that an ALTER TABLE that could have an impact could be as > simple as adding or dropping an index... although usually > very simple > alter table statements like that can be done without defining the > table engine, some GUIs may however insert that for you on even the > simplest ALTER TABLE commands. > If I understand what you're saying here, some MySQL front end gui software will add onto any "Alter table" statement you submit a statement specifying the type of table like myisam automatically. So if you used that gui and tried to issue an alter statement to say add an index to a InnoDB table it would add on a table type = MyISAM and cause havoc? Normally I don't rely on gui tools to do my serious quiries like altering tables or adding indexes etc. I'll do them logging directly into mysql server on the linux box itself. In this case there shouldn't be a problem correct? > Best Regards, Bruce > > On Sep 22, 2005, at 7:59 AM, Jeff wrote: > > > Hey all, > > > > I've got a production database that made up of all MyISAM > tables. I'd > > like to change some of the more heavily written to tables > to InnoDB to > > take advantage of the record level locking and thus improve write > > performance of our applications. > > > > I currently have a second db server that is replicating from the > > current production system but not in production yet. I'd like to try > > to > > convert > > it to InnoDB. MySQL version is 4.0.16. It it as symple as just > > issuing > > the modify table query or are there problems I should be > aware of when > > doing this? > > > > Also are there known problems replicating from A -> B -> A (circular > > replication) when A had Table1= InnoDB and B has Table1=MyISAM? > > > > Thanks, > > > > Jeff > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql? > > [EMAIL PROTECTED] > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
You will need to make sure you have innodb configured in the my.cnf file and you have enough space built for it in the shared table space. InnoDB also needs it's own memory pool, so make sure you give it enough memory. For day to day issues there is no problem doing innodb/myisam replication, with a couple of small caveats... an "ALTER TABLE" would replicate and thus... may change the table type from myisam to innodb or vice versa depending on which server the ALTER TABLE came from. To go with that the original conversion from myisam to InnoDB would also need to be done in such a way as to not be replicated. Remember that an ALTER TABLE that could have an impact could be as simple as adding or dropping an index... although usually very simple alter table statements like that can be done without defining the table engine, some GUIs may however insert that for you on even the simplest ALTER TABLE commands. Best Regards, Bruce On Sep 22, 2005, at 7:59 AM, Jeff wrote: Hey all, I've got a production database that made up of all MyISAM tables. I'd like to change some of the more heavily written to tables to InnoDB to take advantage of the record level locking and thus improve write performance of our applications. I currently have a second db server that is replicating from the current production system but not in production yet. I'd like to try to convert it to InnoDB. MySQL version is 4.0.16. It it as symple as just issuing the modify table query or are there problems I should be aware of when doing this? Also are there known problems replicating from A -> B -> A (circular replication) when A had Table1= InnoDB and B has Table1=MyISAM? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myISAM to innodb
vinay <[EMAIL PROTECTED]> wrote: > hi, > i have been using mysql-3.23.49a and now i want innodb support for the same > version of the database, can any one tell how should i proceed. > regards If you want to use InnoDB, you should install MySQL-Max binary: http://www.mysql.com/doc/en/mysqld-max.html You must also specify path to the InnoDB datafiles with innodb_data_file_path option: http://www.mysql.com/doc/en/InnoDB_in_MySQL_3.23.html Description of the other startup options you can find at: http://www.mysql.com/doc/en/InnoDB_start.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM to InnoDB
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) Cc: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Date: Tue, 24 Sep 2002 11:04:35 +0100 I thought the InnoDB doesn't support a few MyISAM features. FULLTEXT for one.Those features it does support will not need SQL changes. On the other hand, if you don't need MyISAM freatures, can't you just do "ALTER TABLE mytable TYPE=INNODB". If you are brave, and after taking a backup, of course. That didn't seem to work for me, I digging out the backup and going back to version 3 mysqld, version 4 tested slower and innoDB timing was 2x. - 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: MyISAM to InnoDB
At 12:23 +0200 9/24/02, Harald Fuchs wrote:
>In article <[EMAIL PROTECTED]>,
>Daniel Kiss <[EMAIL PROTECTED]> writes:
>
>>> Question is, will this
>>> require any SQL code changes to our application? We make heavy use of
>>> features like temporary tables, autoincrement columns etc. Would if be
>>> as simple as dumping the database and restoring it to InnoDB tables?
>
>Not necessary - "ALTER TABLE tbl TYPE=InnoDB" is enough.
>
>> The simple answer is: No. You don't have to change any code in your
>> application, InnoDB will works with the actual code, because it is
>> 100% compatible with MyISAM.
>
>That's not quite right. Things that InnoDB doesn't support in
>comparison to MyISAM:
>* Fulltext indices
>* Indices on (parts of) TEXT columns
>* AUTO_INCREMENT on the second part of a combined PRIMARY KEY
>
>Any others?
OPTIMIZE TABLE
REPAIR TABLE
CREATE TABLE {DATA | INDEX} DIRECTORY = 'pathname'
(and several other table creation options)
symlinking
BACKUP TABLE
RESTORE TABLE
MERGE tables
Can specify initial value of AUTO_INCREMENT sequence
-
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
[Fwd: Re: MyISAM to InnoDB]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 grr: sql,query,mysql ;-) Hi Guys, Thanks to everyone that gave a response there, i feel much better now ;-) I'm not using any of the features listed in the recent posts just pain simple boring INSERTS, UPDATES, SELECTS and temporary tables. (keep it simple eh?!?) so i think the shift will be quick and easy. I have a windows of about 3 hrs to down the server make the changes and get it operational again so I though it wise to check first! Thanks again all, Danny - -- Danny Haworth =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Ge2 System Administrator Gas~Elec Safety Systems Tel : 01895 422 997 Email : [EMAIL PROTECTED] (mailto:[EMAIL PROTECTED]) -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9kESMUKf+497XC3QRAp2RAKDDeRmvHB2Eayr74X1Ku+d2cqdd6QCeOHN7 Olm4Bg6VxheM0elhN/xf+wY= =+KyX -END PGP SIGNATURE- - 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: MyISAM to InnoDB
> At 10:12 2002.09.24._ +0100, you wrote: > >Question is, will this > >require any SQL code changes to our application? We make heavy use of > >features like temporary tables, autoincrement columns etc. Would if be > >as simple as dumping the database and restoring it to InnoDB tables? > > The simple answer is: No. You don't have to change any code in your > application, InnoDB will works with the actual code, because it is 100% > compatible with MyISAM. > But!!! If you want to use special features of InnoDB table types (which are > not in MyISAM), you might need changing your code. For example when you > decide to use transactions (non-autocommit mode) or foreign key constraints. I thought the InnoDB doesn't support a few MyISAM features. FULLTEXT for one.Those features it does support will not need SQL changes. On the other hand, if you don't need MyISAM freatures, can't you just do "ALTER TABLE mytable TYPE=INNODB". If you are brave, and after taking a backup, of course. Alec - 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: MyISAM to InnoDB
Hi! At 10:12 2002.09.24._ +0100, you wrote: >Question is, will this >require any SQL code changes to our application? We make heavy use of >features like temporary tables, autoincrement columns etc. Would if be >as simple as dumping the database and restoring it to InnoDB tables? The simple answer is: No. You don't have to change any code in your application, InnoDB will works with the actual code, because it is 100% compatible with MyISAM. But!!! If you want to use special features of InnoDB table types (which are not in MyISAM), you might need changing your code. For example when you decide to use transactions (non-autocommit mode) or foreign key constraints. Bye, Daniel - 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
