The Heap™ – Consultancy ©®

« first day (2061 days earlier) ← previous day next day → last day (2800 days later) »

12:26 AM

@Lamar Yes, that's the correct idea. Replica is an exact copy of the data on different hardware to ensure availability should one box fail. Sharding is about splitting the data into different ranges and putting each range on separate hardware to provide higer throughput / scale out.

Of course each shard is likely to be replicated so that if one of the boxes supporting that shard fails the data is still available.

Both replication and sharding can be implemented in relational and NoSQL products. The latter tends to have sharding baked-in; for the former it's "bake your own" or pay $$ for top-end warehouse solutions.

Lamar

@MichaelGreen but the thing is

let's say that our shard cluster is composed of 3 server instances, then we use 2 extra instances for replica set for each shard in total that would be 9 instances right ?

now my main problem is that I have looked at few google image of that architecture, and it is like the config servers, and the mongos (which act like a load balancer) are a different server themselves ?

5 hours later…

Andriy M

5:45 AM

"How can I make this faster? I've already pressed all the Turbo buttons."

Q: can i make this multiple join query faster

Just wondering if there is any opportunity to make this query any faster. I have many joins from multiple tables and am wondering if I can make this faster. I have a group by in there as well so am wondering am I missing anything? It is from large DBs but it's taking hours. select csi.ItemIDNum...

1 hour later…

Tom V

6:58 AM

A: Why is this explicit cast causing problems?

So, I was able to reproduce the error after realizing that the CAST was being done locally, not on the remote instance. I had previously recommended moving up to SP3 in the hopes of fixing this (partially due to not being able to reproduce the error on SP3, and partially due to it being a good id...

interesting ^^

5 hours later…

ypercubeᵀᴹ

11:31 AM

Since the inputs are not datetime variables/columns but varchar strings, I'd use CONVERT() to make sure that the style 20 is used. CAST(string AS DATE) may depend on various locale/language connection/database/instance settings. — ypercubeᵀᴹ 1 hour ago

@AaronBertrand I couldn't find something specific about this in your bad habits series. So I'm not sure if my comment is correct.

Andriy M

Curious:

> CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression.

Don't remember ever hearing about that.

Lamar

Hey guys, is anyone familiar with mongodb?

ypercubeᵀᴹ

11:53 AM

@AndriyM decimal and numeric are synonyms, aren't they?

Andriy M

@ypercubeᵀᴹ I think they are (in Transact-SQL)

Maybe they meant numeric in the broader sense, although I still fail to make sense of that.

In any event, they are contrasting CAST with CONVERT there, so I just can't see how CONVERT would be less appropriate, which is my main issue with that statement.

As far as I'm aware, without a third argument CONVERT behaves exactly like CAST.

In all cases.

ypercubeᵀᴹ

@AndriyM I agree.

They might mean that a constant like 3.440 will need CAST to preserve the 3 decimal points. But that could be done with CONVERT as well.

And in any case, this is irrelevant to the question.

Andriy M

I still decided to ask the answerer about it. Maybe they know something.

ypercubeᵀᴹ

12:36 PM

@Lamar probably 0 people, in this room

Lamar

ok thank you @ypercubeᵀᴹ

ypercubeᵀᴹ

you can still ask a q at the site ;)

Michael Green

@Lamar Yes, that's how to do it. Of course you don't have to have three replicas per shard. You can have one, or 12, or .... whatever. All depends on your risk appetite, uptime requirement and RTO.

Lamar

12:52 PM

so if I have 3 shard instances, I could had 2 more instances for each for replica secondary, and i would 3 instance for config servers, and 1 instance for mongos @MichaelGreen

is that right?

Michael Green

Having the config servers in a replica set stops that being a single point of failure.

Tom V

Oh @MichaelGreen is volunteering to be the mongo expert from now on :)

Lamak

@MichaelGreen you'll never live this down

Michael Green

Some of these services can share hardware so the cost need not be astronomical. If your running on VMs be sure each replica in a set is on a different host, else no HA!

Lamar

of course I am using either AWS EC2 or DigitalOcean droplets @MichaelGreen

Michael Green

1:13 PM

Let's say you have two offices, one in East (E) and one in West (W). Each mainly deals with their own data but sometimes needs to read the other's. Makes sense to shard the data so it stays local to the owning office but is accessible to the other.

E has 24x7 support and reckon they can replace a dead server in 1hr. They decide to have just two replicas in the set - E1 and E2. The business accepts the risk of a once-a-decade double failure. W, however, have no weekend support. They want uptime no matter what and have three replicas in the set - W1, W2, W3.

The config servers could be, say, one in either region, in their own replica set. So, servers EC and WC. They copy data between themselves. But this happens relatively rarely in my made-up example so this is good enough.

As for mongos, well let's install that on each app server. So E and W have as many as they need to support their needs. This way the mongos isn't a single point of failure.

HTH

@Lamak one more nail in the coffin of my street cred. Ho hum.

Lamar

1:32 PM

Thank you @MichaelGreen

8 hours later…