1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/doc/notes/stv/20190430-Metadata-Investigation.txt	Tue Apr 30 18:22:13 2019 +0300
     1.3 @@ -0,0 +1,286 @@
     1.4 +
     1.5 +# <meta:header>
     1.6 +#   <meta:licence>
     1.7 +#     Copyright (c) 2018, ROE (http://www.roe.ac.uk/)
     1.8 +#
     1.9 +#     This information is free software: you can redistribute it and/or modify
    1.10 +#     it under the terms of the GNU General Public License as published by
    1.11 +#     the Free Software Foundation, either version 3 of the License, or
    1.12 +#     (at your option) any later version.
    1.13 +#
    1.14 +#     This information is distributed in the hope that it will be useful,
    1.15 +#     but WITHOUT ANY WARRANTY; without even the implied warranty of
    1.16 +#     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    1.17 +#     GNU General Public License for more details.
    1.18 +#
    1.19 +#     You should have received a copy of the GNU General Public License
    1.20 +#     along with this program.  If not, see <http://www.gnu.org/licenses/>.
    1.21 +#   </meta:licence>
    1.22 +# </meta:header>
    1.23 +#
    1.24 +#
    1.25 +
    1.26 +## Following is the counts for all tables in the Metadata Database of the Araybwyn VM, currently running the OSA TAP service Firethorn chain
    1.27 +## The VM & Services were setup 4 months ago, on December 12th 2018
    1.28 +##
    1.29 +##
    1.30 +##
    1.31 +## This shows the row counts that have accumulated over this 4 month period until today (30 April 2019)
    1.32 +##
    1.33 +
    1.34 +
    1.35 +
    1.36 +
    1.37 +## --------------------------------------------------------------------------------------------------------------------------------------------
    1.38 +
    1.39 +
    1.40 +
    1.41 +## Log into Araybwyn
    1.42 +
    1.43 +ssh Stevedore@Araybwyn 
    1.44 +
    1.45 +
    1.46 +
    1.47 +## Log into Postgres Metadata Database
    1.48 +docker exec -it stevedore_bethany_1 bash
    1.49 +
    1.50 +
    1.51 +## psql
    1.52 +
    1.53 +root@80da24dbaf6a:/# psql -host localhost --dbname postgres --username ${metauser:?}
    1.54 +
    1.55 +postgres=# \dt 
    1.56 +                                 List of relations
    1.57 + Schema |                   Name                    | Type  |        Owner         
    1.58 +--------+-------------------------------------------+-------+----------------------
    1.59 + public | ft020116adqlcolumnentity                  | table | liengooXien1mooP0tae
    1.60 + public | ft020116adqlresourceentity                | table | liengooXien1mooP0tae
    1.61 + public | ft020116adqlschemaentity                  | table | liengooXien1mooP0tae
    1.62 + public | ft020116adqltableentity                   | table | liengooXien1mooP0tae
    1.63 + public | ft020116authmethodentity                  | table | liengooXien1mooP0tae
    1.64 + public | ft020116bluequeryentity                   | table | liengooXien1mooP0tae
    1.65 + public | ft020116bluequeryentityjointobaseresource | table | liengooXien1mooP0tae
    1.66 + public | ft020116bluetaskentity                    | table | liengooXien1mooP0tae
    1.67 + public | ft020116bluetasklogentity                 | table | liengooXien1mooP0tae
    1.68 + public | ft020116bluetaskparam                     | table | liengooXien1mooP0tae
    1.69 + public | ft020116communityentity                   | table | liengooXien1mooP0tae
    1.70 + public | ft020116configproperty                    | table | liengooXien1mooP0tae
    1.71 + public | ft020116identityentity                    | table | liengooXien1mooP0tae
    1.72 + public | ft020116ivoacolumnentity                  | table | liengooXien1mooP0tae
    1.73 + public | ft020116ivoaresourceentity                | table | liengooXien1mooP0tae
    1.74 + public | ft020116ivoaschemaentity                  | table | liengooXien1mooP0tae
    1.75 + public | ft020116ivoatableentity                   | table | liengooXien1mooP0tae
    1.76 + public | ft020116jdbccolumnentity                  | table | liengooXien1mooP0tae
    1.77 + public | ft020116jdbcresourceentity                | table | liengooXien1mooP0tae
    1.78 + public | ft020116jdbcschemaentity                  | table | liengooXien1mooP0tae
    1.79 + public | ft020116jdbctableentity                   | table | liengooXien1mooP0tae
    1.80 + public | ft020116ogsadqpresourceentity             | table | liengooXien1mooP0tae
    1.81 + public | ft020116ogsaexecresourceentity            | table | liengooXien1mooP0tae
    1.82 + public | ft020116ogsaivoaresourceentity            | table | liengooXien1mooP0tae
    1.83 + public | ft020116ogsajdbcresourceentity            | table | liengooXien1mooP0tae
    1.84 + public | ft020116ogsaserviceentity                 | table | liengooXien1mooP0tae
    1.85 + public | ft020116operationentity                   | table | liengooXien1mooP0tae
    1.86 +(27 rows)
    1.87 +
    1.88 +
    1.89 +
    1.90 +postgres=# select count(*) from ft020116adqlcolumnentity;
    1.91 +  count  
    1.92 +---------
    1.93 + 4250859
    1.94 +(1 row)
    1.95 +
    1.96 +postgres=# select count(*) from ft020116adqlresourceentity;
    1.97 + count  
    1.98 +--------
    1.99 + 116657
   1.100 +(1 row)
   1.101 +
   1.102 +postgres=# select count(*) from ft020116adqlschemaentity;
   1.103 + count  
   1.104 +--------
   1.105 + 116679
   1.106 +(1 row)
   1.107 +
   1.108 +postgres=# select count(*) from ft020116adqltableentity;
   1.109 + count  
   1.110 +--------
   1.111 + 158317
   1.112 +(1 row)
   1.113 +
   1.114 +postgres=# select count(*) from ft020116authmethodentity;
   1.115 + count   
   1.116 +----------
   1.117 + 15943196
   1.118 +(1 row)
   1.119 +
   1.120 +postgres=# select count(*) from ft020116bluequeryentity;
   1.121 + count  
   1.122 +--------
   1.123 + 162540
   1.124 +(1 row)
   1.125 +
   1.126 +postgres=# select count(*) from ft020116bluequeryentityjointobaseresource;
   1.127 + count  
   1.128 +--------
   1.129 + 156885
   1.130 +(1 row)
   1.131 +
   1.132 +postgres=# select count(*) from ft020116bluetaskentity;
   1.133 + count  
   1.134 +--------
   1.135 + 162540
   1.136 +(1 row)
   1.137 +
   1.138 +postgres=# select count(*) from ft020116bluetasklogentity;
   1.139 + count  
   1.140 +--------
   1.141 + 521063
   1.142 +(1 row)
   1.143 +
   1.144 +postgres=# select count(*) from ft020116bluetaskparam;
   1.145 + count 
   1.146 +-------
   1.147 +  2540
   1.148 +(1 row)
   1.149 +
   1.150 +postgres=# select count(*) from ft020116communityentity;
   1.151 + count 
   1.152 +-------
   1.153 +     2
   1.154 +(1 row)
   1.155 +
   1.156 +postgres=# select count(*) from ft020116configproperty;
   1.157 + count 
   1.158 +-------
   1.159 +     0
   1.160 +(1 row)
   1.161 +
   1.162 +postgres=# select count(*) from ft020116identityentity;
   1.163 +^[[A  count   
   1.164 +----------
   1.165 + 15252163
   1.166 +(1 row)
   1.167 +
   1.168 +postgres=# select count(*) from ft020116ivoacolumnentity;
   1.169 + count 
   1.170 +-------
   1.171 +     0
   1.172 +(1 row)
   1.173 +
   1.174 +postgres=# select count(*) from ft020116ivoaresourceentity;
   1.175 + count 
   1.176 +-------
   1.177 +     0
   1.178 +(1 row)
   1.179 +
   1.180 +postgres=# select count(*) from ft020116ivoaschemaentity;
   1.181 + count 
   1.182 +-------
   1.183 +     0
   1.184 +(1 row)
   1.185 +
   1.186 +postgres=# select count(*) from ft020116ivoatableentity;
   1.187 + count 
   1.188 +-------
   1.189 +     0
   1.190 +(1 row)
   1.191 +
   1.192 +postgres=# select count(*) from ft020116jdbccolumnentity;
   1.193 +  count  
   1.194 +---------
   1.195 + 4251185
   1.196 +(1 row)
   1.197 +
   1.198 +postgres=# select count(*) from ft020116jdbcresourceentity;
   1.199 + count 
   1.200 +-------
   1.201 +    15
   1.202 +(1 row)
   1.203 +
   1.204 +postgres=# select count(*) from ft020116jdbcschemaentity;
   1.205 + count 
   1.206 +-------
   1.207 +  1206
   1.208 +(1 row)
   1.209 +
   1.210 +postgres=# select count(*) from ft020116jdbctableentity;
   1.211 + count  
   1.212 +--------
   1.213 + 158727
   1.214 +(1 row)
   1.215 +
   1.216 +postgres=# select count(*) from ft020116ogsadqpresourceentity;
   1.217 + count 
   1.218 +-------
   1.219 +     0
   1.220 +(1 row)
   1.221 +
   1.222 +postgres=# select count(*) from ft020116ogsaexecresourceentity;
   1.223 + count  
   1.224 +--------
   1.225 + 156650
   1.226 +(1 row)
   1.227 +
   1.228 +postgres=# select count(*) from ft020116ogsaivoaresourceentity;
   1.229 + count 
   1.230 +-------
   1.231 +     0
   1.232 +(1 row)
   1.233 +
   1.234 +postgres=# select count(*) from ft020116ogsajdbcresourceentity;
   1.235 + count 
   1.236 +-------
   1.237 +     4
   1.238 +(1 row)
   1.239 +
   1.240 +postgres=# select count(*) from ft020116ogsaserviceentity;
   1.241 + count 
   1.242 +-------
   1.243 +     1
   1.244 +(1 row)
   1.245 +
   1.246 +postgres=# select count(*) from ft020116operationentity;
   1.247 +
   1.248 +
   1.249 +
   1.250 +
   1.251 +## ------------------------------------------------------------------------------
   1.252 +
   1.253 +
   1.254 +##
   1.255 +##   Looks like the Largest Tables are:
   1.256 +##
   1.257 +##   ft020116adqlcolumnentity - 4 million rows
   1.258 +##   ft020116jdbccolumnentity - 4 million rows
   1.259 +##   ft020116authmethodentity - 15 million rows
   1.260 +##   ft020116identityentity - 15 million rows
   1.261 +
   1.262 +
   1.263 +## Disk space usage:
   1.264 +##
   1.265 +## We are currently sitting at 77% usage:
   1.266 +
   1.267 +[Stevedore@Araybwyn ~]$ df -h
   1.268 +
   1.269 +	Filesystem      Size  Used Avail Use% Mounted on
   1.270 +	devtmpfs        2.0G     0  2.0G   0% /dev
   1.271 +	tmpfs           2.0G     0  2.0G   0% /dev/shm
   1.272 +	tmpfs           2.0G  792K  2.0G   1% /run
   1.273 +	tmpfs           2.0G     0  2.0G   0% /sys/fs/cgroup
   1.274 +	/dev/vda3        31G   23G  7.0G  77% /
   1.275 +	tmpfs           2.0G   25M  2.0G   2% /tmp
   1.276 +	/dev/vda1       240M   89M  135M  40% /boot
   1.277 +	tmpfs           395M     0  395M   0% /run/user/1001
   1.278 +
   1.279 +
   1.280 +## Bethany, the Metadata Database is using up 12G
   1.281 +
   1.282 +## Estimated growth per month: 3.5 Gb
   1.283 +## Very rough estimation: Disk space will by 100% after about two months 
   1.284 +
   1.285 +
   1.286 +## -------------------------------------------------------------------------------
   1.287 +
   1.288 +
   1.289 +