I’m doing some stress test with MySQL on views…
– i created a chain of very simple views:
CREATE VIEW view1 AS SELECT * FROM `table1`;
CREATE VIEW view2 AS SELECT * FROM `view1`;
CREATE VIEW view3 AS SELECT * FROM `view2`;
….
(table1 contains 1000 records) i created 200 of them and it seems that MySQL manages them very good…
mysql> SELECT empno FROM table1 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view1 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view50 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view100 WHERE birthdate=”1953-04-08″;
2 rows in set (0.00 sec)
mysql> SELECT empno FROM view200 WHERE birthdate=”1953-04-08″;
2 rows in set (0.01 sec)
this were the first executions of the views… the system might have cached something but not a lot…
– then i created other 300 views (all the way to view500)… MySQL tends to panic… lost connection, or sometimes gets extremely slow…
(here i change the date in the WHERE to avoid cache, this is why we have empty set)
mysql> SELECT empno FROM view100 WHERE birthdate=”1953-04-09″;
Empty set (0.00 sec)
mysql> SELECT empno FROM view200 WHERE birthdate=”1953-04-09″;
Empty set (0.01 sec)
mysql> SELECT empno FROM view300 WHERE birthdate=”1953-04-09″;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> SELECT empno FROM view300 WHERE birthdate=”1953-04-09″;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 1
Current database: viewstresstest
ERROR 2013 (HY000): Lost connection to MySQL server during query
I tested a little further and the problem seems to be in the chain somewhere between 200 and 205 views…
My feeling is that the problem relies in the implementation and is not a theoretical limitation of the scalability, cause the performance drop is too sudden to be intrinsic in the approach…
I posted it on the MySQL forum but I’ve got no answer so far…
If someone plans to repeat the test , this is the script for generating the views:
#!/bin/bash
numberofview=$1;
echo “CREATE VIEW view1 AS SELECT * FROM \`table1\`;”;
for((cv=2;$cv<=$numberofview;cv=$(($cv+1))));
do
cvold=$(($cv-1));
echo “CREATE VIEW view$cv AS SELECT * FROM \`view$cvold\`;”
done