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]
