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 (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]
-- 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]
