The Heap™ – Consultancy ©®

6:39 AM

Morning

7:19 AM

@SpringLearner Utter horseshit

Peter Vandivier

7:46 AM

Morning

McNets

7:59 AM

Morning

2 hours later…

Philᵀᴹ

10:07 AM

Ah, today will be the day our government falls

10:36 AM

@Philᵀᴹ ...again

1 hour later…

11:41 AM

@Philᵀᴹ I thought that would've been yesterday already

Hi. What is the difference between PostgreSQL checkpoint process and the way MySQL InnoDB writes dirty pages on disk? Why pros and cons of both solutions? Please give some links with comparison of algorithms of writing dirty pages on disk in different DBMS systems?

Philᵀᴹ

12:07 PM

@pavelkolodin Hi. Ask a question on the site. It might be too broad though

@pavelkolodin Why do want to know? I know PGSQL has some good resurces about this (and WAL etc) in their docs. What is missing from these?

12:57 PM

Wee Yearling with 200 Rep (again)

1:34 PM

@gbn I need to know that is key difference. What PG do, and InnoDB does not and vice versa. Why InnoDB needs Double Buffer, bug PG does not.

Philᵀᴹ

They're mainly just design decisions

SpringLearner

hello everyone

2:14 PM

@pavelkolodin a quick google shows percona.com/blog/2006/08/04/innodb-double-write

Basically it's a workaround for MySQL not doing proper WAL

xaprb.com/blog/2010/02/08/…

@gbn I like that they do it in batches, sort of....

> InnoDB implements a checkpoint mechanism known as fuzzy checkpointing. InnoDB flushes modified database pages from the buffer pool in small batches. There is no need to flush the buffer pool in one single batch, which would disrupt processing of user SQL statements during the checkpointing process.
>
> During crash recovery, InnoDB looks for a checkpoint label written to the log files. It knows that all modifications to the database before the label are present in the disk image of the database. Then InnoDB scans the log files forward from the checkpoint, applying the logged modifications…

Reference: InnoDB Checkpoints (MySQL 8.0 Reference)

So basically we can say that PostgreSQL is smarter with its long checkpoint process?

2:49 PM

I wouldn't use the word smarter. I'd go for more efficient. (MOO)

> To avoid flooding the I/O system with a burst of page writes, writing dirty buffers during a checkpoint is spread over a period of time. That period is controlled by checkpoint_completion_target, which is given as a fraction of the checkpoint interval.

> The I/O rate is adjusted so that the checkpoint finishes when the given fraction of checkpoint_segments WAL segments have been consumed since checkpoint start, or the given fraction of checkpoint_timeout seconds have elapsed, whichever is sooner. With the default value of 0.5, PostgreSQL can be expected to complete each checkpoint in about half the time before the next checkpoint starts.

Reference: WAL Configuration (PostgreSQL)

@SpringLearner All indexes have performance overheads on writes... An FK without an actual index backing it up can have severe enforcement overheads (imagine tablescans to check for referential integrity on every insert in child and deletion in parent). But PK-FK on its own is basic fundamental referential integrity and if you need it there are some overheads and ways to have them be reasonable. If you don't need it, then there aren't.

Last day on the force here! Start new job on Monday!

@hot2use yes, i mean this by "smarter" :)

SpringLearner

@CadeRoux would you recommend to implement if inserting/updations occures very frequently and if there are millions of records?

I guess if you have implemented primary-foriegn key relationships, then I dont think this will impact perfomance in search operations

3:10 PM

@SpringLearner depends what you mean by frequently. Number of rows should not matter (that's the point of b-trees). Number of indexes would matter. When you insert a row in a table, is it a heap or a clustered index? What other indexes need to be updated? What other indexes need to be checked (FK)? PK-FK and indexes will only typically improve performance in read/seek situations.

If you are in a high-speed bulk-processing situation like data loads or logging, you might not want many indexes at all so that the transaction volume can be handled. But you might later process that data into a different model which is indexed.

2 hours later…

Zane

5:23 PM

I have exciting news folks. i'm out of this dump!

jadarnel27

The Heap might be a little dated, but I wouldn't call it a dump.

It's lived-in.

Zane

lol

2 hours later…

7:04 PM

Seems like everyone is getting new jobs these days

@TomV you got a new job?

or did I knew that already?

jadarnel27

Cade Roux was talking about starting a new job next week.

I don't want to be left behind....but I changed jobs on February

@jadarnel27 did you call us old?

Pff youngster doesn't even have a real beard

7:24 PM

That's PFY's for ya.

I had to Google that. I suppose that confirms I'm old

@TomV I'm waiting....

@TomV never heard of BOFH ? Are you in for a great day.

but first, the DELETE QUESTION queue is calling :-D

@Lamak ~~JEAGL~~ JEAML duckduckgo.com/?q=bofh+pfy&t=ffsb&atb=v65-7&ia=web

dezso

@TomV I'd guess too young

*JEAML - jokes explained at minimal length :-O

7:32 PM

Oh I read bofh. Somewhere I lost track of the acronyms

@MaxVernon that goes against everything we believe on

@MaxVernon out of votes again :(

@TomV ahhh, I figured there was no way you'd not read BOFH

@TomV yeaaah…...me too

Q: Correct deletion of files in pgsql_tmp

@TomV oh well. There's always tomorrow!

I'll back away from the delete button.

dezso

7:34 PM

Sometimes we have to kill running processes at Operating System level (kill -9 PID) to solve issues related to max_connections value reached. In such cases, we targeted long running SELECT queries to kill. As a result, we found out that our filesystem grows at 98% and fills up quickly, showing...

why people do such things?

I didn't realize they still post new bofh

@dezso incompetence

Just reboot the server like a pro

Wow - so on my last day: the CFO's last day is also today, the director who originally hired me before I got split off into another division told me he submitted his resignation this morning, and the HR person who did my exit interview is leaving after just a couple months.

well...sounds like a good decision then

@CadeRoux sounds like our place

I'd been there 19 months.

New gig on Monday! Excited

7:39 PM

@TomV are you changing jobs?, I'm so confused

@CadeRoux you can help the remainers to a heart medicine now though

@Lamak can't say yet because I don't know yet

@TomV that explains why I'm confused then

I could explain in slack if you want

sure

Wait, there's a Slack?!

7:44 PM

@PaulWhite are you going to the summit in march?

Q: What is the industry term for superset of "metrics" and "dimensions"

8:00 PM

@Lamak No it's too far & expensive.

McNets

8:10 PM

If I want to refer to metrics and dimensions with a single term, what would be a good industry-accepted term?

terminology

Do you think this a DBA question?

@PaulWhite aaaw

:'(

Zane

@McNets It'as a battle you'll have to take to the semantics dome

@McNets Sounds like a BI/SSAS question. No comment on how good a question it is.

You mean measures instead of metrics? — Tom V 13 secs ago

I wont vtc just yet but it's not a great question so far

McNets

Time And Relative Dimension In Space

8:20 PM

@PaulWhite I'm lucky, my company is paying the trip & expenses

Although I could think of a decent answer

Great Question™ detected

@Lamak Paul's employer is difficult to talk to I heard

@TomV I am indeed.

@TomV yeah, sounds like it

Q: What are Measures and Dimensions in Cubes

8:27 PM

May be a dupe of this one

I'm very new to Microsoft Sql Server Business Intelligence and Analysis Service(but I'm programming for years with SQL Server).Can any one describe Measures and Dimensions in Cubes in Simple words(If it's possible with images)? thanks

sql-server ssas business-intelligence

8:47 PM

Screw it I posted an answer :)