• 27 Posts
  • 205 Comments
Joined 3 years ago
Cake day: August 8th, 2023
  • RagingHungryPanda@lemm.eetoProgramming@programming.dev•database greenhorn
    1·
    1 year ago

    Thanks for giving it a good read through! If you’re getting on nvme ssds, you may find some of your problems just go away. The difference could be insane.

    I was reading something recently about databases or disk layouts that were meant for business applications vs ones meant for reporting and one difference was that on disk they were either laid out by row vs by column.

  • RagingHungryPanda@lemm.eetoProgramming@programming.dev•database greenhorn
    1·
    1 year ago

    yes? maybe, depending on what you mean.

    Let’s say you’re doing a job and that job will involve reading 1M records or something. Pagination means you grab N number at a time, say 1000, in multiple queries as they’re being done.

    Reading your post again to try and get context, it looks like you’re identifying duplicates as part of a job.

    I don’t know what you’re using to determine a duplicate, if it’s structural or not, but since you’re running on HDDs, it might be faster to get that information into ram and then do the job in batches and update in batches. This will also allow you to do things like writing to the DB while doing CPU processing.

    BTW, your hard disks are going to be your bottleneck unless you’re reaching out over the internet, so your best bet is to move that data onto an NVMe SSD. That’ll blow any other suggestion I have out of the water.

    BUT! there are ways to help things out. I don’t know what language you’re working in. I’m a dotnet dev, so I can answer some things from that perspective.

    One thing you may want to do, especially if there’s other traffic on this server:

    • use WITH (NOLOCK) so that you’re not stopping other reads and write on the tables you’re looking at
    • use pagination, either with windowing or LIMIT/SKIP to grab only a certain number of records at a time

    Use a HashSet (this can work if you have record types) or some other method of equality that’s property based. Many Dictionary/HashSet types can take some kind of equality comparer.

    So, what you can do is asynchronously read from the disk into memory and start some kind of processing job. If this job does also not require the disk, you can do another read while you’re processing. Don’t do a write and a read at the same time since you’re on HDDs.

    This might look something like:

    offset = 0, limit = 1000

task = readBatchFromDb(offset, limit)

result = await task

data = new HashSet\<YourType>(new YourTypeEqualityComparer()) // if you only care about the equality and not the data after use, you can just store the hash codes

while (!result.IsEmpty) {

offset = advance(offset)

task = readBatchFromDb(offset, limit) // start a new read batch



dataToWork = data.exclusion(result) // or something to not rework any objects

data.addRange(result)



dataToWrite = doYourThing(dataToWork)

// don't write while reading

result = await task



await writeToDb(dataToWrite) // to not read and write. There's a lost optimization on not doing any cpu work

}



// Let's say you can set up a read or write queue to keep things busy

abstract class IoJob {

public sealed class ReadJob(your args) : IoJob

{

Task\<Data> ReadTask {get;set;}

}

public sealed class WriteJob(write data) : IoJob

{

Task WriteTask {get;set;}

}

}



Task\<IoJob> executeJob(IoJob job){

switch job {

ReadJob rj => readBatchFromDb(rj.Offset, rj.Limit), // let's say this job assigns the data to the ReadJob and returns it

WriteJob wj => writeToDb(wj) // function should return the write job

}

}



Stack\<IoJob> jobs = new ();



jobs.Enqueue(new ReadJob(offset, limit));

jobs.Enqueue(new ReadJob(advance(offset), limit)); // get the second job ready to start



job = jobs.Dequeue();

do () {

// kick off the next job

if (jobs.Peek() != null) executeJob(jobs.Peek());



if (result is ReadJob rj) {



data = await rj.Task;

if (data.IsEmpty) continue;



jobs.Enqueue(new ReadJob(next stuff))



dataToWork = data.exclusion(data)

data.AddRange(data)



dataToWrite = doYourThing(dataToWork)

jobs.Enqueue(new WriteJob(dataToWrite))

}

else if (result is WriteJob wj) {

await writeToDb(wj.Data)

}



} while ((job = jobs.Dequeue()) != null)
  • RagingHungryPanda@lemm.eetoAsklemmy@lemmy.ml•has anyone done this or anything similar
    1·
    1 year ago
    1. How do you live day to day, spend the hours, get food, hygiene, etc?

    I work office hours at US central time, so things don’t really change a whole lot there. I wake up, do breakfast if it’s offered, hop on the morning meeting, and optionally find a cafe to work at.

    Many places offer breakfast and lots of hostels have kitchens and refrigerators, so I’ll take one of those eco bags that I got for $0.50 in latin america and buy a few things for a meal, or I’ll eat out, which I do too much.

    Hygiene is fine. Every place has showers and toilets - just wear flip flops or shower shoes in shared showers.

    1. I’m currently in the US. My company’s policy of work abroad changed, so I’ll be back here for a while.

    2. problems

    I’ve learned to be more flexible. Sometimes places have flakey internet, but I have my phone as a hotspot. Your plans won’t always work out and its fine, there will be another way to make it happen. There’s another bus, plane, lodging around. It’ll be fine.

    1. What is a DN?

    Your moving long term and have a digitally oriented job.

    1. money

    I kept my job and I saved a ton of money in latin america. To give you an idea, the cost of my not out of the ordinary apartment plus utilities in Austin, TX came out to about $68 per day. My housing costs in latin america ranged from $10-$40 per day. It’s hard to spend there in a day what I was doing just for rent. And that’s eating out 2-3 times per day.

    So I have way more money now.

    1. Best/worst

    Best part is the adventure and seeing the world. Worst might be that connections are fleeting and you need to be ok with the finality of things. I think this is good overall, but what I do is I’ll travel for a while and then settle for a month or more at a CoLive arrangement. Those have the benefits of hostels in the social aspect, but you often have your own room, sometimes a dorm, and people stay for minimum amounts of time, which is often a month.

    The conveniences of home can be missed a bit, but then I can rent a private room or a hotel if I want. Also, sometimes I want my me time, but then I retreat too much, so the social aspects of hostels can pull me out of that.

    1. Transportation

    Buses in Latin America are great. I flew sometimes, but I have local transit passes for most places that I went to. Going between cities, I often took the bus.

    1. Sustainable

    I’ve been doing it for a year and a half and don’t see myself stopping. I know people that have been doing it for 7+ years. It’s whatever fits you. You don’t have to fit a mold. You can try it for short term and go back, you can do it on and off, whatever. It’s your life. Live it. haha