Possible performance regression in version 10.1 with pgbench read-write tests.
Hi all,
When I was trying to do read-write pgbench bench-marking of PostgreSQL
9.6.6 vs 10.1 I found PostgreSQL 10.1 regresses against 9.6.6 in some
cases.
Non Default settings and test
======================
Server:
./postgres -c shared_buffers=8GB -N 200 -c min_wal_size=15GB -c
max_wal_size=20GB -c checkpoint_timeout=900 -c
maintenance_work_mem=1GB -c checkpoint_completion_target=0.9 &
Pgbench:
CASE 1: when data fits shared buffers.
./pgbench -i -s 1000 postgres
CASE 2: when data exceeds shared buffers.
./pgbench -i -s 1000 postgres
./pgbench -c $threads -j $threads -T 1800 -M prepared postgres
Script "perf_buff_mgmt_write-2.sh" which is added below can be used to run same.
Machine : "cthulhu" 8 node numa machine with 128 hyper threads.
===================================================
numactl --hardware
available: 8 nodes (0-7)
node 0 cpus: 0 65 66 67 68 69 70 71 96 97 98 99 100 101 102 103
node 0 size: 65498 MB
node 0 free: 37885 MB
node 1 cpus: 72 73 74 75 76 77 78 79 104 105 106 107 108 109 110 111
node 1 size: 65536 MB
node 1 free: 31215 MB
node 2 cpus: 80 81 82 83 84 85 86 87 112 113 114 115 116 117 118 119
node 2 size: 65536 MB
node 2 free: 15331 MB
node 3 cpus: 88 89 90 91 92 93 94 95 120 121 122 123 124 125 126 127
node 3 size: 65536 MB
node 3 free: 36774 MB
node 4 cpus: 1 2 3 4 5 6 7 8 33 34 35 36 37 38 39 40
node 4 size: 65536 MB
node 4 free: 62 MB
node 5 cpus: 9 10 11 12 13 14 15 16 41 42 43 44 45 46 47 48
node 5 size: 65536 MB
node 5 free: 9653 MB
node 6 cpus: 17 18 19 20 21 22 23 24 49 50 51 52 53 54 55 56
node 6 size: 65536 MB
node 6 free: 50209 MB
node 7 cpus: 25 26 27 28 29 30 31 32 57 58 59 60 61 62 63 64
node 7 size: 65536 MB
node 7 free: 43966 MB
CASE 1:
In 9.6.6 peak performance is achieved at 72 concurrent cleints TPS :
35554.573858 and in 10.1 at 72 clients TPS dips to 26882.828133 so
nearly 23% decrease in TPS.
CASE 2:
In 9.6.6 peak performance is achieved at 72 concurrent cleints TPS :
24861.074079 and in 10.1 at 72 clients TPS dips to 18372.565663 so
nearly 26% decrease in TPS.
Added "Postgresql_benchmarking_9.6vs10.ods" which gives more detailed
TPS numbers. And, TPS is median of 3 runs result.
I have not run bisect yet to find what has caused the issue.
--
Thanks and Regards
Mithun C Y
EnterpriseDB: http://www.enterprisedb.com
Attachments:
Postgresql_benchmarking_9.6vs10.odsapplication/vnd.oasis.opendocument.spreadsheet; name=Postgresql_benchmarking_9.6vs10.odsDownload
PK ��7L�l9�. . mimetypeapplication/vnd.oasis.opendocument.spreadsheetPK ��7L content.xml�]m���q��_�WRN�����v]�u%��W�o�q�?p$J��"U$5�{�>
���I��OJ�{W�B�4
40��7����1���,��1B�&i1+�Y�|s���=2w�y����E6K���l�N�&��E�'�.������mU��I���E�N��fv_n����C�����I�|���V8D7��v���_s+��U��;Y0j_�X��:�%X}�I�����������i6�����y���Sf���ow
�vr�m��R��4�SWY=e�M��:m�~N6T����
m��I^���Jk��:b�
1�z\����<a��*��<k��"�x��y�]'��D����������=��5�.'{`�Y�m����C|Y�;U�����R9�~����?UY�V�����,�g;���cF96�(}t��
"g���O��;�z~��������*]'{����QV�MR�-S�N8�R5��MY5;�,��z��t[5����po��������:b
nq���O�:����`��P�O��:������;�j������wn�3F�i�V�{��-������y��"�9-(���gi�G��IG�)�h]����>@����8G�r�x^��A?�k�����w��6ab�k
�~����/�����E2K�y:����u>~�x����~s��4L�l�D��|~7?�%�Y���@X���iMQ?eu}7=_��f��?'��>R�?%����gr�CD���&kf���*k���~��-���y��J��&]�N�U�L~�>&E��i{=�E��sZ�q���M�'-&�K��G��7'�<!1]���@�<�6%���,j������A�f%�U�7�u����uq����hC=��,�'����J����F=���%��O��Z�6M���9��/��-�r�r�S�0"���S�Z�O�4�'�Z�+�p�f�xm��q�v^�m�F����I�����x�����������6�2��/[$��y���A��j���<�������?X2E�r%�U�<�����I����$�[����U����#����\T��S�j�W��_�e5w+ J��4����������W�����?�h+XnX����$���W�y��@��*���EY�j>o������lz�o��gx�hO�<)�[��7wiq�)�-�
L�����S����6�*������`��ju=h�/��g�C<�u�q{Y�-���1R�:%��IqwRx�t/�q�;g"�=Z+��Vay-�w�h����^��y0���V�y���B��/U��:����i�v"/�']oVIG����<KG����S�� ���-��y��85�]�U|�`�<5�]T���l�v>������/Y<�x�(O��2:����X{N�)7'���J���o���\��Y5���&'y�,���V.}��>��U�I�]�c�&����f��J6�g��7�qp��~�����-�Cu�U��������^��[O��v����u�}x(�*���}�������%40�Ve��\��i��fi�fw�]
���:��s_Z fp? I7��m�����yUhi�k��A�����/t��� �_<������
f^���y�]�c�2�������q%~3e_�"^��C����O}�������e�~����P���.�v����t�@����vg�
�w�9���h�w�+�Z��z���m'=.���$7D����M+���?�:��� :V[B���u����(�l�9�m7��I�rU������A�����Gq>���;�(�����Q�J����c�z�Q�=�����~���I{X�~U9�M���!��]\Qgn��������C:���Y���}sW�d��#[�u���M���3��u�g����!�� 1�Xi5�, �(���G���!"
���a�-�,�^�Pz�G����{�^��A
~��r�Z��p^ &�>KsdBj�d}&����X�@��Sty����
_���/��D�U�t�@��zV7�w�f�^���|���>�!����G�A���y��*�'mP3q�$+ �J�dR>�_��$Y@�4��E������D��I���lk�x�|H��
"�j���B=�DL�r�(P���sl���m�N��=PG��@:���E/lb��&�B�dc�#(����[y�� �L���z����a�X����#�c��b�|�!���_Y���� �����vkZ���5�ri]�>*�D=�[����1���������<[,�jrA*Qv��Is��6�?�|'���
3 �r�)QB[cPZ���Q\&'��1�qV����`�$�6O �\������+��N��_`Y���v�����nl1�s��Vr\+��5v�x14��h���2�I!�������)���?���q��x8?c��P@M����s�5~2�s�}����X)b���`$���Q��X�J�2,5=KM�R3���5�Q�b3
����o���~~��ak
3��I� 0�J��9����J�2<�=Om�S;���H��fR[%c��B���TpT�����A�`�fU�q�#��� b����R �K�i
?�<u����X&el(��n��[#���&���N4+4�)�K��"