#224 Performance degradation with a large number of subnets

[David Taylor]

We are currently using PHPIPAM for over 900 subnets and we have noticed a serious degradation in performance in the section containing the majority of those subnets. When we click on the section it takes around 30seconds to complete the page load, the database isn't heavily loaded and other apps using it aren't having any issues.

I have noticed two things:

1) In the Mysql slow query log I am getting the following

SELECT count(*) from subnets where masterSubnetId = "";

User@Host: phpipam[phpipam] @ [10.0.0.10]

Query_time: 0.001454 Lock_time: 0.000037 Rows_sent: 1 Rows_examined: 904

SET timestamp=1408575606;
SELECT count(*) from subnets where masterSubnetId = "";

User@Host: phpipam[phpipam] @ [10.0.0.10]

Query_time: 0.001509 Lock_time: 0.000037 Rows_sent: 1 Rows_examined: 904

SET timestamp=1408575606;
SELECT count(*) from subnets where masterSubnetId = "";

User@Host: phpipam[phpipam] @ [10.0.0.10]

Query_time: 0.001472 Lock_time: 0.000039 Rows_sent: 1 Rows_examined: 904

SET timestamp=1408575606;

2) SHOW ENGINE INNODB STATUS shows the following after phpipam has been used

---

BUFFER POOL AND MEMORY

Total memory allocated 2311281320; in additional pool allocated 482432
Dictionary memory allocated 197496
Buffer pool size 131072
Free buffers 130907
Database pages 163
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 163, created 0, written 1
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout

---

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 5369, id 698444656, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 5520
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

My guess is that the regular check for the number of rows is not scaling well, partly because it isn't a buffered function.

[Anonymous]

Hi David, how much time does it take if you do it manually? I have similar size and for me page load is always < 1s.

Have you done any changes to MySQL configuration or you left default values? I would propose to run mysqltuner (http://mysqltuner.com) to get some recommended values to change for mysql server, please post results here or send it via email.

mysql> select count(*) from subnets;
+----------+
| count(*) |
+----------+
|      911 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT count(*) from subnets where masterSubnetId = "";
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.02 sec)

mysql>

[Miha Petkovsek]

Above would be me :)

[Anonymous]

Hi miha, I have run a mysql tuning script that has all our other apps looking great:) Phpipam serms to have an issue with the section that has most of our subnets with initial page load around the 30sec mark, I'm happy to pop it up on teamviewer for you to see yourself.

[Miha Petkovsek]

Hi, sure, I can take a look.

[Miha Petkovsek]

David, can you update to latest dev release and check if it is any better? Will do some improvements tomorrow but I have already reduced lots of unnecessary queries against db.

[phpipam:code] [r432] - myha: Reduced DB queries and implemented basic caching | v 1.0.002

[David Taylor]

I'll run up a parallel instance and see how it goes.

[David Taylor]

Ok have done so and I got an interesting result, users that have Administrative privilege are now quite fast while normal users are still slow.

[Miha Petkovsek]

Ok, at least we are going to right direction :) I updated devel with further improvements, can you check now?

What is the difference now, before you had ~30s, what is the time now?

Also, if you have time I would like you to check queries that take long time to execute... Under functions/dbfunctions.php on line 79 uncomment 3 lines and you should have query logging in /tmp/phpipam_queries.php:
* null the file (>/tmp/phpipam_queries.php)
* reload page that takes long to execute
* save queries to new file (otherwise all other logs also will be logged).
* execute each query individually to see which take long to execute. I suspect I will be the one that has multiple 'or' in statement that is used for usage checking.
* let me know problematic queries (you can send me email)

thanks,
brm

[David Taylor]

I've attached the latest "slow queries" log.

[Anonymous]

We are getting some quite odd results now, My user account was the first created and I don't seem to be having any issues however other accounts seem to be getting very long page load times.
I've actually jumped on a user machine that was getting a very slow response time and logged in with my account and suddenly is very fast so it looks like an issue related to the account. Also all the users are administrators so we should all have the same permissions.
I also dropped one user account and created it from scratch with the same result.

[Anonymous]

OK it looks like the latest dev release fixes these issues.