Re: Switching to InnoDB turns out dissapointing
Brent Baisley wrote: calculations from your query, like replace datesub(now(), interval 12 month) with a constant. Which means figuring out the right date before hand. AFAIK, no column is involved in this particular function, so there's no point doing the computation beforehand, it's done by mysql. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
El Mié 02 Mar 2005 16:04, escribió: > H, sounds like you are trying to mix OLTP and OLAP in one database > structure. That's a tough one. You want your tables designed to always > accept data in real time, but once the data is in, it doesn't change > and you want to query it. Relational vs. Dimensional data models. > > Your hardware is pretty good. Sorry, I missed the early thread > responses, did you figure out where things are bottlenecking (CPU, Disk > I/O, RAM, Network)? That will help you focus on what you can change in > your software if you can't upgrade your hardware. > > The first place to always look is your queries. Optimizing your queries > always gives you the best bang for you buck. Use explain to make sure > MySQL is using the right indexes, especially since you are using date > ranges. Sometimes MySQL may use the best index, sometimes it won't, > simply by changing the date range. It won't hurt to use hints (USE, > FORCE, IGNORE) in your query if you know you want MySQL to use a > certain index. > You could possible also change you structure slightly, like add a > WeekNumber column. It could just be an incrementing week number with > 1/1/2004 being week 1, 1/1/2005 being week 53, etc. So it would be > weeks since 1/1/2004. It could be just a regular int type, which should > be quicker than searching on a date field. The idea is to add constants > on entry to speed up the summaries. Also, try to eliminate any and all > calculations from your query, like replace datesub(now(), interval 12 > month) with a constant. Which means figuring out the right date before > hand. > > Would you be able to run daily summaries? Then your weekly summaries > are just running against 7 records. > > 1 million rows is not that big, so you should be able to get good > performance, it's just a matter of structuring things correctly. > > Heck, it may end up that the best thing to do is an insert select into > another table (maybe even a temp table), which you then run your > summaries against. Since your "dump" is sequential access to disk (the > same order the data was entered), it may be very quick. After some thought, and seeded by the many fine suggestions from the list, I decided to restructure completely the approach to the problem. The result is that the query to calculate one variable now takes only 7 minutes! We will go on and restructure the remaining 10 variables and see how it goes. Sometimes, not working makes you more productive ;-) Thank you all and best regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
H, sounds like you are trying to mix OLTP and OLAP in one database structure. That's a tough one. You want your tables designed to always accept data in real time, but once the data is in, it doesn't change and you want to query it. Relational vs. Dimensional data models. Your hardware is pretty good. Sorry, I missed the early thread responses, did you figure out where things are bottlenecking (CPU, Disk I/O, RAM, Network)? That will help you focus on what you can change in your software if you can't upgrade your hardware. The first place to always look is your queries. Optimizing your queries always gives you the best bang for you buck. Use explain to make sure MySQL is using the right indexes, especially since you are using date ranges. Sometimes MySQL may use the best index, sometimes it won't, simply by changing the date range. It won't hurt to use hints (USE, FORCE, IGNORE) in your query if you know you want MySQL to use a certain index. You could possible also change you structure slightly, like add a WeekNumber column. It could just be an incrementing week number with 1/1/2004 being week 1, 1/1/2005 being week 53, etc. So it would be weeks since 1/1/2004. It could be just a regular int type, which should be quicker than searching on a date field. The idea is to add constants on entry to speed up the summaries. Also, try to eliminate any and all calculations from your query, like replace datesub(now(), interval 12 month) with a constant. Which means figuring out the right date before hand. Would you be able to run daily summaries? Then your weekly summaries are just running against 7 records. 1 million rows is not that big, so you should be able to get good performance, it's just a matter of structuring things correctly. Heck, it may end up that the best thing to do is an insert select into another table (maybe even a temp table), which you then run your summaries against. Since your "dump" is sequential access to disk (the same order the data was entered), it may be very quick. On Mar 2, 2005, at 2:13 PM, Alfredo Cole wrote: El Mié 02 Mar 2005 11:41, Brent Baisley escribió: Coming in late on this thread. The testing on your laptop, are you just running the one query or are you somehow emulating the typical load you are trying to design for? As you said, you are trying to improve concurrency, so you'll need to compare MyISAM and InnoDB setups under load (i.e. the weekly run+typical activity). If the concurrency you are trying to improve is caused by your weekly runs, I would try doing replication. Your summarization queries would run against the replicated machine and create a text file for batch import/update into the table you need to update. That way you remove the load from the main machine caused by the summary queries. Thank you, Brent. Actually, there are three servers involved, all with a similar configuration: Server 1 - 2 Xeon 2.4 Ghz HT with 4 Gb RAM and three 36 GB SCSI HD's 10K in RAID 5 holding the main database. Uses a openMosix kernel. Server 2 - Identical config used for replication. All selects are run against this server. Server 3 - Same config except for 8 Gb RAM. Acts as an application server running the ERP software and acting as connection via a NX server for 200+ users. The servers must be available on a 24/7 basis, and are never brought down except for routine maintenance, at which time their roles are switched temporarily. Running the application that updates database structures, when needed, must be done before 7:00 am because customers begin to come in at 8:00 am, and should not last for more than 30 minutes. We are using MyISAM tables and they have to be locked when beeing updated by concurrent users, like salesmen invoicing customers in real time (it's a hardware store/True Value convenience chain of 9 stores all running our server-based ERP). We have setup another database and parallel version of our ERP software with InnoDB tables for testing, and we are encountering this problem. With MyISAM tables, locking them causes some terminals to wait for up to one minute at peak hours, which seems like an eternity when a customer is waiting for his invoice to go and pay, get his merchandise and leave. This we are hoping to improve with row level locking. Inventory, AR, GL, etc. are updated in real time. However, statistics such as history sales, sales forecasting, average discounts, profit margins, EOQ, DRP, etc. are calculated on a weekly basis moving the period to always hold a year's worth of data, using something like: select sum(sales_value) from invoices where invoice_date>=datesub(now(), interval 12 month) The total items in inventory is 45,000 and the invoices table has about 1 million rows. The system was started January 2004. In my laptop I can only run single processes, but that's where I test before making software, data, and configuration changes in the servers. Compiling changes to the software
Re: Switching to InnoDB turns out dissapointing
El Mié 02 Mar 2005 11:41, Brent Baisley escribió: > Coming in late on this thread. The testing on your laptop, are you just > running the one query or are you somehow emulating the typical load you > are trying to design for? As you said, you are trying to improve > concurrency, so you'll need to compare MyISAM and InnoDB setups under > load (i.e. the weekly run+typical activity). > > If the concurrency you are trying to improve is caused by your weekly > runs, I would try doing replication. Your summarization queries would > run against the replicated machine and create a text file for batch > import/update into the table you need to update. That way you remove > the load from the main machine caused by the summary queries. > Thank you, Brent. Actually, there are three servers involved, all with a similar configuration: Server 1 - 2 Xeon 2.4 Ghz HT with 4 Gb RAM and three 36 GB SCSI HD's 10K in RAID 5 holding the main database. Uses a openMosix kernel. Server 2 - Identical config used for replication. All selects are run against this server. Server 3 - Same config except for 8 Gb RAM. Acts as an application server running the ERP software and acting as connection via a NX server for 200+ users. The servers must be available on a 24/7 basis, and are never brought down except for routine maintenance, at which time their roles are switched temporarily. Running the application that updates database structures, when needed, must be done before 7:00 am because customers begin to come in at 8:00 am, and should not last for more than 30 minutes. We are using MyISAM tables and they have to be locked when beeing updated by concurrent users, like salesmen invoicing customers in real time (it's a hardware store/True Value convenience chain of 9 stores all running our server-based ERP). We have setup another database and parallel version of our ERP software with InnoDB tables for testing, and we are encountering this problem. With MyISAM tables, locking them causes some terminals to wait for up to one minute at peak hours, which seems like an eternity when a customer is waiting for his invoice to go and pay, get his merchandise and leave. This we are hoping to improve with row level locking. Inventory, AR, GL, etc. are updated in real time. However, statistics such as history sales, sales forecasting, average discounts, profit margins, EOQ, DRP, etc. are calculated on a weekly basis moving the period to always hold a year's worth of data, using something like: select sum(sales_value) from invoices where invoice_date>=datesub(now(), interval 12 month) The total items in inventory is 45,000 and the invoices table has about 1 million rows. The system was started January 2004. In my laptop I can only run single processes, but that's where I test before making software, data, and configuration changes in the servers. Compiling changes to the software (our own ERP) must also be made in my laptop, turned into an rpm file and then installed in the application server (Server 3). Best regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
Coming in late on this thread. The testing on your laptop, are you just running the one query or are you somehow emulating the typical load you are trying to design for? As you said, you are trying to improve concurrency, so you'll need to compare MyISAM and InnoDB setups under load (i.e. the weekly run+typical activity). If the concurrency you are trying to improve is caused by your weekly runs, I would try doing replication. Your summarization queries would run against the replicated machine and create a text file for batch import/update into the table you need to update. That way you remove the load from the main machine caused by the summary queries. On Mar 2, 2005, at 11:23 AM, Alfredo Cole wrote: El Mar 01 Mar 2005 18:29, Heikki Tuuri escribió: Alfredo, I have changed my my.cnf to try and include the suggestions from the list, as much as possible and try to run my program again. It now reads like this: innodb_data_file_path = ibdata1:2G;ibdata2:2G:autoextend set-variable = innodb_buffer_pool_size=256M set-variable = innodb_additional_mem_pool_size=32M set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 The MyISAM database is 1Gb in size, and the resulting InnoDB table space is 4 Gb. In my laptop (I have to test here before I decide to implement in the server), the time has dropped from 4 hours to 70 minutes. With MyISAM tables, it takes 12 minutes. Although the reduction in time is substantial, I still think it should be better. InnoDB is supposed to provide transactions and row level locking, which I need to improve concurrency, "without compromising the speed of MySQL". We have other processes that need to be run on a weekly basis that do a lot (45,000 records need to be examined) of select sum() for a one year period, from a table with 1 million+ rows and then updates a table with 800,000 rows, and so far it takes around 12 hours, even after enclosing between BEGIN and COMMIT statements. Could be entirely our fault. Have to check that too. Will try with this new setup and see how it goes. Thank you all. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
El Mar 01 Mar 2005 18:29, Heikki Tuuri escribió: > Alfredo, > I have changed my my.cnf to try and include the suggestions from the list, as much as possible and try to run my program again. It now reads like this: innodb_data_file_path = ibdata1:2G;ibdata2:2G:autoextend set-variable = innodb_buffer_pool_size=256M set-variable = innodb_additional_mem_pool_size=32M set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 The MyISAM database is 1Gb in size, and the resulting InnoDB table space is 4 Gb. In my laptop (I have to test here before I decide to implement in the server), the time has dropped from 4 hours to 70 minutes. With MyISAM tables, it takes 12 minutes. Although the reduction in time is substantial, I still think it should be better. InnoDB is supposed to provide transactions and row level locking, which I need to improve concurrency, "without compromising the speed of MySQL". We have other processes that need to be run on a weekly basis that do a lot (45,000 records need to be examined) of select sum() for a one year period, from a table with 1 million+ rows and then updates a table with 800,000 rows, and so far it takes around 12 hours, even after enclosing between BEGIN and COMMIT statements. Could be entirely our fault. Have to check that too. Will try with this new setup and see how it goes. Thank you all. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
Jon, - Original Message - From: ""Jon Frisby"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Wednesday, March 02, 2005 3:32 AM Subject: RE: Switching to InnoDB turns out dissapointing > set-variable =3Dinnodb_log_buffer_size=3D32M =20 The log buffer is too big. Is there a performance penalty associated with making the log buffer size too large, or is just not beneficial? it is not beneficial beyond 8 MB. In this case, the big log buffer is eating valuable RAM from the buffer pool where the memory would be more useful. -JF Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Switching to InnoDB turns out dissapointing
> > set-variable =innodb_log_buffer_size=32M > > The log buffer is too big. Is there a performance penalty associated with making the log buffer size too large, or is just not beneficial? -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
On Tue, 1 Mar 2005 18:09:37 -0600, Alfredo Cole wrote: > El Mar 01 Mar 2005 17:32, Gary Richardson escribió: >> >> InnoDB uses transactions. If you are doing each row as a single >> transaction (the default), it would probably take a lot longer. >> >> I assume you're doing your copying as a INSERT INTO $new_table SELECT >> * FROM $old_table. Try wrapping that in a >> BEGIN; >> INSERT INTO $new_table SELECT * FROM $old_table; >> COMMIT; > This is the InnoDB related stuff from my.cnf: > > innodb_data_file_path = ibdata1:10M:autoextend > set-variable = innodb_buffer_pool_size=192M > set-variable = innodb_additional_mem_pool_size=32M > set-variable = innodb_log_file_size=5M > set-variable = innodb_log_buffer_size=32M > innodb_flush_log_at_trx_commit=0 > set-variable = innodb_lock_wait_timeout=50 > > I am using the syntax as you describe it. If the transactions you are using insert thousands of records (or more) it is probably faster to leave the default value for innodb_flush_log_at_trx_commit. It is recommended anyway for data security. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
Alfredo, - Original Message - From: "Alfredo Cole" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Wednesday, March 02, 2005 2:09 AM Subject: Re: Switching to InnoDB turns out dissapointing El Mar 01 Mar 2005 17:32, Gary Richardson escribi=F3: What have you actually done to 'tune' the server? How are you doing the inserts? InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; How do you have your table space configured? Just some random thoughts.. This is the InnoDB related stuff from my.cnf: innodb_data_file_path =ibdata1:10M:autoextend set-variable =innodb_buffer_pool_size=192M the buffer pool may be a bit too small for a database of this size. Remember that tables in the InnoDB format typically take 2 to 4 x the space of a MyISAM table. The zip-compressed InnoDB table format in MySQL-5.1 will squeeze InnoDB tables to about the same size as MyISAM. set-variable =innodb_additional_mem_pool_size=32M set-variable =innodb_log_file_size=5M The log file size is too small. set-variable =innodb_log_buffer_size=32M The log buffer is too big. innodb_flush_log_at_trx_commit=0 set-variable =innodb_lock_wait_timeout=50 I am using the syntax as you describe it. In my notebook, with 512M RAM, it= takes 4 hours to complete. The top command says mysqld is using about 8% of CPU, so it must be a disk= problem. Funny thing is, it did not show when the tables were MyISAM. Thank you and regards. Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- Save the Pacific Northwest Tree Octopus! | http://zapatopi.net/treeoctopus.html | -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
InnoDB is a very different platform from MyISAM. > innodb_data_file_path = ibdata1:10M:autoextend How big is your data? You need to set your innodb_data_file_path to have enough space for this. Right now, your file is autoextending constantly. I would either turn on per table table space, or pre-create your table space. My preference is to pre-create: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend That will create 3 gigs of table space. If you need more than 3 gigs of space, it will autogrow the last file. Note that restart the server after this change will probably nuke your existing files. It'll also probably take awhile to start as it has to create those files. > set-variable = innodb_buffer_pool_size=192M If the server is only for MySQL and primarily InnoDB, you should set this way higher. On your 512MB laptop, You'd probably want to go to 384MB if it's only for Inno. Otherwise, I'd probably set 2G - 3G for your 4GB machine. Benchmarking will help determine the right number based on your number of concurrent connections. > I am using the syntax as you describe it. In my notebook, with 512M RAM, it > takes 4 hours to complete. InnoDB uses a lot more disc IO, for me anyway. Overall, I see a huge concurrency jump and the increased IO cost is well worth it. > The top command says mysqld is using about 8% of CPU, so it must be a disk > problem. Funny thing is, it did not show when the tables were MyISAM. Try the settings above -- it'll probably make a difference.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
El Mar 01 Mar 2005 17:32, Gary Richardson escribió: > What have you actually done to 'tune' the server? How are you doing > the inserts? > > InnoDB uses transactions. If you are doing each row as a single > transaction (the default), it would probably take a lot longer. > > I assume you're doing your copying as a INSERT INTO $new_table SELECT > * FROM $old_table. Try wrapping that in a > BEGIN; > INSERT INTO $new_table SELECT * FROM $old_table; > COMMIT; > > How do you have your table space configured? > > Just some random thoughts.. This is the InnoDB related stuff from my.cnf: innodb_data_file_path = ibdata1:10M:autoextend set-variable = innodb_buffer_pool_size=192M set-variable = innodb_additional_mem_pool_size=32M set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=32M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 I am using the syntax as you describe it. In my notebook, with 512M RAM, it takes 4 hours to complete. The top command says mysqld is using about 8% of CPU, so it must be a disk problem. Funny thing is, it did not show when the tables were MyISAM. Thank you and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
Gary's got another point about the transactions. I'd still look to using mysqldump first if possible, if they have the ability it will be remarkably faster. Otherwise, turning transactions off before the insert, and locking the table as well, (if you haven't already done that) could prove to save you a lot of time. Scott. On Tue, 1 Mar 2005 15:32:54 -0800, Gary Richardson <[EMAIL PROTECTED]> wrote: > What have you actually done to 'tune' the server? How are you doing > the inserts? > > InnoDB uses transactions. If you are doing each row as a single > transaction (the default), it would probably take a lot longer. > > I assume you're doing your copying as a INSERT INTO $new_table SELECT > * FROM $old_table. Try wrapping that in a > BEGIN; > INSERT INTO $new_table SELECT * FROM $old_table; > COMMIT; > > How do you have your table space configured? > > Just some random thoughts.. > > On Tue, 1 Mar 2005 17:24:32 -0600, Alfredo Cole <[EMAIL PROTECTED]> wrote: > > Hi: > > > > I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE > > 8.2. > > > > My application, an ERP system developed in-house, uses 70 tables, the > > largest > > one holding a little over one million rows. To assist when changing table > > structures, we developed a software that creates a new table for each of the > > 70 tables, one at a time, using the new structure, copies all of the records > > from the old table to the new one, drops the old one and renames the new > > one. > > > > Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz > > server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2 > > hours using InnoDB tables with the same configuration. We have followed the > > guidelines for tuning the server, and still, we find this to be excessive. > > Can somebody point to some docs, guidelines or web sites we can consult to > > improve InnoDB's performance? It seems inserting many rows decreases > > performance significantly. > > > > Thank you and regards. > > > > -- > > Alfredo J. Cole > > Grupo ACyC > > www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching to InnoDB turns out dissapointing
What have you actually done to 'tune' the server? How are you doing the inserts? InnoDB uses transactions. If you are doing each row as a single transaction (the default), it would probably take a lot longer. I assume you're doing your copying as a INSERT INTO $new_table SELECT * FROM $old_table. Try wrapping that in a BEGIN; INSERT INTO $new_table SELECT * FROM $old_table; COMMIT; How do you have your table space configured? Just some random thoughts.. On Tue, 1 Mar 2005 17:24:32 -0600, Alfredo Cole <[EMAIL PROTECTED]> wrote: > Hi: > > I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE > 8.2. > > My application, an ERP system developed in-house, uses 70 tables, the largest > one holding a little over one million rows. To assist when changing table > structures, we developed a software that creates a new table for each of the > 70 tables, one at a time, using the new structure, copies all of the records > from the old table to the new one, drops the old one and renames the new one. > > Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz > server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2 > hours using InnoDB tables with the same configuration. We have followed the > guidelines for tuning the server, and still, we find this to be excessive. > Can somebody point to some docs, guidelines or web sites we can consult to > improve InnoDB's performance? It seems inserting many rows decreases > performance significantly. > > Thank you and regards. > > -- > Alfredo J. Cole > Grupo ACyC > www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com > > -- > 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]
Switching to InnoDB turns out dissapointing
Hi: I have switched from MyISAM tables to InnoDB, using MySQL 4.1.10 under SuSE 8.2. My application, an ERP system developed in-house, uses 70 tables, the largest one holding a little over one million rows. To assist when changing table structures, we developed a software that creates a new table for each of the 70 tables, one at a time, using the new structure, copies all of the records from the old table to the new one, drops the old one and renames the new one. Using MyISAM tables, this process takes 10 minutes using a two Xeon 2.4 Ghz server, with 4 Gb RAM and SCSI RAID 5 disks. The same system takes 2 1/2 hours using InnoDB tables with the same configuration. We have followed the guidelines for tuning the server, and still, we find this to be excessive. Can somebody point to some docs, guidelines or web sites we can consult to improve InnoDB's performance? It seems inserting many rows decreases performance significantly. Thank you and regards. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom - www.acycdomains.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]