What to do with a 1TB DB

Notice: Page may contain affiliate links for which we may earn a small commission through services like Amazon Affiliates or Skimlinks.

p_cubiche

New Member
Jul 13, 2017
8
0
1
44
I am assisting with a project and was just told that the TB could grow larger than a TB.

What would be your recommendation as in how to provision such environment when running VMMware?

I have my thoughts about it but just checking how would other people do it as well.

Thanks!!
 

Evan

Well-Known Member
Jan 6, 2016
3,346
598
113
1TB oracle or sql with compression or mongo DB or what ???

Anyway 1TB is not an issue being virtual either it comes down to transaction rates etc.
 

Blinky 42

Active Member
Aug 6, 2015
615
232
43
48
PA, USA
Other questions to consider and help characterize what the workload is like Also:
- how is it configured now and does that work for them or not?
- reads vs write traffic
- % of data that changes on a normal basis (daily hourly whatever)
- DML done row at a time or in batches?
- Any mega rebuilds of data done (rebuild summary tables/ materialized views. cubes, all that fun stuff) and how often?
 
  • Like
Reactions: p_cubiche

Markus

Member
Oct 25, 2015
78
19
8
Beside technical stuff, keep the commercial stuff in mind.
If you are using an Oracle DB you probably don't want to mess around with the licence-model in combination with VMWare...

Regards
Markus
 
  • Like
Reactions: p_cubiche and _alex

BobbyB

Member
Dec 26, 2016
33
10
8
74
Throw memory and CPU at it within reason, licensing constraints if any considered as was mentioned. Vast majority of DB performance problems are due to coding and/or sql and not hardware sizing, however. Best thing you can do for a database is find out top x statements by execution time, improve either code or sql, rinse and repeat.
Find out usage profile, OLTP vs OLAP or combo, what is the driving force behind performance - high transaction count (sales orders etc), analytics or something else. Is/will most of the data become warm/cold data after a while or is the entire growing dataset being changed?
Is this purely for DB or is there a middleware component utilizing same server? If later, identify requirements for each, not necessarily to split them up but to understand what needs more attention.

Size alone is no indication of performance requirements, people just like large numbers. Have 2-4+TB OLTP DBs which work just fine with 3-4vCPU in production (with barely ~40% of that reserved) and 48G RAM but also 300GB DBs with 48CPUs that definitely needs the compute power.
 
  • Like
Reactions: p_cubiche

p_cubiche

New Member
Jul 13, 2017
8
0
1
44
1TB oracle or sql with compression or mongo DB or what ???

Anyway 1TB is not an issue being virtual either it comes down to transaction rates etc.
It is Microsoft SQL


Sent from my iPhone using Tapatalk
 

p_cubiche

New Member
Jul 13, 2017
8
0
1
44
Thanks a lot for your suggestions.

Mostly my question goes around how to host it within a VMware environment.

As in:
- would have one 1+ TB VM living on local storage and packing it up
- would you have a local small VM and then running the DB off a NAS via CIFS share
- Or just run the whole VM off a NAS as well as mounting a CIFS share to run the DB from it ?

Thanks,



Sent from my iPhone using Tapatalk
 

Blinky 42

Active Member
Aug 6, 2015
615
232
43
48
PA, USA
I wouldn't suggest running any sort of DB on a NAS over using direct-attached (virtual) disks if you can avoid it.
The filesystem caching that the NAS box will do doesn't play well what the way a SQL database engine makes use of the files stored on the drive and your performance will be sub-par. Also unless you have a network connection between your VMWare box and the NAS box that is faster than the drives that are providing the data then your performance will also suffer greatly.
 

EffrafaxOfWug

Radioactive Member
Feb 12, 2015
1,394
511
113
Why would you run this DB over CIFS rather than presenting a LUN (either local virtual disc or iSCSI perhaps) to the database server itself...? As BLinky42 alludes, the access patterns a DB engine performs are pretty much the worst-case scenario for presentation over protocols like CIFS.

Think you need to give some more information about what your infrastructure looks like in order for us to give better answers.
 
  • Like
Reactions: p_cubiche

p_cubiche

New Member
Jul 13, 2017
8
0
1
44
Why would you run this DB over CIFS rather than presenting a LUN (either local virtual disc or iSCSI perhaps) to the database server itself...? As BLinky42 alludes, the access patterns a DB engine performs are pretty much the worst-case scenario for presentation over protocols like CIFS.

Think you need to give some more information about what your infrastructure looks like in order for us to give better answers.

yes, a LUN could be presented as well, was just generalizing, looking for recommendations or what are you folks using out there.

Mostly having one large VM vs Hypervisor + Storage attached to it, which method would you rather use.

Thx!
 

Blinky 42

Active Member
Aug 6, 2015
615
232
43
48
PA, USA
If you don't need to put the storage for the DB on a different box, then I would put it where it will perform best.
If the DB is only a TB or 3, that can fit on 2.5" drives no problem so you don't need a SAN setup just to handle the storage.

If you do push it off to an external storage device, I would present it as a block device (iSCSI LUN). But there still should be a reason to have it external to offset the:
- complexity / more points of failure
- increased latency and lower b/w unless you are doing > 10Gb ethernet / IB
 
  • Like
Reactions: Aestr

dwright1542

Active Member
Dec 26, 2015
377
73
28
50
Thanks a lot for your suggestions.

Mostly my question goes around how to host it within a VMware environment.

As in:
- would have one 1+ TB VM living on local storage and packing it up
- would you have a local small VM and then running the DB off a NAS via CIFS share
- Or just run the whole VM off a NAS as well as mounting a CIFS share to run the DB from it ?

Thanks,



Sent from my iPhone using Tapatalk
MSSQL doesn't like really anything on shares(You can't even backup to a share natively). I can't imagine ANY nas having the horsepower you're looking for to run that size DB, unless it's 1-2 users and they don't care about performance.

How many records? If it's 100 records with huge videos, then it may work.
How many users?
What are they doing with it? Reporting? Entry?
What's the change delta daily?
What about redundancy?
What about backups?

You need to give us more info to help.
 

Net-Runner

Member
Feb 25, 2016
81
22
8
40
Regardless of the precise technical details you should always keep the DB as close to the storage as possible. Directly attached storage (RAID or RAIN or whatever) with minimal latency and reasonable redundancy since it's most probably one of the most crucial elements of your production infrastructure.
 

acquacow

Well-Known Member
Feb 15, 2017
784
439
63
42
MS SQL, create 1 tempDB per core, store those tempDBs on local flash SSDs, put the rest of the DB wherever it fits.
 
  • Like
Reactions: wildchild

p_cubiche

New Member
Jul 13, 2017
8
0
1
44
MSSQL doesn't like really anything on shares(You can't even backup to a share natively). I can't imagine ANY nas having the horsepower you're looking for to run that size DB, unless it's 1-2 users and they don't care about performance.

How many records? If it's 100 records with huge videos, then it may work.
How many users?
What are they doing with it? Reporting? Entry?
What's the change delta daily?
What about redundancy?
What about backups?

You need to give us more info to help.
Not fully designed out yet hence asking ahead of time