Really large mysql db ? you worked with ? - GoFuckYourself.com

cooldude7 · 02-05-2012, 09:45 PM

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.

mikesouth · 02-05-2012, 10:30 PM

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.

martinsc · 02-05-2012, 11:44 PM

Quote:

Originally Posted by mikesouth

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.

grumpy · 02-06-2012, 05:24 AM

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

cooldude7 · 02-06-2012, 05:38 AM

Quote:

Originally Posted by mikesouth

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.

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

Quote:

Originally Posted by grumpy

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

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

fris · 02-06-2012, 05:49 AM

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

cooldude7 · 02-06-2012, 06:58 AM

Quote:

Originally Posted by fris

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.

AdultEUhost · 02-06-2012, 07:11 AM

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

raymor · 02-06-2012, 07:13 AM

Quote:

Originally Posted by cooldude7

only2 columns per table,
1 uniq id and another is numbers.

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.

fris · 02-06-2012, 07:51 AM

Quote:

Originally Posted by cooldude7

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

i would use a seperate server for sql.

HomerSimpson · 02-06-2012, 10:00 AM

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...

borked · 02-06-2012, 02:50 PM

Quote:

Originally Posted by cooldude7

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

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.

cooldude7 · 02-06-2012, 08:48 PM

Quote:

Originally Posted by AdultEUhost

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

myisam , no text fiends

Quote:

Originally Posted by HomerSimpson

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...

zip and ftp ....

Quote:

Originally Posted by borked

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.

okie., need to learn that thing.

d-null · 02-06-2012, 09:16 PM

is hard drive configuration an important consideration here?

cooldude7 · 02-06-2012, 10:44 PM

Quote:

Originally Posted by d-null

is hard drive configuration an important consideration here?

nope., only server load.

V_RocKs · 02-07-2012, 12:32 AM

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

02-05-2012, 09:45 PM	#1
cooldude7 Confirmed User Industry Role: Join Date: Nov 2009 Location: Heaven Posts: 4,306	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.

02-05-2012, 10:30 PM	#2
mikesouth Confirmed User Industry Role: Join Date: Jun 2003 Location: My High Horse Posts: 6,334	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. __________________ Mike South It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.

02-06-2012, 05:24 AM	#4
grumpy Too lazy to set a custom title Join Date: Jan 2002 Location: Holland Posts: 9,870	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 __________________ Don't let greediness blur your vision \| You gotta let some shit slide icq - 441-456-888

02-06-2012, 05:49 AM	#6
fris Too lazy to set a custom title Industry Role: Join Date: Aug 2002 Posts: 55,313	i know b0rked uses a huge mysqldb for his geoip stuff, records in the millions at least __________________ Since 1999: 69 Adult Industry awards for Best Hosting Company and professional excellence. WP Stuff

02-06-2012, 07:11 AM	#8
AdultEUhost ORLY? Industry Role: Join Date: Oct 2005 Location: NL & US Posts: 2,579	innodb or myisam? do you have blob or text fields? __________________ ICQ: 267-443-722 / leon [at] adulteuhost [dotcom] Nominated for an XBIZ Award as "Webhost of the Year" in 2007, 2012, 2013 and 2014

02-06-2012, 10:00 AM	#11
HomerSimpson Too lazy to set a custom title Industry Role: Join Date: Sep 2005 Location: Springfield Posts: 13,826	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... __________________ Make a bank with Chaturbate - the best selling webcam program Ads that can't be block with AdBlockers !!! /// Best paying popup program (Bitcoin payouts) !!! PHP, MySql, Smarty, CodeIgniter, Laravel, WordPress, NATS... fixing stuff, server migrations & optimizations... My ICQ: 27429884 \| Email:

02-06-2012, 09:16 PM	#14
d-null . . . Industry Role: Join Date: Apr 2007 Location: NY Posts: 13,724	is hard drive configuration an important consideration here?

02-07-2012, 12:32 AM	#16
V_RocKs Damn Right I Kiss Ass! Industry Role: Join Date: Dec 2003 Location: Cowtown, USA Posts: 32,405	Good queries are important... monyog can help with seeing where problems are...