http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
В комплекте бинарников mysql идет консольная тулза mysqlslap, назначение которой - эмуляция клиентской загрузки. По сути - инструмент нагрузочного тестирования.
Правда, на хостинге у себя я его не обнаружил, что, пожалуй, оправдано
для профилирования:
http://dev.mysql.com/tech-resources/art ... filer.html
Код: Выделить всё
mysql> create view v_client_portfolio_high
-> (
-> client_id,
-> client_first_name,
-> client_last_name,
-> portfolio_value
-> )
-> as
-> select b.client_id,
-> b.client_first_name,
-> b.client_last_name,
-> sum(number_of_units * price) -
-> case
-> (select sum(number_of_units * price)
-> from client_transaction d
-> where d.action = 'sell' and
-> d.client_id = b.client_id)
-> when NULL then 0
-> else
-> (select sum(number_of_units * price)
-> from client_transaction d
-> where d.action = 'sell' and
-> d.client_id = b.client_id)
-> end portfolio_value
-> from client_transaction a,
-> client b
-> where a.client_id = b.client_id and
-> action = 'buy'
-> group by b.client_id,
-> b.client_first_name,
-> b.client_last_name
-> having portfolio_value > 1000000;
Query OK, 0 rows affected (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_client_portfolio_high;
+-----------+-------------------+------------------+-----------------+
| client_id | client_first_name | client_last_name | portfolio_value |
+-----------+-------------------+------------------+-----------------+
| 5 | ABNER | ROSSELLETT | 1252115.50 |
| 500 | CANDICE | BARTLETT | 1384877.50 |
+-----------+-------------------+------------------+-----------------+
2 rows in set (0.47 sec)
mysql> set profiling=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------+
| 0 | 0.00007300 | set profiling=1 |
| 1 | 0.00044700 | select count(*) from client where broker_id=2 |
| 2 | 0.00003400 | set profiling=0 |
| 3 | 0.00007400 | set profiling=1 |
| 4 | 0.63789700 | alter table t engine=myisam |
| 5 | 0.00004000 | set profiling=0 |
| 6 | 0.00007600 | set profiling=1 |
| 7 | 4.01965600 | select * from v_client_portfolio_high |
| 8 | 0.00003500 | set profiling=0 |
| 9 | 0.00007500 | set profiling=1 |
| 10 | 0.45292700 | select * from v_client_portfolio_high |
| 11 | 0.00003800 | set profiling=0 |
+----------+------------+-----------------------------------------------+
12 rows in set (0.00 sec)
mysql> select min(seq) seq,state,count(*) numb_ops,
-> round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,
-> round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu
-> from information_schema.profiling
-> where query_id = 10
-> group by state
-> order by seq;
+-------+----------------------+----------+---------+---------+---------+---------+
| seq | state | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |
+-------+----------------------+----------+---------+---------+---------+---------+
| 0 | (initialization) | 1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |
| 1 | Opening tables | 1 | 0.00024 | 0.00024 | 0.00000 | 0.00000 |
| 2 | System lock | 1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 3 | Table lock | 1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 4 | checking permissions | 1 | 0.00012 | 0.00012 | 0.00100 | 0.00100 |
| 5 | optimizing | 4 | 0.00005 | 0.00001 | 0.00000 | 0.00000 |
| 6 | statistics | 4 | 0.00007 | 0.00002 | 0.00000 | 0.00000 |
| 7 | preparing | 4 | 0.00005 | 0.00001 | 0.00000 | 0.00000 |
| 8 | Creating tmp table | 1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |
| 9 | executing | 11194 | 0.04983 | 0.00000 | 0.01800 | 0.00000 |
| 10 | Copying to tmp table | 1 | 0.00008 | 0.00008 | 0.00000 | 0.00000 |
| 15 | Sending data | 11195 | 0.39853 | 0.00004 | 0.36794 | 0.00003 |
| 22401 | Sorting result | 1 | 0.00375 | 0.00375 | 0.00400 | 0.00400 |
| 22403 | removing tmp table | 2 | 0.00005 | 0.00002 | 0.00000 | 0.00000 |
| 22405 | init | 1 | 0.00002 | 0.00002 | 0.00000 | 0.00000 |
| 22411 | end | 1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 22412 | query end | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
| 22413 | freeing items | 1 | 0.00002 | 0.00002 | 0.00000 | 0.00000 |
| 22414 | closing tables | 2 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 22417 | logging slow query | 1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |
+-------+----------------------+----------+---------+---------+---------+---------+
20 rows in set (0.44 sec)