Tips for LARGE mysql system

[svar]

I need to come up with an intra-company system recommendation: We are looking at a possibly huge system
handling from 3M to 20M records/day with a lot of processing, live inserts/ updates etc.
I have a demo version(running on a PIII 2x1000MHz, 2GB RAM
Linux machine) but it cannot handle the full load.
I am thinking of keeping Mysql 4-0.4. and going
to an alpha machine(running linux). The question is whether anyone has done this before. So I am looking for tips and recommendations(before buying any hardware):

Are there any known or expected problems with Mysql on alpha machines?

Do I need a special linux version(the kernel I use says 2.4.18-4GB, but I need much more RAM and
in fact this is the main reason for going to an alpha machine)?(never used linux on anything except Pentiums)

Are there any guidelines in estimating more presicely what hardware I will need?

Thanks,
svar

 

[Chopsaki]

Arghh... common, please, you cannot be serious? mySql and that traffic. Sure you can get it running, but can you keep it running, is it easy to maintain, etc, etc? Why don't you suggest a proper RDBMS instead of mySql. Don't get me wrong mySql is a great little/medium size database but heck, data management is a pain, scalability is pretty non-existant & it lacks a load of fundamental features of large RDBMS'. I could go on but I fear it is futile. Where is your scalability? Just sticking more ram in the system? Nice. You may want to think about lateral moves (building out / clustering) for some redunacy and some load balancing upfront. foo

 

[svar]

Wow! This is very different from the reply I get from Mysql people, both in the forums and officially. In case of doubt(I am not sure the word 'traffic' is right), this is NOT a web-based system. Just a system
to get some data over a LAN, pare them(both these steps are very fast and database-independent) and store them
in the database, plus some more apps to do some queries.

I must say I have never worked with any other RDBMS, so I
really do not know. It is clear I need to find out
how serious the problems you mention are and do it fast
before I come up with a solution that will not work. So I'd
appreciate if you can please explain or point me to a reference:
1. What do you mean datamanagenet is a pain?
2. Why is scalability nonexistent? I may be able to get a
large alpha machine with some 64 or more GB or RAM
and hope this is enough. When and if this becomes inadequate,
I thought well, get another alpha and cluster them(sounds simpler than it is. I do not think I can get a cluster of
PCs right now due to physical space issues).
What would a real RDBMS do differently?
My understanding based on Mysql forums is that it would be cheaper to buy more RAM than a big RDBMS, no?


3. Mysql 4.1 which is due out at best by the end of October
and at worst by early next year should have triggers
and these other fundamental features, no?
InnoDb has been working fine with me with commit etc
tough admittedly not on such a large system.

Thank you very much for your comments, hopefully I get get some answers or pointers to solve these issues soon.
Thanks again,
svar

 

[HenryMonster]

SVAR,

I am not a mySql expert, but let me share some
common sense based considerations with you.

1) mySql is a great product at least because
a) it's free (almost free),
b) it does the job a relational database
is supposed to do (almost does).

Of course the are some negative aspects here as well
and you have to consider them:

2) if you have a lot of transaction processing
in your project, then data integrity may become
quite an issue; yes, there are these InnoDb tables,
but you have to verify how stable and reliable they are
(I frankly do not know).

3) How should your application work ? 7x24 or you can
allow some legitimate down time ?
If it's 7x24, you need a 'hot backup utility', which
would allow to back up you data in a consistent
fashion while you database is up and running.
Do they have it in mySql ?

If you can have some down-time, say nightly,
you have to estimate the potential size of you database
and find out whether you can backup the whole database
to some other media/computer during this down-time period.

4) You have to estimate the impact of the potential
data loss for your company in financial terms.

5) Consider using RAID 0 + 1 (preferably from a good
brand name) to improve I/O throughput and
reliability of data storage.

6) If mySql supports multiprocessor architecture
(I ran into some hints it does), then consider having at
least a two-processor system.

7) Of course T.C.X DataKonsult AB as well as mySql
zealots (and there are many out there)
would describe mySql as the fastest, the easiest
and the best database in the world. Given all
positive sides of mySql we should not however
fool ourselves into thinking that a company with
several dozens developers working remotely in different
parts of the globe can supply a product that outshines
databases developed by the pillars of the industry,
such as Oracle, IBM (DB2), Sybase and... even
Microsoft SQL server.

mySql does have its niche and you have to figure out
whether your app can fit in there.

8) Another advice is: while writing you application
(and I do not know what tools you will be using)
whenever possible when you manipulate with the data
try to use the most standard forms of SQL or place you
code in some wrapper functions so that if eventually
your company fills strong enough to aquire one of
commercial databases, your migration from mySql to this product will be painless.

 

[svar]

Henry,
Thanks very much for your posting,
in my experience I have had terrible problems with MyIsam
tables ( a nasty non-recoverable table corruption maybe every day or 2). This was with previous versions though. The reason I like InnoDB is I never had any table corruption with it.
Everything you say makes sense. I also hear many good things about Mysql. I will probably give it a shot
but the app is standard SQL and does not use any Mysql-specific part. Just the vendor name in some data file(in fact, this architecture has been tested with Oracle also)
Thanks again,
svar