Performance degrades with an elevate number of rows

[renecannao]

While profiling memory usage I came across a performance bottleneck with a resultset with a large number of rows.
The bottleneck is not present with large resultset in general, but only with resultset with a lot of rows.

For example, using a large table with 2M rows:

vagrant@ubuntu-14:~$ mysqlslap -u msandbox -pmsandbox -c 8 -i 5 -P6033 -h 127.0.0.1 --create-schema=sbtest -q "SELECT id FROM sbtest.longtable"
Warning: Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 16.755 seconds
        Minimum number of seconds to run all queries: 15.146 seconds
        Maximum number of seconds to run all queries: 21.020 seconds
        Number of clients running queries: 8
        Average number of queries per client: 1

According to perf :

# Samples: 544K of event 'cpu-clock'
# Event count (approx.): 136120500000
#
# Overhead   Command        Shared Object                                                                                                           Symbol
# ........  ........  ...................  ...............................................................................................................
#
    70.18%  proxysql  libc-2.19.so         [.] __memmove_ssse3_back
     2.26%  proxysql  [kernel.kallsyms]    [k] clear_page
     2.09%  proxysql  proxysql             [.] MySQL_Protocol::generate_pkt_row2(PtrSizeArray*, unsigned int*, unsigned char, int, unsigned long*, char**)
     1.98%  proxysql  proxysql             [.] MySQL_Data_Stream::array2buffer()
     1.47%  proxysql  proxysql             [.] MySQL_Threads_Handler::listener_add(char const*)
     1.46%  proxysql  libc-2.19.so         [.] __memcpy_sse2_unaligned
     1.46%  proxysql  proxysql             [.] MySQL_Connection::handler(short)
     1.33%  proxysql  proxysql             [.] my_real_read
     1.30%  proxysql  proxysql             [.] malloc
     1.11%  proxysql  proxysql             [.] free
     1.08%  proxysql  proxysql             [.] mthd_my_read_one_row
     1.05%  proxysql  proxysql             [.] je_arena_dalloc_bin_locked
     1.01%  proxysql  [kernel.kallsyms]    [k] smp_call_function_many
     0.89%  proxysql  proxysql             [.] je_arena_tcache_fill_small
     0.80%  proxysql  proxysql             [.] vio_read
     0.66%  proxysql  [kernel.kallsyms]    [k] _raw_spin_unlock_irqrestore
     0.60%  proxysql  proxysql             [.] my_net_read
     0.47%  proxysql  [kernel.kallsyms]    [k] __do_page_fault
     0.45%  proxysql  proxysql             [.] net_safe_read
     0.43%  proxysql  proxysql             [.] MySQL_ResultSet::add_row(char**)
     0.43%  proxysql  proxysql             [.] je_tcache_bin_flush_small
     0.43%  proxysql  proxysql             [.] mysql_fetch_row_start
     0.41%  proxysql  [kernel.kallsyms]    [k] generic_exec_single
     0.40%  proxysql  [kernel.kallsyms]    [k] copy_user_generic_unrolled
     0.36%  proxysql  [kernel.kallsyms]    [k] copy_page
     0.34%  proxysql  proxysql             [.] mysql_fetch_row_start_internal
     0.33%  proxysql  proxysql             [.] mysql_fetch_row
     0.29%  proxysql  proxysql             [.] PtrSizeArray::copy_add(PtrSizeArray*, unsigned int, unsigned int)

There is a clear bottleneck in memmove()

An educated guess is that the main offender is:

• MySQL_ResultSet::add_row() calls MySQL_Protocol::generate_pkt_row2()
• for each row a packet is generated (PtrSize_t) and insert into PSArrayOUT
• MySQL_Data_Stream::array2buffer() does a lot of calls to PtrArray::remove_index_range()
• PtrArray::remove_index_range() calls memmove()