Hmmmm, 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 (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/mysql?unsub=1
>
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
