On Wed, Mar 19, 2014 at 10:09 PM, acanada <acanada@xxxxxxx> wrote:
Hello,First of all I'd like to thank all of you for taking your time and help me with this. Thank you very much.I did migrate the database to the new server with 32 processors Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60GHz and 60GB of RAM.Evegeny pointed that the disks I am using are not fast enough (For data: 00:1f.2 RAID bus controller: Intel Corporation C600/X79 series chipset SATA RAID Controller (rev 05); and for logging a SAS disk but with only 240GB available, database is 365GB...). I cannot change the locations of data and log since there's not enough space for the data in the SAS disk. Sadly this is a problem that I cannot solve any time soon...The migration had really improved the performanceI paste the before and after (the migration) explain analyze, buffers(if aplicable due to server versions)BEFORE:explain analyze select * from (select * from entity2document2 where name='Acetaminophen' ) as a order by a.hepval;QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------------Sort (cost=18015.66..18027.15 rows=4595 width=139) (actual time=39755.942..39756.246 rows=2845 loops=1)Sort Key: entity2document2.hepvalSort Method: quicksort Memory: 578kB-> Bitmap Heap Scan on entity2document2 (cost=116.92..17736.15 rows=4595 width=139) (actual time=45.682..39751.255 rows=2845 loops=1)Recheck Cond: ((name)::text = 'Acetaminophen'::text)-> Bitmap Index Scan on entity2document2_name (cost=0.00..115.77 rows=4595 width=0) (actual time=45.124..45.124 rows=2845 loops=1)Index Cond: ((name)::text = 'Acetaminophen'::text)Total runtime: 39756.507 msAFTER:explain (analyze,buffers) select * from (select * from entity2document2 where name='Acetaminophen' ) as a order by a.hepval;QUERY PLAN------------------------------------------------------------------------------------------------------------------------------------------------Sort (cost=18434.76..18446.51 rows=4701 width=131) (actual time=9196.634..9196.909 rows=2845 loops=1)Sort Key: entity2document2.hepvalSort Method: quicksort Memory: 604kBBuffers: shared hit=4 read=1725-> Bitmap Heap Scan on entity2document2 (cost=105.00..18148.03 rows=4701 width=131) (actual time=38.668..9190.318 rows=2845 loops=1)Recheck Cond: ((name)::text = 'Acetaminophen'::text)Buffers: shared hit=4 read=1725-> Bitmap Index Scan on entity2documentnew_name (cost=0.00..103.82 rows=4701 width=0) (actual time=30.905..30.905 rows=2845 loops=1)Index Cond: ((name)::text = 'Acetaminophen'::text)Buffers: shared hit=1 read=14Total runtime: 9197.186 msThe improve is definitely good!!.This is the table that I'm using:\d+ entity2document2;Table "public.entity2document2"Column | Type | Modifiers | Storage | Stats target | Description------------------+--------------------------------+-----------+----------+--------------+-------------id | integer | not null | plain | |document_id | integer | | plain | |name | character varying(255) | not null | extended | |qualifier | character varying(255) | not null | extended | |tagMethod | character varying(255) | | extended | |created | timestamp(0) without time zone | not null | plain | |updated | timestamp(0) without time zone | | plain | |curation | integer | | plain | |hepval | double precision | | plain | |cardval | double precision | | plain | |nephval | double precision | | plain | |phosval | double precision | | plain | |patternCount | double precision | | plain | |ruleScore | double precision | | plain | |hepTermNormScore | double precision | | plain | |hepTermVarScore | double precision | | plain | |svmConfidence | double precision | | plain | |Indexes:"ent_pkey" PRIMARY KEY, btree (id)"ent_cardval" btree (cardval)"ent_document_id" btree (document_id)"ent_heptermnormscore" btree ("hepTermNormScore")"ent_heptermvarscore" btree ("hepTermVarScore")"ent_hepval" btree (hepval)"ent_name" btree (name)"ent_nephval" btree (nephval)"ent_patterncount" btree ("patternCount")"ent_phosval" btree (phosval)"ent_qualifier" btree (qualifier)"ent_qualifier_name" btree (qualifier, name)"ent_rulescore" btree ("ruleScore")"ent_svm_confidence_index" btree ("svmConfidence")And this are my current_settingsname | current_setting | source----------------------------+--------------------+----------------------application_name | psql | clientclient_encoding | UTF8 | clientDateStyle | ISO, MDY | configuration filedefault_text_search_config | pg_catalog.english | configuration fileeffective_cache_size | 45000MB | configuration filelc_messages | en_US.UTF-8 | configuration filelc_monetary | en_US.UTF-8 | configuration filelc_numeric | en_US.UTF-8 | configuration filelc_time | en_US.UTF-8 | configuration filelisten_addresses | * | configuration filelog_timezone | Europe/Madrid | configuration filelogging_collector | on | configuration filemaintenance_work_mem | 4000MB | configuration filemax_connections | 100 | configuration filemax_stack_depth | 2MB | environment variableshared_buffers | 10000MB | configuration fileTimeZone | Europe/Madrid | configuration filework_mem | 32MB | configuration fileThe size of the table is 41 GB and some statistics:relname | rows_in_bytes | num_rows | number_of_indexes | unique | single_column | multi_columnentity2document2 | 89 MB | 9.33479e+07 | 14 | Y | 13 | 1I'm doing right now the CLUSTER on the table using the name+hepval multiple index as Venkata told me and will post you if it works.Anyway, even though the improvement is important, I'd like an increase of the performance. When the number of rows returned is high, the performance decreases too much..
Sorry, i have not been following this since sometime now.
Hardware configuration is better now. You were running on 8.3.x, can you please help us know what version of Postgres is this ?
Did you collect latest statistics and performed VACUUM after migration ?
Can you get us the EXPLAIN plan for "select * from entity2document2 where name='Acetaminophen' ; " ?
Venkata Balaji N
Sr. Database Administrator
Fujitsu Australia
