firethorn
changeset 4209:68d026ea0895 2.1.28-tap-issues
Notes on Metadata Database Stats
author | Stelios <stv@roe.ac.uk> |
---|---|
date | Tue Apr 30 18:22:13 2019 +0300 (2019-04-30) |
parents | b511bd7b05d7 |
children | 4e7203353cf7 |
files | doc/notes/stv/20190430-Metadata-Investigation.txt |
line diff
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 +