node.js - MYSQL Read-only DB in memory optimization -


i've created website biochem researchers allows users query specific genes , calculate various statistics between different sets of genes.

the mysql db 16gb , read-only (our lab generated novel data , website portal view data) . i've performance tested website , realized db query slowest portion of app. want put entire db memory ran few problems accepted solutions:

i have 8-core 32gb server use.

option 1: set engine = memory

a few columns of type mediumtext , exceed 64k row limit , refuse put memory engine

option 2: increase innodb_buffer_pool_size

this doesn't seem put data memory. checked used buffer pool using techniques described (https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size) see ~100mbs of used buffer pool. innodb_buffer_pool_size correctly set 24gb.

option 3: create ramdisk , put db on there doesn't seem great option based on few posts.

how should continue? please advise.

have check explain , profiling ?

sample profiling

here can see server spends time , optimize this

mariadb [yourschema]> set profiling=on; query ok, 0 rows affected (0.00 sec)  mariadb [yourschema]> select * 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          | 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]>

Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more