Sounds like you have more potential problems than is typical. It also sounds like you may want to setup replication, even if the
data is replicated to an old, old computer that you were going to throw out. It's not a big deal if it falls behind in the
replication at times. And if you could get a UPS just for that one computer, than that would certainly be a decent safety net.
I'm not sure how strong any system holds up to cutting the power. How do you hold up to being hit over the head? MySQL probably
isn't as good as some other databases, especially commercial databases. At least with Innodb you can use transactions so things can
be rolled back if it crashes.
Your first step would probably not be at the database level, but at the OS level, specifically the file system. You should at least
use a file system that supports journaling. Make sure your OS will survive first, because your database probably won't if the OS
doesn't.
http://en.wikipedia.org/wiki/Journaling_file_system
http://en.wikipedia.org/wiki/Comparison_of_file_systems
http://en.wikipedia.org/wiki/Soft_updates
You may want to rethink your setup a bit too. Storing local first is a good idea. You can then have all of the local systems
replicate to a central system. The usual problem in this setup is maintaining unique id's in disparate systems. You can assign a
unique id to each server, that server then generates sub-ids unique to that system. Then you need only two columns to keep a unique
id for each record in that table.
----- Original Message -----
From: "Asif Lodhi" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, June 27, 2006 1:22 PM
Subject: multi-column indexes on InnoDB tables
Hi,
I have created 4, 5 and 8 column unique indexes ( I had to - the
application requirements dictated me to do so) on some tables in an
InnoDB only database on a 5.0.22 MySQL server running on XP-SP2
machine. The 4-col indexes are on order, invoice and receipt tables;
the 5-col indexes are on detail tables corresponding to these master
transaction tables; the 8-col indexes are on link-up tables such as
OrderMenuItems, InvoiceOrders (used to record orders stacking up
against each invoice), InvoiceReceipts (multiple receipts against one
invoice), etc.
The many-col-index requirement is due to the fact that we have several
departments and each department has several order/invoice/receipt
desks. There is a great likelihood of power failures on a daily
basis. We are not using any UPSes. We have generators but they start
after 15-20 minutes after a power failure. The departments are
scattered over 50-60 acres (perhaps more) public area and even network
failures can occur because of weather-specific corrosion,
mice-digging-into-the-ground-and-eating-cable and other such problems.
So I have to store departmetn-desk specific data in local tables. In
addition, communication with the main server is minimal. Transaction
load is a few thousands transactions a day.
ALL I am asking is how strong you think MySQL stands up in such a
business scenario. I have even created the manual business procedures
for power-failure scenarios. I DO need to know HOW gracefully MySQL
will recover after each power failure or pull-the-plug situations.
I have to decide as to which database server I am going to use. I
have so far only designed the db and written table-creation scripts
only.
I would genuinely appreciate your help and advice.
--
Best regards,
Asif
--
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]
