# PostgreSQL com E/S descontrolada
**URL:** https://meta.discourse.org/t/postgresql-runaway-io/91516
**Category:** Self-hosting
**Created:** [3 Julho , 2018 21:59 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516 "2018-07-03T21:59:01Z")
**Posts on this page:** 18
**Page:** 2
### Author:  [@pturmel](https://meta.discourse.org/u/pturmel)
#### Post date: [6 Julho , 2018 15:15 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/21 "2018-07-06T15:15:27Z")
> [@JanJoost](#):
>
> Everything seems to be about 1/7th of the speed you get. Perhaps indeed I should consider switching from old-school HDD to SSDs.
I’ve seen this before (on the linux-raid mailing list) when legacy partition tables and contents are copied onto modern 4k sector hard drives. (Default legacy partitions are not 4k -aligned.) The 512-byte sector support will generate read-modify-write cycles in the drive firmware as needed, and random access workloads on misaligned blocks will need lots of R-M-W.
On linux-raid, these discoveries are usually associated with raid array rebuild failures and catastrophic data loss. The poor performance is a hint that all is not alright with your storage stack.
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [6 Julho , 2018 15:36 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/22 "2018-07-06T15:36:32Z")
> [@pturmel](#):
>
> The poor performance is a hint that all is not alright with your storage stack.
You’re right (although in a slightly different way).
First of all, I am an idiot. Second of all, thank you all for the hints and suggestions.
Third and most importantly: I have a new-found hate for raid controllers which disable disk caches.
Enabled the disk caches, got the proper speeds.
Let’s see how big the real-world gains are though.
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [23 Julho , 2018 11:47 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/23 "2018-07-23T11:47:36Z")
Hi,
Sorry to open up this topic again but I have a question on the size of post\_timings.
> [@RGJ](#):
>
> Fact is that post\_timings is bigger than the internal memory of the server (20 GB vs 16 GB) and even limiting to the first x posts would still result in the database failing to pull the entire table into memory.
Out of curiousity I had a look at the database to see how big that table really is, so I checked it with:
`select count(*) from post_timings; `
and that came back with 25 million records, 25.101.376, to be specific.
The total number of posts in the system is 99.260.
Does it make sense that post\_timings contains roughly 250 entries per post? Or is there something that went (a bit) wrong somewhere and is there stuff I can do to clean up?
Thanks!
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [23 Julho , 2018 11:57 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/24 "2018-07-23T11:57:53Z")
> [@JanJoost](#):
>
> Does it make sense that post\_timings contains roughly 250 entries per post?
`post_timings` will contain 1 entry for every user that reads a post. So if you have, on average, 250 users reading each post, then 25 million rows sounds about right.
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [30 Julho , 2018 20:39 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/25 "2018-07-30T20:39:47Z")
Hi! Sorry to trouble you again over this but I have -yet again- a related question.
Would it perhaps be possible to calculate the relevant value for a specific topic once the topic is being archived (and then remove all those entries from the post\_timings table?).
Some context:
I’ve got a forum here that’s been up for 2.5 months now, and we’re over 100K posts already, with close to 1400 (very active!) users.
Some of these topics tend to be revisited by very many of those users, so I’m afraid the system will eventually come to a grinding halt if i’m not careful.
Some data: 25M records in the post\_timings table. The top-10 topics (out of little over 400 topics) are good for 16.7M entries.
What we’re doing now is close topics when they reach 5.000 posts (the most active topic reaches this after around 10 days). And then we open a new topic. As you can imagine, these topics are used for very active social conversations and sometimes more resemble a chat than a forum.
When we close a topic the users move on to the next one (and hardly ever come back to the previous one - it’s basically an archive from then on). So we archive the old topic. I would happily accept it if we were to make one final calculation of the average reading time of the archived topic (so that the other functionality doesn’t break), then remove all entries for the archived topic bar one (which contains the final calculated value) and thereby significantly reduce the size of the table.
We could even do the same thing but per user if needed.
In fact: If the most important reasons for this table are to be able to calculate how much time a specific user has spent reading then perhaps we could make it a configurable option? I know that funnily enough our users are not really interested in that (in fact, it’s a bit of a shocker for some that they spend so much time on the forum!)
Would this work?
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [30 Julho , 2018 21:05 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/26 "2018-07-30T21:05:07Z")
Are you actually seeing any issues caused by the number of rows in your `post_timings` table? Pruning the table **might** be possible, but would likely have unintended side effects.
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [30 Julho , 2018 21:11 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/27 "2018-07-30T21:11:01Z")
> [@david](#):
>
> Are you actually seeing any issues caused by the number of rows in your `post_timings` table? Pruning the table **might** be possible, but would likely have unintended side effects.
Well, I am still seeing issues with the CalculateAvgTime query which doesn’t seem to want to finish. I first thought it was related to reduced performance of my underlying IO storage, but indeed - I moved it to an SSD-based test environment and it still seems to not want to finish.
I have to admit, I’m just trying to see what I can do to improve life here, so any and all advice is welcome. Then again: if this is a case of the ‘sorry, can’t fix’ then I’ll accept that as well.
Btw: for the administrative side of things, would it be possible to setup a read-only slave which does the bulk of the processing for all maintenance bits? Because I could then move that to its own working environment, and not have it impact performance of the main forum.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [30 Julho , 2018 23:48 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/28 "2018-07-30T23:48:46Z")
You _absolutely_ need to prevent megatopics from forming. Any topic approaching 10k replies is an accident waiting to happen. The more you have of these, the worse it will get, and the more replies, the worse the overall effect.
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [31 Julho , 2018 03:36 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/29 "2018-07-31T03:36:12Z")
We indeed learned from this and will close topics when they grow to 5000 posts.
Question is: what can I do with the one archived 17.0000-post topic?
Can I break it into smaller pieces? Does it help when I delete the associated entries from then post\_timings table?
Or should I just throw away that topic altogether?
Edit: I think the size of the post\_timings table now also starts to affect the BadgeGrant query which seems to be not too happy.
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [31 Julho , 2018 04:58 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/30 "2018-07-31T04:58:53Z")
Update: I’ve taken the plunge and moved a lot of entries into new topics. The biggest topic is now on longer 17K posts, but 7K.
Still, no dice.
The CalculateAvgTime and BadgeGrant now both seem to keep on running forever.
a select count(\*) from post\_timings; tells me that post\_timings is now 29 million records large, and I think that has to do with at least some of the problems.
From the BadgeGrant query I ran the innermost bit by hand:
```
SELECT pt.user_id
FROM post_timings pt
JOIN badge_posts b ON b.post_number = pt.post_number AND
b.topic_id = pt.topic_id
JOIN topics t ON t.id = pt.topic_id
LEFT JOIN user_badges ub ON ub.badge_id = 17 AND ub.user_id = pt.user_id
WHERE ub.id IS NULL AND t.posts_count > 100
GROUP BY pt.user_id, pt.topic_id, t.posts_count
HAVING count(*) >= t.posts_count
```
and indeed I get the same results - taking forever. And with forever I mean I let it run for at least 20 minutes before I interrupted this query.
My biggest concern is that this will only worsen due to the fact that this is a rather active forum and we’ve only been underway for 2.5 months.
Is there any suggestion you have that could alleviate this situation?
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [31 Julho , 2018 05:14 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/31 "2018-07-31T05:14:27Z")
It is inherently extremely expensive to figure out if a user read EVERY single post on a topic across all topics across all users.
If you don’t have the hardware to handle that I would recommend just disabling the reader badge.
I am on the fence big time on the value of “calculateavgtime” I am not convinced we even need this long term
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [31 Julho , 2018 05:18 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/32 "2018-07-31T05:18:12Z")
Hi Sam,
Thanks for the suggestion - I’ve disabled the reader badge, will do some more testing to see if indeed BadgeGrant now works again for the other badges.
If there’s anything else I can do, or test or so, please let me know 🙂 . I don’t just want to take back, if there’s things I can do that may help then I’m more than happy to do so.
---
### Author:  [@JanJoost](https://meta.discourse.org/u/JanJoost)
#### Post date: [31 Julho , 2018 05:33 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/33 "2018-07-31T05:33:54Z")
Just a quick update: BadgeGrant works again, so THANK YOU!
The Jobs::Weekly still triggers the calculation of avg\_time based on stuff from post\_timings, so that’s not very happy yet, but at least something’s started working again 😉
I do wonder what else there is in the Weekly job, because if I kill the query that keeps on running forever, then the rest of that job probably will not be executed either.
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [31 Julho , 2018 18:22 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/34 "2018-07-31T18:22:48Z")
Denormalizing & pruning the post\_timings table also came up during GDPR work, and I wasn’t really able to come up with a coherent plan of action there either. The (user, post, duration) really does get used individually and without a solid time cutoff, so you can’t just truncate the table and expect that you’re only purging old entries.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [31 Julho , 2018 22:50 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/35 "2018-07-31T22:50:01Z")
Is there any other low hanging fruit left out there as far as removing IP address from tables where we were needlessly collecting it?
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [2 Março , 2021 06:56 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/36 "2021-03-02T06:56:17Z")
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [3 Março , 2021 00:06 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/37 "2021-03-03T00:06:38Z")
Olá @JanJoost… curioso para saber onde você está em relação a essa questão muitos anos depois?
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [17 Março , 2021 08:00 UTC](https://meta.discourse.org/t/postgresql-runaway-io/91516/38 "2021-03-17T08:00:39Z")
Este tópico foi automaticamente fechado após 14 dias. Novas respostas não são mais permitidas.
[Previous page](https://meta.discourse.org/t/postgresql-runaway-io/91516.md?page=1)