2ge
commented
5 years ago same problem here, running on FreeBSD 11.2-STABLE
postgresqltuner.pl version 1.0.0
Checking if OS commands is available on db1...
[BAD] Unable to run OS command, report will be incomplete
Connecting to db1:5432 database osdb3 with user osdb3...
[OK] User used for report have super rights
===== OS information =====
[UNKNOWN] Unable to run OS commands on db1. For now you will not have OS information
===== General instance informations =====
----- Version -----
[OK] You are using last 10.5
----- Uptime -----
[INFO] Service uptime : 98d 11m 32s
----- Databases -----
[INFO] Database count (except templates): 3
[INFO] Database list (except templates): postgres osdb3 osdb3_development
----- Extensions -----
[INFO] Number of activated extensions : 2
[INFO] Activated extensions : plpgsql hstore
[WARN] Extensions pg_stat_statements is disabled
----- Users -----
[OK] No user account will expire in less than 7 days
[OK] No user with password=username
[OK] Password encryption is enabled
----- Connection information -----
[INFO] max_connections: 1000
[INFO] current used connections: 164 (16.40%)
[INFO] 3 are reserved for super user (0.30%)
[INFO] Average connection age : 3d 01h 08m 48s
----- Memory usage -----
[INFO] configured work_mem: 4.00 MB
[INFO] Using an average ratio of work_mem buffers by connection of 150% (use --wmp to change it)
[INFO] total work_mem (per connection): 6.00 MB
[INFO] shared_buffers: 8.00 GB
[INFO] Track activity reserved size : 1011.00 KB
[WARN] maintenance_work_mem is less or equal default value. Increase it to reduce maintenance tasks time
[INFO] Max memory usage :
shared_buffers (8.00 GB)
+ max_connections * work_mem * average_work_mem_buffers_per_connection (1000 * 4.00 MB * 150 / 100 = 5.86 GB)
+ autovacuum_max_workers * maintenance_work_mem (3 * 64.00 MB = 192.00 MB)
+ track activity size (1011.00 KB)
= 14.05 GB
[INFO] effective_cache_size: 4.00 GB
[INFO] Size of all databases : 20.74 GB
[UNKNOWN] OS total mem unknown : unable to analyse PostgreSQL memory usage
----- Logs -----
[OK] log_hostname is off : no reverse DNS lookup latency
[WARN] log of long queries is desactivated. It will be more difficult to optimize query performances
[OK] log_statement=none
----- Two phase commit -----
[OK] Currently no two phase commit transactions
----- Autovacuum -----
[OK] autovacuum is activated.
[INFO] autovacuum_max_workers: 3
----- Checkpoint -----
[WARN] checkpoint_completion_target(0.5) is low
----- Disk access -----
[OK] fsync is on
[OK] synchronize_seqscans is on
----- WAL -----
----- Planner -----
[OK] costs settings are defaults
[UNKNOWN] Information about rotational/SSD disk is unknown : unable to check random_page_cost and seq_page_cost tuning
[OK] all plan features are enabled
===== Database information for database osdb3 =====
----- Database size -----
[INFO] Database osdb3 total size : 20.42 GB
[INFO] Database osdb3 tables size : 11.30 GB (55.33%)
[INFO] Database osdb3 indexes size : 9.12 GB (44.67%)
----- Tablespace location -----
[OK] No tablespace in PGDATA
----- Shared buffer hit rate -----
[INFO] shared_buffer_heap_hit_rate: 83.21%
[INFO] shared_buffer_toast_hit_rate: 99.74%
[INFO] shared_buffer_tidx_hit_rate: 99.98%
[INFO] shared_buffer_idx_hit_rate: 99.77%
[OK] Shared buffer idx hit rate is very good
----- Indexes -----
[OK] No invalid indexes
[WARN] Some indexes are unused since last statistics: activities.index_activities_on_action average_caches.average_caches_index_rater_rateable average_caches.index_average_caches_on_rateable_type_and_rateable_id average_caches.index_average_caches_on_rater_id comments.index_comments_on_osdbid comments.index_comments_on_user_id companies.index_companies_on_parent_company_id companies_features.index_companies_features_on_company_id companies_features.index_companies_features_on_feature_id featfiles.index_featfiles_on_file_hash featfiles.index_featfiles_on_hash_int feathashes.index_feathashes_on_file_hash feathashes.index_feathashes_on_osdb_movie_hash_id features.index_features_on_plot features.index_features_on_tagline features.index_features_on_title features.index_features_on_tmdb_images features_networks.index_features_networks_on_feature_id friendly_id_slugs.index_friendly_id_slugs_on_slug_and_sluggable_type friendly_id_slugs.index_friendly_id_slugs_on_sluggable_id genres.index_genres_on_tmdbid lit_incomming_localizations.index_lit_incomming_localizations_on_incomming_id lit_incomming_localizations.index_lit_incomming_localizations_on_locale_id lit_incomming_localizations.index_lit_incomming_localizations_on_localization_id lit_incomming_localizations.index_lit_incomming_localizations_on_localization_key_id lit_incomming_localizations.index_lit_incomming_localizations_on_source_id mobility_string_translations.index_mobility_string_translations_on_keys mobility_string_translations.index_mobility_string_translations_on_query_keys mobility_string_translations.index_mobility_string_translations_on_translatable_attribute mobility_text_translations.index_mobility_text_translations_on_keys mobility_text_translations.index_mobility_text_translations_on_translatable_attribute names.index_names_on_imdbid names.index_names_on_tmdb_details osdb_languages.index_osdb_languages_on_iso_639 overall_averages.index_overall_averages_on_rateable_id_and_rateable_type overall_averages.index_overall_averages_on_rateable_type_and_rateable_id rates.index_rates_on_rateable_id_and_rateable_type rates.index_rates_on_rateable_type_and_rateable_id rates.index_rates_on_rater_id rates.index_rates_on_rater_id_man rating_caches.index_rating_caches_on_cacheable_id_and_cacheable_type rating_caches.index_rating_caches_on_cacheable_type_and_cacheable_id roles.index_roles_on_resource_type_and_resource_id seasons.index_seasons_on_tmdbid seasons.index_seasons_on_tvdbid subfilehashes.index_subfilehashes_on_file_hash subfilehashes.index_subfilehashes_on_osdb_subfilehash_id subfiles.index_subfiles_on_featfiles_count subfiles.index_subfiles_on_file_hash subfiles.index_subfiles_on_subfilehashes_count subtitles.index_subtitles_on_osdb_user_id subtitles.index_subtitles_on_translator_id taggings.index_taggings_on_context taggings.index_taggings_on_tag_id taggings.index_taggings_on_taggable_id taggings.index_taggings_on_taggable_id_and_taggable_type_and_context taggings.index_taggings_on_taggable_type taggings.index_taggings_on_tagger_id taggings.index_taggings_on_tagger_id_and_tagger_type taggings.taggings_idx tags.index_tags_on_name thredded_categories.index_thredded_categories_on_messageboard_id_and_slug thredded_categories.thredded_categories_name_ci thredded_messageboard_notifications_for_followed_topics.thredded_messageboard_notifications_for_followed_topics_unique thredded_messageboards.index_thredded_messageboards_on_messageboard_group_id thredded_post_moderation_records.index_thredded_moderation_records_for_display thredded_posts.index_thredded_posts_on_messageboard_id thredded_posts.index_thredded_posts_on_postable_id thredded_posts.thredded_posts_content_fts thredded_private_topics.index_thredded_private_topics_on_last_post_at thredded_topic_categories.index_thredded_topic_categories_on_category_id thredded_topics.index_thredded_topics_for_display thredded_topics.index_thredded_topics_on_last_post_at thredded_topics.thredded_topics_title_fts thredded_user_details.index_thredded_user_details_for_moderations thredded_user_details.index_thredded_user_details_on_latest_activity_at thredded_user_topic_read_states.index_thredded_user_topic_read_states_on_messageboard_id users.index_users_on_translations_count users.index_users_on_type
----- Procedures -----
[WARN] Some user procedures does not have custom cost and rows settings : public.hstore_in public.hstore_out public.hstore_recv public.hstore_send public.hstore_version_diag public.fetchval public.slice_array public.slice public.isexists public.exist public.exists_any public.exists_all public.isdefined public.defined public.delete public.delete public.delete public.hs_concat public.hs_contains public.hs_contained public.tconvert public.hstore public.hstore public.hstore public.hstore_to_json public.hstore_to_json_loose public.hstore_to_jsonb public.hstore_to_jsonb_loose public.hstore public.hstore_to_array public.hstore_to_matrix public.akeys public.avals public.populate_record public.hstore_eq public.hstore_ne public.hstore_gt public.hstore_ge public.hstore_lt public.hstore_le public.hstore_cmp public.hstore_hash public.ghstore_in public.ghstore_out public.ghstore_compress public.ghstore_decompress public.ghstore_penalty public.ghstore_picksplit public.ghstore_union public.ghstore_same public.ghstore_consistent public.gin_extract_hstore public.gin_extract_hstore_query public.gin_consistent_hstore
===== Configuration advices =====
----- checkpoint -----
[MEDIUM] Your checkpoint completion target is too low. Put something nearest from 0.8/0.9 to balance your writes better during the checkpoint interval
----- extension -----
[LOW] Enable pg_stat_statements to collect statistics on all queries (not only queries longer than log_min_duration_statement in logs)
----- index -----
[MEDIUM] You have unused indexes in the database since last statistics. Please remove them if they are never use
----- proc -----
[LOW] You have custom procedures with default cost and rows setting. Please reconfigure them with specific values to help the planer
----- report -----
[URGENT] Please configure your .ssh/config to allow postgresqltuner.pl to connect via ssh to db1 without password authentication. This will allow to collect more system informations
adiSuper94
cstdenis
jfcoz
There is no
freecommand, best bet for os_mem is probably a modified version of your darwin way:top -S -n 0Produces output of
The overcommit sysctl is vm.overcommit
I don't think there is an equivalent to overcommit_ratio
FreeBSD does not use sysfs, and it's doesn't really give a choice of IO schedulers anyway.