Hi.
On Wed, July 25, 2018 at 0:28 AM, Alexander Korotkov wrote:
> Hi!
>
> On Tue, Jul 10, 2018 at 4:39 PM 今井 良一 <yoshikazu_i443(at)live(dot)jp> wrote:
> > On 2018/07/10 20:36, Alexander Korotkov wrote:
> > > Thank you for the experiments! It seems that there is real regression
> > > here... BTW, which script were you using in this benchmark:
> > > script_unordered.sql or script_duplicated.sql?
> >
> > Sorry, I forgot to write that. I used script_unordered.sql.
>
> I've reread the thread. And I found that in my initial letter [1] I
> forget to include index definition.
> CREATE INDEX unordered_i_idx ON unordered (i);
>
> So, when you did experiments [2], did you define any index on
> unordered table? If not, then it's curious why there is any
> performance difference at all.
>
> 1.
> https://www.postgresql.org/message-id/CAPpHfduAMDFMNYTCN7VMBsFg_hsf0GqiqXnt%2BbSeaJworwFoig%40mail.gmail.com
> 2.
> https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A51189451%40g01jpexmbkw24
My experiment[1][2] was totally wrong that I didn't create index in unordered
and duplicated case.
On Mon, July 9, 2018 at 3:19 AM, I wrote:
> # script_duplicated.sql
> INSERT INTO unordered VALUES (1, 'abcdefghijklmnoprsqtuvwxyz');
I also made mistake in duplicated.sql that I used unordered table in duplicated
experiment...
I re-run the experiments.
This time, I created indexes on unordered and duplicated table, and confirmed
indexes are created in both master and patched by using \d commands in psql.
I used AWS c5.18xlarge, and executed pgbench with 36 clients.
# results with indexes created
master, ordered, Ave 252796 TPS (253122,252469)
patched, ordered, Ave 314541 TPS (314011,315070)
master, unordered, Ave 409591 TPS (413901,412010,404912,404607,416494,405619)
patched, unordered, Ave 412765 TPS (409626,408578,416900,415955)
master, duplicated, Ave 44811 TPS (45139,44482)
patched, duplicated, Ave 202325 TPS (201519,203130)
The TPS of "master, ordered" and "patched, ordered" is similar to the TPS of
"master, key1 with 1 values"
in previous experiment[3] (contention is concentrated), the TPS of "master,
unordered" and "patched, unordered"
is similar to the TPS of "master, key1 with 100 values" in previous
experiment[3] (contention is dispersed).
Therefore this experiment and previous experiment[3] asked for from Simon is
correct I think.
The TPS of "patched, duplicated" was almost 4.5 times bigger than "master,
duplicated" one.
About a small performance regression in previous experiments[1], I think it is
because of machine's performance.
I don't have rational reason nor evidence, so I only describe what I thought
and did (TL;DR:).
In this experiments, in order to increase machine's performance, I did
something like "machine warm" as below batch.
## DDL
CREATE UNLOGGED TABLE improve_performance (i integer not null, value text not
null);
CREATE INDEX improve_i_idx on improve_performance (i);
## improve_performance.sql
\set i random(1, 1000000)
INSERT INTO improve_performance VALUES (:i, 'abcdefghijklmnopqrstuvwxyz');
## test batch
port=$1
client=$2
j=$3
tablenames_all=(ordered unordered duplicated ordered2)
tablenames=(unordered)
function cleaning() {
for tablename_ in ${tablenames_all[@]}; do
psql -p ${port} -c "truncate table ${tablename_};"
done
psql -p ${port} -c "vacuum full analyze;"
}
pgbench -p ${port} -T 60 -P 1 -M prepared -f improve_performance.sql -c 72 -j
36 postgres # do for warm
cleaning
for tablename in ${tablenames[@]}; do
for i in `seq 0 1`; do
pgbench -p ${port} -T 60 -P 1 -M prepared -f script_${tablename}.sql -c
${client} -j ${j} postgres`
cleaning
done
done
When I created index on improve_performance table and executed test batch
against unordered table
in patched version three times, results are below.
1. 379644,381409
2. 391089,391741
3. 383141,367851
These results are little decreased compared to "patched, key1 with 100 values:
405115" (unordered)
in my previous experiments[3].
I thought if I execute pgbench for warm against table with index created, warm
hasn't be completely done.
Because in experiments[3], I executed pgbench for warm against table without
index created.
So I dropped index from improve_performance table and executed test batch
against patched two times, results are below.
1. 409626,408578,
2. 416900,415955,
Even in the same test, performance differs largely, so a small regression can
be ignored I think.
Of course, I wonder there were regressions in all cases at the experiments[2].
[1]
https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A51186E9D%40g01jpexmbkw24
[2]
https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A51189451@g01jpexmbkw24
[3]
https://www.postgresql.org/message-id/0F97FA9ABBDBE54F91744A9B37151A5118C7C3%40g01jpexmbkw24
Yoshikazu Imai
