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:
None 
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
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] Shane Bester
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.