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

X

cooldude7

Confirmed User

Join Date: Nov 2009

Posts: 4306
Share

Post
#1

Really large mysql db ? you worked with ?

02-05-2012, 07: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.
Tags: None
mikesouth

Confirmed User

Join Date: Jun 2003

Posts: 6340
Share

Post
#2

02-05-2012, 08: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.

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

Too lazy to set a custom title

Join Date: Jun 2005

Posts: 27043
Share

Post
#3

02-05-2012, 09:44 PM

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.

Make Money
Comment
grumpy

Too lazy to set a custom title

Join Date: Jan 2002

Posts: 9870
Share

Post
#4

02-06-2012, 03: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

Don't let greediness blur your vision | You gotta let some shit slide
icq - 441-456-888
Comment
cooldude7

Confirmed User

Join Date: Nov 2009

Posts: 4306
Share

Post
#5

02-06-2012, 03:38 AM

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

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

Last edited by cooldude7; 02-06-2012, 03:40 AM.
Comment
fris

I have to go potty

Join Date: Aug 2002

Posts: 55795
Share

Post
#6

02-06-2012, 03:49 AM

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.

My Newest Theme
Comment
cooldude7

Confirmed User

Join Date: Nov 2009

Posts: 4306
Share

Post
#7

02-06-2012, 04:58 AM

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

ORLY?

Join Date: Oct 2005

Posts: 2579
Share

Post
#8

02-06-2012, 05:11 AM

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
Comment
raymor

Confirmed User

Join Date: Oct 2002

Posts: 3745
Share

Post
#9

02-06-2012, 05:13 AM

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.

For historical display only. This information is not current:
support@bettercgi.com ICQ 7208627
Strongbox - The next generation in site security
Throttlebox - The next generation in bandwidth control
Clonebox - Backup and disaster recovery on steroids
Comment
fris

I have to go potty

Join Date: Aug 2002

Posts: 55795
Share

Post
#10

02-06-2012, 05:51 AM

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.

Since 1999: 69 Adult Industry awards for Best Hosting Company and professional excellence.

My Newest Theme
Comment
HomerSimpson

Too lazy to set a custom title

Join Date: Sep 2005

Posts: 13826
Share

Post
#11

02-06-2012, 08: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...

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:
Comment
borked

Totally Borked

Join Date: Feb 2005

Posts: 6284
Share

Post
#12

02-06-2012, 12:50 PM

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.

For coding work - hit me up on andy // borkedcoder // com
(consider figuring out the email as test #1)

All models are wrong, but some are useful. George E.P. Box. p202
Comment
cooldude7

Confirmed User

Join Date: Nov 2009

Posts: 4306
Share

Post
#13

02-06-2012, 06:48 PM

Originally posted by AdultEUhost

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

myisam , no text fiends

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

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.
Comment
d-null

. . .

Join Date: Apr 2007

Posts: 13724
Share

Post
#14

02-06-2012, 07:16 PM

is hard drive configuration an important consideration here?

__________________

Looking for a custom TUBE SCRIPT that supports massive traffic, load balancing, billing support, and h264 encoding? Hit up Konrad!
Looking for designs for your websites or custom tubesite design? Hit up Zuzana Designs
Check out the #1 WordPress SEO Plugin: CyberSEO Suite
Comment
cooldude7

Confirmed User

Join Date: Nov 2009

Posts: 4306
Share

Post
#15

02-06-2012, 08:44 PM

Originally posted by d-null

is hard drive configuration an important consideration here?

nope., only server load.
Comment
V_RocKs

Damn Right I Kiss Ass!

Join Date: Nov 2003

Posts: 32451
Share

Post
#16

02-06-2012, 10:32 PM

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

Previous template Next

Working...