Mailing List Archive: Delete deactivated stories and media (1.8.5): does this work on 2.x?

Delete deactivated stories and media (1.8.5): does this work on 2.x?

Jun 28, 2013, 10:16 AM

Post #1 of 14 (14287 views)

I was just hunting around in the wiki for an SQL query to remove old cruft from the database and found this:
https://github.com/bricoleurs/bricolage/wiki/Useful-SQL

… has anyone tried that on a recent version of Bricolage?

Specifically, the database in question is full of wire service stories, and each story has up to 50 versions stored. There are more than 10,000 of them, which is a sizeable percentage of the total number of stories in the system, and I can't help but think that permanently deleting them, along with their version, would reduce the size of the database and possibly improve performance.

Thoughts on the above and how to achieve both the deletion of the stories, and their version, and so on? Does anyone know if there's a "cascade on delete" that's going to run and handle all of that if I run the above SQL?

Many thanks in advance,

Phillip.

--
Phillip Smith
http://phillipadsmith.com

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

ps at phillipadsmith

Jun 28, 2013, 10:17 AM

Post #2 of 14 (14117 views)

Permalink

Sorry, here's the correct link:
https://github.com/bricoleurs/bricolage/wiki/Useful-SQL#delete-deactivated-stories-and-media-185

On 2013-06-28, at 12:16 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

>
> I was just hunting around in the wiki for an SQL query to remove old cruft from the database and found this:
> https://github.com/bricoleurs/bricolage/wiki/Useful-SQL
>
> … has anyone tried that on a recent version of Bricolage?
>
> Specifically, the database in question is full of wire service stories, and each story has up to 50 versions stored. There are more than 10,000 of them, which is a sizeable percentage of the total number of stories in the system, and I can't help but think that permanently deleting them, along with their version, would reduce the size of the database and possibly improve performance.
>
> Thoughts on the above and how to achieve both the deletion of the stories, and their version, and so on? Does anyone know if there's a "cascade on delete" that's going to run and handle all of that if I run the above SQL?
>
> Many thanks in advance,
>
> Phillip.
>
> --
> Phillip Smith
> http://phillipadsmith.com

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

david at kineticode

Jun 30, 2013, 1:12 AM

Post #3 of 14 (14122 views)

Permalink

On Jun 28, 2013, at 7:16 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

> I was just hunting around in the wiki for an SQL query to remove old cruft from the database and found this:
> https://github.com/bricoleurs/bricolage/wiki/Useful-SQL
>
> … has anyone tried that on a recent version of Bricolage?
>
> Specifically, the database in question is full of wire service stories, and each story has up to 50 versions stored. There are more than 10,000 of them, which is a sizeable percentage of the total number of stories in the system, and I can't help but think that permanently deleting them, along with their version, would reduce the size of the database and possibly improve performance.

Only if you have a rather underpowered server. Or run PostgreSQL on EC2.

> Thoughts on the above and how to achieve both the deletion of the stories, and their version, and so on? Does anyone know if there's a "cascade on delete" that's going to run and handle all of that if I run the above SQL?

There is a cascade on delete, yes. For example:

https://github.com/bricoleurs/bricolage/blob/master/sql/Pg/Bric/Biz/Asset/Business/Story.con#L46

But I do suggest trying it in a cloned Bricolage instance, first, or at least a dumped and reloaded database.

Best,

David

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

ps at phillipadsmith

Jun 30, 2013, 3:13 PM

Post #4 of 14 (14104 views)

Permalink

On 2013-06-30, at 3:12 AM, "David E. Wheeler" <david@kineticode.com> wrote:

> On Jun 28, 2013, at 7:16 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> I was just hunting around in the wiki for an SQL query to remove old cruft from the database and found this:
>> https://github.com/bricoleurs/bricolage/wiki/Useful-SQL
>>
>> … has anyone tried that on a recent version of Bricolage?
>>
>> Specifically, the database in question is full of wire service stories, and each story has up to 50 versions stored. There are more than 10,000 of them, which is a sizeable percentage of the total number of stories in the system, and I can't help but think that permanently deleting them, along with their version, would reduce the size of the database and possibly improve performance.
>
> Only if you have a rather underpowered server. Or run PostgreSQL on EC2.

Neither. It's on a stand-alone server with a lot of resources. Only other thing on the server is Bricolage.

So you think it's not worth it?

--
Phillip Smith
http://phillipadsmith.com
http://twitter.com/phillipadsmith
http://linkedin.com/in/phillipadsmith

Oaxaca: (011 52 1) 951 154 4620
Toronto: (647) 361-8248
Toll-free: (888) 418-1868

If your email inbox is out of control, check out http://sanebox.com/t/s0q7m

Save our in-boxes! http://emailcharter.org

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

david at kineticode

Jul 1, 2013, 4:54 AM

Post #5 of 14 (14106 views)

Permalink

On Jul 1, 2013, at 12:13 AM, Phillip Smith <ps@phillipadsmith.com> wrote:

> Neither. It's on a stand-alone server with a lot of resources. Only other thing on the server is Bricolage.
>
> So you think it's not worth it?

Are you experiencing performance problems? If not, is PostgreSQL properly tuned for the hardware?

You can find out how big your database actually is by running

SELECT pg_size_pretty(pg_database_size(current_database()));

If the size is less than the RAM you've made available to Postgres, or not a whole lot larger (keeping in mind that, unless those stories are all regularly accessed, most of what you need will be cached in RAM), then it is not worth it.

Best,

David

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

ps at phillipadsmith

Jul 1, 2013, 8:31 AM

Post #6 of 14 (14117 views)

Permalink

On 2013-07-01, at 6:54 AM, "David E. Wheeler" <david@kineticode.com> wrote:

> On Jul 1, 2013, at 12:13 AM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> Neither. It's on a stand-alone server with a lot of resources. Only other thing on the server is Bricolage.
>>
>> So you think it's not worth it?
>
> Are you experiencing performance problems? If not, is PostgreSQL properly tuned for the hardware?
>
> You can find out how big your database actually is by running
>
> SELECT pg_size_pretty(pg_database_size(current_database()));
>
> If the size is less than the RAM you've made available to Postgres, or not a whole lot larger (keeping in mind that, unless those stories are all regularly accessed, most of what you need will be cached in RAM), then it is not worth it.

bric ~/bin $ psql -U bric -c 'SELECT pg_size_pretty(pg_database_size(current_database()));'
pg_size_pretty
----------------
51 GB
(1 row)

bric ~/bin $ free
total used free shared buffers cached
Mem: 8153732 8051436 102296 0 1786852 4316488
-/+ buffers/cache: 1948096 6205636
Swap: 0 0 0

--
Phillip Smith
http://phillipadsmith.com

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

david at kineticode

Jul 1, 2013, 1:38 PM

Post #7 of 14 (14106 views)

Permalink

On Jul 1, 2013, at 5:31 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

> bric ~/bin $ psql -U bric -c 'SELECT pg_size_pretty(pg_database_size(current_database()));'
> pg_size_pretty
> ----------------
> 51 GB
> (1 row)

Not bad.

> bric ~/bin $ free
> total used free shared buffers cached
> Mem: 8153732 8051436 102296 0 1786852 4316488
> -/+ buffers/cache: 1948096 6205636
> Swap: 0 0 0

Is that 8G of RAM? What do you have effective_cache_size set to in postgresql.conf? You can just run

show effective_cache_size;

In the database to find out.

David

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

ps at phillipadsmith

Jul 1, 2013, 3:03 PM

Post #8 of 14 (14115 views)

Permalink

On 2013-07-01, at 3:38 PM, David E. Wheeler <david@kineticode.com> wrote:

> On Jul 1, 2013, at 5:31 PM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> bric ~/bin $ psql -U bric -c 'SELECT pg_size_pretty(pg_database_size(current_database()));'
>> pg_size_pretty
>> ----------------
>> 51 GB
>> (1 row)
>
> Not bad.
>
>> bric ~/bin $ free
>> total used free shared buffers cached
>> Mem: 8153732 8051436 102296 0 1786852 4316488
>> -/+ buffers/cache: 1948096 6205636
>> Swap: 0 0 0
>
> Is that 8G of RAM? What do you have effective_cache_size set to in postgresql.conf? You can just run
>
> show effective_cache_size;
>
> In the database to find out.

bric ~/bin $ psql -U bric -c 'show effective_cache_size;'
effective_cache_size
----------------------
128MB
(1 row)

--
Phillip Smith
http://phillipadsmith.com
http://twitter.com/phillipadsmith
http://linkedin.com/in/phillipadsmith

Oaxaca: (011 52 1) 951 154 4620
Toronto: (647) 361-8248
Toll-free: (888) 418-1868

If your email inbox is out of control, check out http://sanebox.com/t/s0q7m

Save our in-boxes! http://emailcharter.org

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

david at kineticode

Jul 1, 2013, 3:55 PM

Post #9 of 14 (14099 views)

Permalink

On Jul 2, 2013, at 12:03 AM, Phillip Smith <ps@phillipadsmith.com> wrote:

> bric ~/bin $ psql -U bric -c 'show effective_cache_size;'
> effective_cache_size
> ----------------------
> 128MB
> (1 row)

WTF? Who tuned this database server? Is there anything else running on that box?

I suggest you review this document:

http://bricolagecms.org/docs/current/api/Bric::DBA#Tuning

It’s a bit old, but still mostly relevant. For example, if you have 8G of RAM and nothing else on that box, you are currently using no more than 200MB of RAM! (Depending on the other memory settings). You want to give it all you can, at least 6.5G for effective_cache_size. Assume you have 7.5G of available memory and thread through that document to figure out what you should tweak.

You will be shocked at how much better Bricolage will run when you’re through.

Best,

David

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

ps at phillipadsmith

Jul 1, 2013, 4:05 PM

Post #10 of 14 (14098 views)

Permalink

On 2013-07-01, at 5:55 PM, "David E. Wheeler" <david@kineticode.com> wrote:
> On Jul 2, 2013, at 12:03 AM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> bric ~/bin $ psql -U bric -c 'show effective_cache_size;'
>> effective_cache_size
>> ----------------------
>> 128MB
>> (1 row)
>
> WTF? Who tuned this database server? Is there anything else running on that box?
>
> I suggest you review this document:
>
> http://bricolagecms.org/docs/current/api/Bric::DBA#Tuning
>
> It’s a bit old, but still mostly relevant. For example, if you have 8G of RAM and nothing else on that box, you are currently using no more than 200MB of RAM! (Depending on the other memory settings). You want to give it all you can, at least 6.5G for effective_cache_size. Assume you have 7.5G of available memory and thread through that document to figure out what you should tweak.
>
> You will be shocked at how much better Bricolage will run when you’re through.

Reviewing… super helpful. Many thanks, as always, David. :)

--
Phillip Smith
http://phillipadsmith.com

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

ps at phillipadsmith

Jul 1, 2013, 4:54 PM

Post #11 of 14 (14101 views)

Permalink

On 2013-07-01, at 6:05 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

>
> On 2013-07-01, at 5:55 PM, "David E. Wheeler" <david@kineticode.com> wrote:
>> On Jul 2, 2013, at 12:03 AM, Phillip Smith <ps@phillipadsmith.com> wrote:
>>
>>> bric ~/bin $ psql -U bric -c 'show effective_cache_size;'
>>> effective_cache_size
>>> ----------------------
>>> 128MB
>>> (1 row)
>>
>> WTF? Who tuned this database server? Is there anything else running on that box?
>>
>> I suggest you review this document:
>>
>> http://bricolagecms.org/docs/current/api/Bric::DBA#Tuning
>>
>> It’s a bit old, but still mostly relevant. For example, if you have 8G of RAM and nothing else on that box, you are currently using no more than 200MB of RAM! (Depending on the other memory settings). You want to give it all you can, at least 6.5G for effective_cache_size. Assume you have 7.5G of available memory and thread through that document to figure out what you should tweak.
>>
>> You will be shocked at how much better Bricolage will run when you’re through.
>
> Reviewing… super helpful. Many thanks, as always, David. :)

Settings tweaked. effective_cache_size set to 4GB. This box is almost entirely Bricolage, but there are one or two other things running. Set the other configuration options per the DBA documentation. Fingers crossed.

--
Phillip Smith
http://phillipadsmith.com

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

david at kineticode

Jul 2, 2013, 5:24 AM

Post #12 of 14 (14095 views)

Permalink

On Jul 2, 2013, at 1:54 AM, Phillip Smith <ps@phillipadsmith.com> wrote:

> Settings tweaked. effective_cache_size set to 4GB. This box is almost entirely Bricolage, but there are one or two other things running. Set the other configuration options per the DBA documentation. Fingers crossed.

And you restarted, I trust?

David

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

ps at phillipadsmith

Jul 8, 2013, 9:47 AM

Post #13 of 14 (14078 views)

Permalink

On 2013-07-02, at 7:24 AM, David E. Wheeler <david@kineticode.com> wrote:

> On Jul 2, 2013, at 1:54 AM, Phillip Smith <ps@phillipadsmith.com> wrote:
>
>> Settings tweaked. effective_cache_size set to 4GB. This box is almost entirely Bricolage, but there are one or two other things running. Set the other configuration options per the DBA documentation. Fingers crossed.
>
> And you restarted, I trust?

Yes, indeed, running better than every before. :)

--
Phillip Smith
http://phillipadsmith.com

Re: Delete deactivated stories and media (1.8.5): does this work on 2.x? [ In reply to ]

david at kineticode

Jul 9, 2013, 1:02 AM

Post #14 of 14 (14073 views)

Permalink

On Jul 8, 2013, at 6:47 PM, Phillip Smith <ps@phillipadsmith.com> wrote:

>> And you restarted, I trust?
>
> Yes, indeed, running better than every before. :)

Excellent.

Remember people, out-of-the box, PostgreSQL is properly tuned for a four-year-old netbook, at best. Always peruse Bric::DBA, and tune it, at the very least.

Best,

David