So we've been going on this new server for a few weeks now and its
crunching away at many many repos of source-code.
However, I expected this Fossology DB size on the main page
http://localhost/repo/ -> Admin -> Dashboard to start displaying some
amount of size close to the fossology-database size but for some
reason it's still showing up as Zero 0.
Our fossology DB and actual disk space used is currently at:
[r...@san-psip ~]# du -sh /var/lib/pgsql/data/
6.5G /var/lib/pgsql/data/
which is similar to the database size of fossology:
postgres=# SELECT pg_size_pretty(pg_database_size('fossology'));
pg_size_pretty
----------------
6333 MB
(1 row)
But when using your query to display Fossology DB size on Dashboard,
its still showing zero, or nothing unlike the last time I used this
query:
postgres=# SELECT SUM(relpages) AS val FROM pg_class INNER JOIN
pg_stat_all_tables ON pg_stat_all_tables.relname = pg_class.relname
WHERE schemaname='public';
val
-----
(1 row)
Last time I ran this it displayed: val = 0.
So in theory the query and displayed size is correct, but this seems
more like an issue or something not being right, (possibly a bug?). I
don't know but I would like to attempt to correct this somehow.
I would hope to see something close to the DB size when going to the
Dashboard page. Others engineers have asked me about this and I really
don't know what to tell them.
Any suggestions, recommendations guys?
-Gibran
On Mon, Mar 1, 2010 at 9:32 AM, Furosh One <fur...@gmail.com> wrote:
> Okay thanks, now I understand a little more of why it's displaying 0
> size on the localhost/repo/ page.
>
> My postgresql.conf currently has those commented out, which seems to
> use a default location:
> #data_directory = 'ConfigDir' # use data in another directory
> #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
> #ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
>
> I found the pgsql/data directory and it does show 2.8 GB size:
> [r...@san-psip ~]# du -sh /var/lib/pgsql/data/
> 2.8G /var/lib/pgsql/data/
>
> Perhaps I should give it sometime to generate sufficient data to be
> displayed with the query being used.
> Also, it's fairly new but we already transferred a large amount of
> data and has scanned 3 large source code repositories already some of
> which are large git repos (over 40 gb of stuff)...
>
> -Gibran
>
> On Mon, Mar 1, 2010 at 9:14 AM, Gobeille, Robert <bob.gobei...@hp.com> wrote:
>> I think the difference is that pg_database_size is giving you the total disk
>> space used as a result of the database you listed. Whereas the query we use
>> is the sum of the actual data space used by each table.
>>
>> So since the tables in a fresh install are mostly empty, you have approx 0
>> pages of data.
>> But postgres is going to create files that it needs to handle transaction
>> processing, tables, etc. so the actual disk space used is much larger. It
>> surprises me a lot that it comes up with 2.5 GB.
>>
>> If you want to check that, look in your postgresql.conf file (may be in
>> /etc/postgresql/8.1/main/)
>> Look for the variable "data_directory". That will tell you the file path
>> that postgresql is using to store all of its databases. Do a du -sh on that
>> directory to see how much disk space it's taking.
>>
>> Bob
>>
>>
>> On Mar 1, 2010, at 9:31 AM, Furosh One wrote:
>>
>>> Thanks for the response Bob. Well, I'm definately not much of a DB
>>> person, so I had to google this and look online but this is what I'm
>>> using based on a couple of results from my search. Since I'm not much
>>> of a DB person, I usually write notes about what I did for future.
>>> Here are my notes:
>>>
>>> Login:
>>> sudo -u postgres psql
>>>
>>> Display DBs:
>>> ============
>>> [r...@san-psip ~]# sudo -u postgres psql
>>> could not change directory to "/root"
>>> Welcome to psql 8.1.18, the PostgreSQL interactive terminal.
>>>
>>> Type: \copyright for distribution terms
>>> \h for help with SQL commands
>>> \? for help with psql commands
>>> \g or terminate with semicolon to execute query
>>> \q to quit
>>>
>>>
>>> Listings of DBs:
>>> ================
>>> postgres=# \l
>>> List of databases
>>> Name | Owner | Encoding
>>> -----------+----------+-----------
>>> fossology | fossy | SQL_ASCII
>>> postgres | postgres | UTF8
>>> template0 | postgres | UTF8
>>> template1 | postgres | UTF8
>>> (4 rows)
>>>
>>> View DB size for fossology:
>>> ===========================
>>> postgres=# SELECT pg_database_size('fossology');
>>> pg_database_size
>>> ------------------
>>> 2710417276
>>> (1 row)
>>>
>>> View DB size for fossology (human-readable):
>>> ============================================
>>> postgres=# SELECT pg_size_pretty(pg_database_size('fossology'));
>>> pg_size_pretty
>>> ----------------
>>> 2585 MB
>>> (1 row)
>>>
>>> However, you're right in what you use is displaying 0 when I run the query:
>>> =====================================================
>>> r...@san-psip ~]# sudo -u postgres psql -d fossology
>>> could not change directory to "/root"
>>> Welcome to psql 8.1.18, the PostgreSQL interactive terminal.
>>>
>>> Type: \copyright for distribution terms
>>> \h for help with SQL commands
>>> \? for help with psql commands
>>> \g or terminate with semicolon to execute query
>>> \q to quit
>>>
>>> fossology=# SELECT SUM(relpages) AS val FROM pg_class INNER JOIN
>>> pg_stat_all_tables ON pg_stat_all_tables.relname = pg_class.relname
>>> WHERE schemaname='public';
>>> val
>>> -----
>>> 0
>>> (1 row)
>>>
>>> Do you know of a way I can fix/correct this because although the query
>>> fossology is using I'm guessing my DB is now zero.
>>>
>>> Any suggestions?
>>>
>>> -Regards,
>>> Gibran
>>>
>>> On Mon, Mar 1, 2010 at 6:40 AM, Gobeille, Robert <bob.gobei...@hp.com>
>>> wrote:
>>>> Hi Gibran,
>>>> How did you select the size of the fossology db?
>>>> We use:
>>>> SELECT SUM(relpages) AS val FROM pg_class INNER JOIN pg_stat_all_tables ON
>>>> pg_stat_all_tables.relname = pg_class.relname WHERE schemaname='public'
>>>>
>>>> My machine is running postgresql 8.3.9. I haven't verified this on a
>>>> higher rev.
>>>>
>>>> Bob
>>>>
>>
>>
>
_______________________________________________
fossology mailing list
fossology@fossology.org
http://fossology.org/mailman/listinfo/fossology
