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 Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.26, 8.0.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: performance_schema

[13 Jul 2019 7:17] Nikolai Ikhalainen
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:
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

typedef PFS_buffer_scalable_container<PFS_account,
                                      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)

| Performance_schema_hosts_lost | 265925 | 

How to repeat:
a) check the source code
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=
mysql --bind-address=
mysql --bind-address=
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.

[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.