Heya folks;

I am a expereinced user with MySQL and all, but never dealt with having
about 2,000,000 (2million) rows of data in a table, I am interested in what
OS, harddrives, cpu/ram/mobo combo's people have to recommend.

I need the ability to pull multiple rows out of the 2mil+ records, I am doing
WHERE statements with primary Id's and indexing which helps alot, but I need
results faster, currently this is what I've got going:

I've got about ~250,000 rows in this table, I can select with WHERE and LIMIT 0,10.
It takes 1.5 seconds on average, on a FreeBSD 5.2.1pl14 box which is
a fairly hefty machine, 1gig ram, P4 2.4ghz HT.

I am not fond of linux, but I am considerring it for its multithreading support.
I know FreeBSD 5.x supports it, but linux'es is still better. (i guess?).

What do you folks recommend for hardware?
I would like to be able to select via WHERE/LIMIT 10 with offset and get back
the results within a split second. I need the data super fast and will be running
alot of these calls (selects), updates/inserts are rare, data is nearly static.

Let me know what you think, or if you have SQL-side ideas also, for performance. Like key buffer sizes, using InnoDB vs MyISAM etc, etc.

Fully open to suggestions and examples if anyone has any. Thanks alot!

If you need more details on my data/content, just say so.

-Jason
jlschwab@jlschwab.com

I don't remember what BSD provides in the way of "instrumentation", but I'd bet that you are short on RAM, and maybe short on CPU too. Before I invested a lot of time and energy on switching horses, I'd see if adding a bit of RAM would help.

-PatP

I have MySQL setup using 768 megs of the 1gig of ram, didn't make a difference when it was using only 128 megs. I know I need more processor, thats why im asking what people recommend

I think you should be using a dual processor machine, but that's just me.

As far as I am concerned InnoDB is the best table type you can get as of now. I use it exclusively. From the MySQL documentation:


InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs. These features increase multiuser concurrency and performance. There is no need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. InnoDB is the first storage manager in MySQL to support FOREIGN KEY constraints.

InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

InnoDB is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores over 1 TB of data in InnoDB, and another site handles an average load of 800 inserts/updates per second in InnoDB.

Technically, InnoDB is a complete database backend placed under MySQL. InnoDB has its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored as a separate file. InnoDB tables can be of any size even on operating systems where file-size is limited to 2 GB.

Ah, thanks for the info on InnoDB, i'll do some comparisons on them.

Oh yeah, MySQL also has clustering solutions now, so you may want to look at some of that stuff too.

I disagree with the assertion that "you need more than one processor." Database work is an I/O-bound operation. not CPU-bound. If tripling the amount of RAM did not help performance, doubling the speed of the CPU won't do it either.

Implementing a faster I/O subsystem (SCSI and so-forth) might have a measurable effect, but probably the most important thing will be indexing, to minimize the amount of I/O activity that takes place.

Most operating systems you are likely to encounter support multi-threading these days, so you need not consider a Windows-to-Linux switch.

Most of Section 5 of the MySQL documentation concerns optimization and performance. You can run a query and measure how the time is being spent. You need to do that. Guesswork, and "throwing hardware at it," is really not serving your purposes well. (Note: no personal affront whatsoever is intended in the foregoing sentence.)

I know, I was just saying that it can handle the threaded requests faster because it can TRULY multitask, not just preemptive multitask. I agree with the query tuning though.

I seem to have a relating issue, but please redirect if I’m not in the right thread…
I am wondering why MySQL would stop taking records at around 1/2 million records when running to input a 1 million record file.
I ran a vb file that loads the database through MyODBC 3.51 as system DSN. This is done at the speed of nearly 50,000 record per/second with Serial ATA RAID 0/1. However, there is a problem that occurs. As the VB exe keeps running to claim it finishes to a million record update, the table in the database (which is MyISAM) shows a COUNT of about and around over 1/2 million records were inputed. This happens repeatedly.
I'm runnung MySQL 4.1 alpha on XP Pro with 2 gigs mem.

Speaking of memory maybe it’s noteworthy that the memory usage steadily increases during record update on the table. When the memory LEVELS from the increasing slope (this is at around where 1/2 million records ALREADY processed) the VB exe keeps going to 1 million in count (EOF).
Am I missing out on something about MySQL or is it the XP? What to do so one can avoid such table lock out if it is one? Is it MyODBC stopped working or XP not adequate enough?