{"action":"opened","issue":{"url":"https://api.github.com/repos/sysown/proxysql/issues/486","repository_url":"https://api.github.com/repos/sysown/proxysql","labels_url":"https://api.github.com/repos/sysown/proxysql/issues/486/labels{/name}","comments_url":"https://api.github.com/repos/sysown/proxysql/issues/486/comments","events_url":"https://api.github.com/repos/sysown/proxysql/issues/486/events","html_url":"https://github.com/sysown/proxysql/issues/486","id":136915236,"number":486,"title":"Performance degrades with an elevate number of rows","user":{"login":"renecannao","id":3645227,"avatar_url":"https://avatars.githubusercontent.com/u/3645227?v=3","gravatar_id":"","url":"https://api.github.com/users/renecannao","html_url":"https://github.com/renecannao","followers_url":"https://api.github.com/users/renecannao/followers","following_url":"https://api.github.com/users/renecannao/following{/other_user}","gists_url":"https://api.github.com/users/renecannao/gists{/gist_id}","starred_url":"https://api.github.com/users/renecannao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/renecannao/subscriptions","organizations_url":"https://api.github.com/users/renecannao/orgs","repos_url":"https://api.github.com/users/renecannao/repos","events_url":"https://api.github.com/users/renecannao/events{/privacy}","received_events_url":"https://api.github.com/users/renecannao/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/sysown/proxysql/labels/enhancement","name":"enhancement","color":"84b6eb"},{"url":"https://api.github.com/repos/sysown/proxysql/labels/MYSQL","name":"MYSQL","color":"0052cc"},{"url":"https://api.github.com/repos/sysown/proxysql/labels/PROTOCOL","name":"PROTOCOL","color":"006b75"}],"state":"open","locked":false,"assignee":{"login":"renecannao","id":3645227,"avatar_url":"https://avatars.githubusercontent.com/u/3645227?v=3","gravatar_id":"","url":"https://api.github.com/users/renecannao","html_url":"https://github.com/renecannao","followers_url":"https://api.github.com/users/renecannao/followers","following_url":"https://api.github.com/users/renecannao/following{/other_user}","gists_url":"https://api.github.com/users/renecannao/gists{/gist_id}","starred_url":"https://api.github.com/users/renecannao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/renecannao/subscriptions","organizations_url":"https://api.github.com/users/renecannao/orgs","repos_url":"https://api.github.com/users/renecannao/repos","events_url":"https://api.github.com/users/renecannao/events{/privacy}","received_events_url":"https://api.github.com/users/renecannao/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/sysown/proxysql/milestones/4","html_url":"https://github.com/sysown/proxysql/milestones/Truls","labels_url":"https://api.github.com/repos/sysown/proxysql/milestones/4/labels","id":1475254,"number":4,"title":"Truls","description":"","creator":{"login":"renecannao","id":3645227,"avatar_url":"https://avatars.githubusercontent.com/u/3645227?v=3","gravatar_id":"","url":"https://api.github.com/users/renecannao","html_url":"https://github.com/renecannao","followers_url":"https://api.github.com/users/renecannao/followers","following_url":"https://api.github.com/users/renecannao/following{/other_user}","gists_url":"https://api.github.com/users/renecannao/gists{/gist_id}","starred_url":"https://api.github.com/users/renecannao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/renecannao/subscriptions","organizations_url":"https://api.github.com/users/renecannao/orgs","repos_url":"https://api.github.com/users/renecannao/repos","events_url":"https://api.github.com/users/renecannao/events{/privacy}","received_events_url":"https://api.github.com/users/renecannao/received_events","type":"User","site_admin":false},"open_issues":12,"closed_issues":12,"state":"open","created_at":"2015-12-27T20:11:27Z","updated_at":"2016-02-27T12:48:07Z","due_on":"2016-06-30T07:00:00Z","closed_at":null},"comments":0,"created_at":"2016-02-27T12:48:07Z","updated_at":"2016-02-27T12:48:07Z","closed_at":null,"body":"While profiling memory usage I came across a performance bottleneck with a resultset with a large number of rows.\r\nThe bottleneck is not present with large resultset in general, but only with resultset with a lot of rows.\r\n\r\nFor example, using a large table with 2M rows:\r\n```\r\nvagrant@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\"\r\nWarning: Using a password on the command line interface can be insecure.\r\nBenchmark\r\n        Average number of seconds to run all queries: 16.755 seconds\r\n        Minimum number of seconds to run all queries: 15.146 seconds\r\n        Maximum number of seconds to run all queries: 21.020 seconds\r\n        Number of clients running queries: 8\r\n        Average number of queries per client: 1\r\n```\r\n\r\nAccording to `perf` : \r\n```\r\n# Samples: 544K of event 'cpu-clock'\r\n# Event count (approx.): 136120500000\r\n#\r\n# Overhead   Command        Shared Object                                                                                                           Symbol\r\n# ........  ........  ...................  ...............................................................................................................\r\n#\r\n    70.18%  proxysql  libc-2.19.so         [.] __memmove_ssse3_back\r\n     2.26%  proxysql  [kernel.kallsyms]    [k] clear_page\r\n     2.09%  proxysql  proxysql             [.] MySQL_Protocol::generate_pkt_row2(PtrSizeArray*, unsigned int*, unsigned char, int, unsigned long*, char**)\r\n     1.98%  proxysql  proxysql             [.] MySQL_Data_Stream::array2buffer()\r\n     1.47%  proxysql  proxysql             [.] MySQL_Threads_Handler::listener_add(char const*)\r\n     1.46%  proxysql  libc-2.19.so         [.] __memcpy_sse2_unaligned\r\n     1.46%  proxysql  proxysql             [.] MySQL_Connection::handler(short)\r\n     1.33%  proxysql  proxysql             [.] my_real_read\r\n     1.30%  proxysql  proxysql             [.] malloc\r\n     1.11%  proxysql  proxysql             [.] free\r\n     1.08%  proxysql  proxysql             [.] mthd_my_read_one_row\r\n     1.05%  proxysql  proxysql             [.] je_arena_dalloc_bin_locked\r\n     1.01%  proxysql  [kernel.kallsyms]    [k] smp_call_function_many\r\n     0.89%  proxysql  proxysql             [.] je_arena_tcache_fill_small\r\n     0.80%  proxysql  proxysql             [.] vio_read\r\n     0.66%  proxysql  [kernel.kallsyms]    [k] _raw_spin_unlock_irqrestore\r\n     0.60%  proxysql  proxysql             [.] my_net_read\r\n     0.47%  proxysql  [kernel.kallsyms]    [k] __do_page_fault\r\n     0.45%  proxysql  proxysql             [.] net_safe_read\r\n     0.43%  proxysql  proxysql             [.] MySQL_ResultSet::add_row(char**)\r\n     0.43%  proxysql  proxysql             [.] je_tcache_bin_flush_small\r\n     0.43%  proxysql  proxysql             [.] mysql_fetch_row_start\r\n     0.41%  proxysql  [kernel.kallsyms]    [k] generic_exec_single\r\n     0.40%  proxysql  [kernel.kallsyms]    [k] copy_user_generic_unrolled\r\n     0.36%  proxysql  [kernel.kallsyms]    [k] copy_page\r\n     0.34%  proxysql  proxysql             [.] mysql_fetch_row_start_internal\r\n     0.33%  proxysql  proxysql             [.] mysql_fetch_row\r\n     0.29%  proxysql  proxysql             [.] PtrSizeArray::copy_add(PtrSizeArray*, unsigned int, unsigned int)\r\n```\r\n\r\nThere is a clear bottleneck in memmove()\r\n\r\nAn educated guess is that the main offender is:\r\n* MySQL_ResultSet::add_row() calls MySQL_Protocol::generate_pkt_row2()\r\n* for each row a packet is generated (PtrSize_t) and insert into PSArrayOUT\r\n* MySQL_Data_Stream::array2buffer() does a lot of calls to PtrArray::remove_index_range()\r\n* PtrArray::remove_index_range() calls memmove()"}}