09/27/07 -------- DB performance test continued. All timing figures are in seconds. ---------------------------------------------------------------------- Single connection insert performance: single vs bulk insert 1 record: qp_fsn: 0.004566s/record qp_t_obs: 0.002776s/record qp_fsn_t_obs: 0.002762s/record 10 records: qp_fsn: 0.0008559s/record qp_t_obs: 0.0007473s/record qp_fsn_t_obs: 0.0007436s/record 100 records: qp_fsn: 0.00056648s/record qp_t_obs: 0.00054015s/record qp_fsn_t_obs: 0.00054039s/record 1000 records: qp_fsn: 0.000536186s/record qp_t_obs: 0.000543959s/record qp_fsn_t_obs: 0.000530158s/record DRMS uses SQL COPY, not SQL INSERT. Yet to find out the performance difference between the two ---------------------------------------------------------------------- Concurrent connections: * locking is not a problem: - query requires ACCESS SHARE lock, insert requires ROW EXCLUSIVE - no conflicts: . ACCESS SHARE lock does not conflict with ACCESS SHARE lock . ROW EXCLUSIVE lock does not conflict with ROW EXCLUSIVE lock . ACCESS SHARE lock does not conflict with ROW EXCLUSIVE lock * Scenarios: - insert (single record) only - query only - insert (single record) + query mix Queries: Query 1: X[fsn=1234567] Query 2: X[fsn >= 1234567 and fsn <= 1234569] Query 3: X[fsn = max(fsn)] Query 4: X[max(recnum)] Query 5: X[t_obs >= t1 and t_obs <= t2] Randomly generated queries among the above five options over three tables, indexed on fsn, t_obs, (fsn, t_obs) respectively. However, queries on non-index columns are skipped. With these remaining query candidates, only Q5 on table with index (fsn, t_obs) takes more than a minute, the rest takes < 1 in the single connection case. Got a wide range of numbers. Here is a sample for queries on the table with index on fsn number of connections 1 10 50 100 Q1 <0.2 0.1-0.8 0.1-4 0.1-7 Q2 <0.1 0.1-0.6 0.1-6 <0.1-6 Q1 and Q2: %iowait > 20%, %util near 100%, seems to be limited by IO (runs too fast, hard to observe) Q3 and Q4: %iowait 0%, no observable performance degredation with 100 connections. Obviously cached. Q5 on table with (fsn, t_obs), %iowait < 2%, seems to use the index in memory ---------------------------------------------------------------------- parameter shared_buffers: maximum (virtual) memory for a connection hmidb2 has 8166520KB memory or 1020815 8KB blocks with shared_buffers set to 50% of RAM, ~500,000 blocks * memory usage goes up to 4G, about the size of shared_buffers - psql starts off with a very small memory imprint, and grows up to the shared_buffer size. It does not seem to scale back on memory. Curious, no swapping observed so far. Why? * Simple queries result in memory usage being much lower than the maximum. ---------------------------------------------------------------------- maintenance work: reindex, vacuum With nothing else running, * a table with 150M rows - reindex takes nearly 1.5 hours - vacuum takes more than 2 hours * a table with 1M rows - reindex a table takes a few seconds - vacuum a takes a few minutes partitioning the table by time can be helpful since we only need to do these maintenance work on the parts that change. ---------------------------------------------------------------------- Why is a composite index slow on the second key? - queries on fsn and t_obs both do index scan * query on fsn, it takes a faction of a second * query on t_obs, it takes a few minutes difference: the latter has %iowait=20% size of index is about 10G, size of the table is about 200G, the factor of 20 is just about right for the time to scan the whole table and the time to whole index. It seems to me the slowness comes with loading the whole index. - conclusion: not all index scans are equal - to verify: switch the order of keys in the composite index ----------------------------------------------------------------------