I've created a website for biochem researchers that allows users to query specific genes and calculate various statistics between different sets of genes.
我为生物化学研究人员创建了一个网站,允许用户查询特定基因并计算不同基因组之间的各种统计数据。
The MySQL DB is 16GB and read-only (our lab generated this novel data and the website is a portal to view this data) . I've performance tested the website and realized that the DB query is the slowest portion of the app. I want to put the entire DB into memory but ran into a few problems with the generally accepted solutions:
MySQL DB是16GB并且是只读的(我们的实验室生成了这些新颖的数据,该网站是一个查看此数据的门户网站)。我对网站进行了性能测试,并意识到数据库查询是应用程序中最慢的部分。我想将整个数据库放入内存,但是在普遍接受的解决方案中遇到了一些问题:
I have an 8-core 32GB server for use.
我有一个8核32GB服务器供使用。
Option 1: set ENGINE = MEMORY
选项1:设置ENGINE = MEMORY
A few columns are of type mediumtext and exceed the 64k row limit and refuse to be put into the MEMORY ENGINE
一些类型为mediumtext,超过64k行限制并拒绝放入MEMORY ENGINE
Option 2: increase innodb_buffer_pool_size
选项2:增加innodb_buffer_pool_size
This doesn't seem to actually put the data into memory. I checked the used buffer pool using the techniques described (https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size) but only see ~100Mbs of used buffer pool. The innodb_buffer_pool_size is correctly set to 24GB.
这似乎并没有将数据放入内存中。我使用所描述的技术(https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size)检查了使用的缓冲池,但只看到~100Mbs的使用缓冲池。 innodb_buffer_pool_size正确设置为24GB。
Option 3: Create a RAMdisk and put the DB on there This doesn't seem like a great option based on a few SO posts.
选项3:创建一个RAMdisk并将数据库放在那里这似乎不是一个很好的选项基于一些SO帖子。
How should I continue? Please advise.
我该怎么办?请指教。
1 个解决方案
#1
1
Have you check EXPLAIN and profiling ?
你有没有检查EXPLAIN和分析?
sample for profiling
用于分析的样本
here you can see where your server spends the time and optimize this
在这里,您可以看到您的服务器花费时间并优化它
MariaDB [yourschema]> set profiling=on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [yourschema]> select * from table2 t1 left join table2 t2 ON t1.id <> t2.id;;
+--------+------------+--------+------------+
| id | val | id | val |
+--------+------------+--------+------------+
| 000002 | tabe 2 --2 | 000001 | tabe 2 --1 |
....
| 000005 | tabe 2 --5 | 000004 | tabe 2 --4 |
| 000006 | tabe 2 --6 | 000004 | tabe 2 --4 |
| 000001 | tabe 2 --1 | 000005 | tabe 2 --5 |
| 000002 | tabe 2 --2 | 000005 | tabe 2 --5 |
| 000004 | tabe 2 --4 | 000005 | tabe 2 --5 |
| 000006 | tabe 2 --6 | 000005 | tabe 2 --5 |
| 000001 | tabe 2 --1 | 000006 | tabe 2 --6 |
| 000002 | tabe 2 --2 | 000006 | tabe 2 --6 |
| 000004 | tabe 2 --4 | 000006 | tabe 2 --6 |
| 000005 | tabe 2 --5 | 000006 | tabe 2 --6 |
+--------+------------+--------+------------+
20 rows in set (0.00 sec)
MariaDB [yourschema]> show profile all;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| starting | 0.000113 | 0.000072 | 0.000041 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000006 | 0.000003 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 6051 |
| checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 6051 |
| Opening tables | 0.000307 | 0.000071 | 0.000236 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | 0 | open_tables | sql_base.cc | 4509 |
| After opening tables | 0.000010 | 0.000006 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 4747 |
| System lock | 0.000005 | 0.000004 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 308 |
| Table lock | 0.000010 | 0.000009 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 313 |
| init | 0.000027 | 0.000025 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3427 |
| optimizing | 0.000014 | 0.000012 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize_inner | sql_select.cc | 1092 |
| statistics | 0.000022 | 0.000021 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize_inner | sql_select.cc | 1373 |
| preparing | 0.000117 | 0.000037 | 0.000084 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | optimize_inner | sql_select.cc | 1398 |
| executing | 0.000010 | 0.000004 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec_inner | sql_select.cc | 2551 |
| Sending data | 0.000106 | 0.000102 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec_inner | sql_select.cc | 3223 |
| end | 0.000007 | 0.000004 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3462 |
| query end | 0.000008 | 0.000006 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5687 |
| closing tables | 0.000004 | 0.000003 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | close_thread_tables | sql_base.cc | 935 |
| Unlocking tables | 0.000008 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_unlock_tables | lock.cc | 395 |
| freeing items | 0.000008 | 0.000006 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 7319 |
| updating status | 0.000023 | 0.000008 | 0.000015 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1937 |
| cleaning up | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1956 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
20 rows in set (0.00 sec)
MariaDB [yourschema]>
#1
1
Have you check EXPLAIN and profiling ?
你有没有检查EXPLAIN和分析?
sample for profiling
用于分析的样本
here you can see where your server spends the time and optimize this
在这里,您可以看到您的服务器花费时间并优化它
MariaDB [yourschema]> set profiling=on;
Query OK, 0 rows affected (0.00 sec)
MariaDB [yourschema]> select * from table2 t1 left join table2 t2 ON t1.id <> t2.id;;
+--------+------------+--------+------------+
| id | val | id | val |
+--------+------------+--------+------------+
| 000002 | tabe 2 --2 | 000001 | tabe 2 --1 |
....
| 000005 | tabe 2 --5 | 000004 | tabe 2 --4 |
| 000006 | tabe 2 --6 | 000004 | tabe 2 --4 |
| 000001 | tabe 2 --1 | 000005 | tabe 2 --5 |
| 000002 | tabe 2 --2 | 000005 | tabe 2 --5 |
| 000004 | tabe 2 --4 | 000005 | tabe 2 --5 |
| 000006 | tabe 2 --6 | 000005 | tabe 2 --5 |
| 000001 | tabe 2 --1 | 000006 | tabe 2 --6 |
| 000002 | tabe 2 --2 | 000006 | tabe 2 --6 |
| 000004 | tabe 2 --4 | 000006 | tabe 2 --6 |
| 000005 | tabe 2 --5 | 000006 | tabe 2 --6 |
+--------+------------+--------+------------+
20 rows in set (0.00 sec)
MariaDB [yourschema]> show profile all;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| starting | 0.000113 | 0.000072 | 0.000041 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000006 | 0.000003 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 6051 |
| checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 6051 |
| Opening tables | 0.000307 | 0.000071 | 0.000236 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | 0 | open_tables | sql_base.cc | 4509 |
| After opening tables | 0.000010 | 0.000006 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 4747 |
| System lock | 0.000005 | 0.000004 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 308 |
| Table lock | 0.000010 | 0.000009 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 313 |
| init | 0.000027 | 0.000025 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3427 |
| optimizing | 0.000014 | 0.000012 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize_inner | sql_select.cc | 1092 |
| statistics | 0.000022 | 0.000021 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize_inner | sql_select.cc | 1373 |
| preparing | 0.000117 | 0.000037 | 0.000084 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | optimize_inner | sql_select.cc | 1398 |
| executing | 0.000010 | 0.000004 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec_inner | sql_select.cc | 2551 |
| Sending data | 0.000106 | 0.000102 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec_inner | sql_select.cc | 3223 |
| end | 0.000007 | 0.000004 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3462 |
| query end | 0.000008 | 0.000006 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5687 |
| closing tables | 0.000004 | 0.000003 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | close_thread_tables | sql_base.cc | 935 |
| Unlocking tables | 0.000008 | 0.000007 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_unlock_tables | lock.cc | 395 |
| freeing items | 0.000008 | 0.000006 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 7319 |
| updating status | 0.000023 | 0.000008 | 0.000015 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1937 |
| cleaning up | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1956 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
20 rows in set (0.00 sec)
MariaDB [yourschema]>