Bug #96196 performance_schema_accounts_size and p_s_hosts_size limited by 16384

• Submitted: 13 Jul 2019 7:17
• Modified: 15 Jul 2019 14:34
• Reporter: Nikolai Ikhalainen
• Status: Verified
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: 5.7.26, 8.0.16
• OS: Any
• CPU Architecture: Any
• Tags: performance_schema

[13 Jul 2019 7:17] Nikolai Ikhalainen

Description:
Both performance_schema_hosts_size and performance_schema_accounts_size could have a maximum value 1048576.

With USE_SCALABLE (defined by default in pfs_buffer_container.h) the size is actually limited by page size and page count:
https://github.com/mysql/mysql-server/blob/8.0/storage/perfschema/pfs_buffer_container.h#L...
template <class T, int PFS_PAGE_SIZE, int PFS_PAGE_COUNT,
          class U = PFS_buffer_default_array<T>,
          class V = PFS_buffer_default_allocator<T>>
class PFS_buffer_scalable_container
...
m_max = PFS_PAGE_COUNT * PFS_PAGE_SIZE;

typedef PFS_buffer_scalable_container<PFS_account,
                                      128,
                                      128,
                                      PFS_account_array,
                                      PFS_account_allocator> PFS_account_container;

Thus the actual maximum value for performance_schema_hosts_size and performance_schema_accounts_size is just 128*128 or 16384.

This is confirmed if you will try to connect from 16384+ different ip addresses, the size will be still 16384:
mysql> select count(*) from performance_schema.hosts; 
+----------+ 
| count(*) | 
+----------+ 
| 16384 | 
+----------+ 
1 row in set (4.62 sec)

and:
| Performance_schema_hosts_lost | 265925 | 

How to repeat:
a) check the source code
b) 
docker pull mysql:5.7
docker run -d -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 -p m57 mysql:5.7
# you may run several for loops in parallel to increase the speed and probably replace select count(*) from p_s.hosts query with select 1; (performance schema query is really slow with big number of hosts).
for j in `seq 1 254` ; do for i in `seq 1 254`;do ip addr add 172.16.${j}.${i}/32 dev lo;echo $i;mysql -N --protocol=tcp --host 172.16.${j}.$i --password=secret -e 'select count(*) from performance_schema.hosts' 2>/dev/null;ip addr del 172.16.${j}.${i}/32 dev lo ; done ; done
for j in `seq 1 254` ; do for i in `seq 1 254`;do ip addr add 172.18.${j}.${i}/32 dev lo;echo $i;mysql -N --protocol=tcp --host 172.18.${j}.$i --password=secret -e 'select count(*) from performance_schema.hosts' 2>/dev/null;ip addr del 172.18.${j}.${i}/32 dev lo ; done ; done

select count(*) from performance_schema.hosts is not able to return values bigger than 16384.

Suggested fix:
Mention actual maximum size in the documentation or provide the ability to increase maximum hosts and accounts size up to 1048576 (at least if max_connections is huge enough).

[13 Jul 2019 11:11] MySQL Verification Team

fyi probably a faster way to simulate connecting from a 
gazillion IP's is to use run client like this :

mysql --bind-address=127.0.0.1
mysql --bind-address=127.0.0.2
mysql --bind-address=127.0.0.3
mysql --bind-address=127.0.x.y

Add them all to /etc/hosts too, if resolution is needed.

[15 Jul 2019 14:34] MySQL Verification Team

Hello Nikolai Ikhalainen,

Thank you for the report and feedback.

Thanks,
Umesh

[15 Jul 2019 16:25] MySQL Verification Team

Test results - 8.0.16

Attachment: 96196_8.0.16.results (application/octet-stream, text), 3.84 KiB.

[15 Jul 2019 20:58] Trey Raymond

Considering the documented max of them is 1M, the right fix would be to let autoscaling work up to that - just documenting that it's broken isn't a useful solution for users (and means people will need to manually scale again, which defeats the whole purpose of the system)

[16 Jul 2019 10:28] MySQL Verification Team

Test results - 5.7.26

Attachment: 96196_5.7.26.results (application/octet-stream, text), 4.17 KiB.