Re: [SPAM] Re: WAL directory size calculation

Il 29/07/2016 11:44,
      FarjadFarid(ChkNet) ha scritto:

    <blockquote
      cite="mid:002101d1e97d$c19a6540$44cf2fc0$@checknetworks.com"
      type="cite">

      <meta name="Generator" content="Microsoft Word 15 (filtered
        medium)">
      <!--
/* Font Definitions */
@font-face
    {font-family:"Cambria Math";
    panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
    {font-family:Calibri;
    panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
    {font-family:Consolas;
    panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
    {margin:0cm;
    margin-bottom:.0001pt;
    font-size:12.0pt;
    font-family:"Times New Roman",serif;
    color:black;}
a:link, span.MsoHyperlink
    {mso-style-priority:99;
    color:blue;
    text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
    {mso-style-priority:99;
    color:purple;
    text-decoration:underline;}
p
    {mso-style-priority:99;
    mso-margin-top-alt:auto;
    margin-right:0cm;
    mso-margin-bottom-alt:auto;
    margin-left:0cm;
    font-size:12.0pt;
    font-family:"Times New Roman",serif;
    color:black;}
pre
    {mso-style-priority:99;
    mso-style-link:"HTML Preformatted Char";
    margin:0cm;
    margin-bottom:.0001pt;
    font-size:10.0pt;
    font-family:"Courier New";
    color:black;}
p.msonormal0, li.msonormal0, div.msonormal0
    {mso-style-name:msonormal;
    mso-margin-top-alt:auto;
    margin-right:0cm;
    mso-margin-bottom-alt:auto;
    margin-left:0cm;
    font-size:12.0pt;
    font-family:"Times New Roman",serif;
    color:black;}
span.moz-txt-tag
    {mso-style-name:moz-txt-tag;}
span.HTMLPreformattedChar
    {mso-style-name:"HTML Preformatted Char";
    mso-style-priority:99;
    mso-style-link:"HTML Preformatted";
    font-family:"Consolas",serif;
    color:black;}
span.EmailStyle22
    {mso-style-type:personal-reply;
    font-family:"Calibri",sans-serif;
    color:windowtext;}
.MsoChpDefault
    {mso-style-type:export-only;
    font-size:10.0pt;}
@page WordSection1
    {size:612.0pt 792.0pt;
    margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
    {page:WordSection1;}
-->






        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> 
        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">The
            question to ask is what benefit would you gain by saving
            BLOB object on a database than on say a flat file server or
            url on an ftp server? Specially larger ones.


    Privacy. Blobs are stored encrypted, since they are health-related
    images or documents.
    You should be right if all of this data would be resident only on
    our server (that can only be accessed by application), but every
    user has a small PG cluster in his PC with his patients data and
    images that replicates continuously with our server.
    Our application runs on Windows. To get into patient data from
    another user (say, someone that stole the computer) is a bit tricky,
    because you have to know how to exclude authentication in postgres
    and even after this, you have to know where to search and what to
    search and sometines what is the meaning on the encodings.
    Imagine if we have a folder containing all images.... double click
    and open...

    Another point is a bit of self-defense. Our users are far to be
    smart computer users, and in the past we had some cases in which
    someone, trying to clean up a filled-up disk, deleted a directory
    under his Paradox database (!!!) and then asked us why the app was
    not loading anymore....
    <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> 
    <blockquote
      cite="mid:002101d1e97d$c19a6540$44cf2fc0$@checknetworks.com"
      type="cite">

        <span

style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">BLOB’s
            cause a lot problem for all DBs. Not unless the DB engine
            can understand their structure and process them. It is not
            worth the effort.
        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">It
            can hit the DB performance in Indexing, backups, migrations
            and load balancing.


    Regarding backups I disagree. Files related to database must be
    consistent to the database itself, so backup must be done saving
    both database and images. AFAIK there's not a big difference in
    backing up image files versus BLOBS in a database.
    I agree about load balancing, but only in case of a bulk load of
    several megabytes. (our actual server got an overload 2 months ago
    when a client we were activating sent a transaction with the
    insertion of 50 blobs sizing about 300 megabytes)
    <blockquote
      cite="mid:002101d1e97d$c19a6540$44cf2fc0$@checknetworks.com"
      type="cite">

        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> 
        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> 
        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US">Hope
            this helps.
        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> 
        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> 
        <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext;mso-fareast-language:EN-US"> 

          <div style="border:none;border-top:solid #E1E1E1
            1.0pt;padding:3.0pt 0cm 0cm 0cm">
            <span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
                  lang="EN-US">From:<span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
                lang="EN-US"> pgsql-general-owner@postgresql.org
                [mailto:pgsql-general-owner@postgresql.org] On
                  Behalf Of Moreno Andreo
                Sent: 29 July 2016 10:19
                To: pgsql-general@postgresql.org
                Subject: Re: [SPAM] Re: [GENERAL] WAL directory
                size calculation


         

          Il 29/07/2016 10:43, John R Pierce ha
            scritto:



             




                Aside of this, I'm having 350 DBs
                  that sum up a bit more than 1 TB, and plan
                  to use wal_level=archive because I plan to have a
                  backup server with barman.



           
          With that many databases with that so many objects

        350 DBs with about 130 tables and a bunch
          of sequences each, for the sake of precision.
          With extensive use of BLOBs.




          and undoubtable client connections,

        Yes, that's another big problem... we run
          normally between 500 and 700 concurrent connections... I had
          to set max_connections=1000, the whole thing grew up faster
          than we were prepared for...




          I'd want to spread that across a cluster of smaller
            servers.

        That will be step 2... while migration is
          running (and will run for some months, we have to plan
          migration with users) I'll test putting another one or two
          machines in cluster, make some test cases, and when ready,
          databases will be migrated on other machines, too.
          I posted a question about this some months ago, and I was told
          that one solution would be to set the servers to be master on
          some databases and slave on others, so we can have a better
          load balancing (instead of having all writes on the sole
          master, we split among all masters depending on which database
          is getting the write command, especially when having to write
          BLOBs that can be some megabytes in size).
          I don't know to achieve this, but I will find a way somewhere.




          just sayin...

        ideas are always precious and welcome.



           
          --
          john r pierce, recycling bits in santa cruz