Really large mysql db ? you worked with ?
 

hi all.,

out of curiosity has anybody here worked with really large mysql db ?

i am working on new project which has 167 gb database file
consist of 840 tables each of 5 mil records. each table is of 180-200 mb

just wanted to know whether can 8gigs ram and quadcore server handle it ?

not much queries., like 20k per day, simple find and echo queries.

its all gonna depend on the database design and the use.

if your db is well designed and queries are optimized and simple then yes.

If you are doing transactions, live updates, complex queries then you probably will not be happy with the performance, but if yer doing all of that you shouldnt be running it on mysql, you should be looking at Oracle or something. spread the DB across a couple of servers and load balance everything.

its all gonna depend on the usage.

:2 cents::2 cents:

some stats of mine
This MySQL server has been running for 149 days, 15 hours, 52 minutes and 29 seconds. It started up on Sep 09, 2011 at 10:30 PM.

Query statistics: Since its startup, 8,478,380,495 queries have been sent to the server.

Traffic 1 ø per hour
Received 631 GiB 180 MiB
Sent 18 TiB 5,111 MiB
Total 18 TiB 5,291 MiB

No probs, using 16Mb of ram
4k of tables

okie. this helps.,
only2 columns per table,
1 uniq id and another is numbers.
so i guess this is very simple

good, this is awesome.,
but mine is not only db server its all in one server like apache, mail , database.
anyways, i m gonna give it a go on my current server.,
thanks

i know b0rked uses a huge mysqldb for his geoip stuff, records in the millions at least

mine contains, 4 200 000 000
i m gonna give it try., if server starts acting weird then ill pull the db.

innodb or myisam? do you have blob or text fields?

MySQL is probably the wrong tool for the job in that case. Under the hood, MySQL sometimes uses Berkeley DB to store the data. Berkeley DB is simple and very efficient, but it can only store key -> value pairs (what you have). MySQL does a lot of extra work to make key -> value look like a table with multiple columns of various types and make it possible to do complex SQL queries on the data. All that work is wasted if you just want to store and retrieve keys and values. For that, use Berkeley directly.

i would use a seperate server for sql.

How in the hell are you going to move 167gb database from one server to another?

It should work if indexes are good and there are no complex queries...

You should *very seriously* look at migrating this database over to apache solr. You will get much, much better response with a tiny footprint for such a key/value pair database. Run with either the standalone Jetty app or bundle it behind Tomcat. :2 cents:

myisam , no text fiends

zip and ftp ....
okie., need to learn that thing.

is hard drive configuration an important consideration here?

nope., only server load.

Good queries are important... monyog can help with seeing where problems are...