hey all.

i have a dedicated mysql server finally, and i was planning on having a little fun with it. in reality i am having alot of fun with it, too much. i now have a table with 6million records and 680mb, and several more at 400mb. I would like to know which way you think I should with upgrades go as in hardware. About my new mysql dedicated server: In desktop computer terms, it's a nice computer, but in server terms, it's kinda weak. i colo it along with some other pcs that serve my content; so it's mine and i can do what i want with it, so any suggestions will be performed by me and not a hosting co.

quick specs: P4 2.4 800fsb - 1gb ddr400 - 160gb ide ata/133 hard disk.

When I make a request on the table, it takes 16 seconds to load my page sometimes. Up until about 3 million records, i didn't notice a lag. Then after about 3.5 million it slowed down dramatically. I've played around with settings etc, compiled a few different ways etc, but I want more speed.

Which way should i upgrade?... should I replicate the server (ie: would replicating the server make requests on a large table faster, or would this only speed up requests on a busy machine on a reg. size table?, btw my machine isn't "busy") or should I save up for a dual xeon with like 16gb ram and dual sata drives (slobbering)? I can afford another computer ($800-1000 for another p4/ide/1gb) and replication looks 'not-so-hard', but I'd be saving for the dual xeon with gobs of ram for a while; and wouldn't be happy if it didn't cure my slow woes.

Or, is mysql just not for me? I have played around with the Berkeley DB library in C, and it's fast, but even with it, when i get up to a 1gb db file, it slows down a little bit and I imagine it would slow more if I went to 2 gb. I have also thought about writing my C program to cap the db files at about 500mb. So that I have a tree of directories with several db files in each. but, then i would have to search alot of db files to find my data. hmmm... =]

i really only expect your mysql recommendations. but if you have more to offer, i'd love to hear (other sql based db suggestions; postgre etc). you don't have to comment on my berkeley db stuff; just pointing out that i understand mysql might not be for me, but do understand that it's pretty fast and it's possible it's for me, provided i could obtain the right hardware, and which way i should go, replicate or get a super-duper kick butt puter.

thanks,
oliver

Software recommendation -

Database reporting tool - QLR Manager. Cheap and powerful. Version 2 coming out soon.

Have fun! (sounds like you are)

I have written some scripts that do some reporting for me, but I like to get my data on the fly. I wrote a script that takes the database and makes flat text database files, and when i access the flat text files, it will select a limit 1 row with the key that i want, then i displays all the data i want, and quickly, but it would be nice to know which way i should expand...