mysql - InnoDB performance -

i know there plenty of information on topic, think i've tried can, , don't know new ideas (if any?) increasing mysql database performance.

situation: use etesting platform (taotesting if knows it). uses mysql database, 8 tables. @ moment 1 of tables has ~500k rows. others either empty or has ~10-15 rows. @ first mysql performance terrible using platform, decided convert myisam tables innodb , make my.cnf changes. seemed have improved performance, not as wanted.

server has 1 cpu / 4 cores. 6 gb ram. it's not dedicated mysql, hosts php/apache/nginx.

what's more, there 80% more selects database inserts/updates/deletes.

any ideas how further (if possible) improve mysql configuration welcome .

here's my.cnf:

# # mysql database server configuration file. # # can copy 1 of: # - "/etc/mysql/my.cnf" set global options, # - "~/.my.cnf" set user-specific options. #  # 1 can use long options program supports. # run program --help list of available options , # --print-defaults see understand , use. # # explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html  # passed mysql clients # has been reported passwords should enclosed ticks/quotes # escpecially if contain "#" chars... # remember edit /etc/mysql/debian.cnf when changing socket location. [client] port        = 3306 socket      = /var/run/mysqld/mysqld.sock  # here entries specific programs # following values assume have @ least 32m ram  # formally known [safe_mysqld]. both versions parsed. [mysqld_safe] socket      = /var/run/mysqld/mysqld.sock nice        = 0  [mysqld] # # * basic settings # user        = mysql pid-file    = /var/run/mysqld/mysqld.pid socket      = /var/run/mysqld/mysqld.sock port        = 3306 basedir     = /usr datadir     = /var/lib/mysql tmpdir      = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # # instead of skip-networking default listen on # localhost more compatible , not less secure. bind-address        = 127.0.0.1 # # * fine tuning # key_buffer_size     = 32m max_allowed_packet  = 16m thread_stack        = 256k thread_cache_size       = 50 # replaces startup script , checks myisam tables if needed # first time touched myisam-recover         = backup max_connections        = 200 #table_cache            = 1m sort_buffer_size       = 1m read_buffer_size       = 1m join_buffer_size    = 1m #thread_concurrency     = 8 max_heap_table_size     = 64m tmp_table_size = 64m # # * query cache configuration # query_cache_limit   = 2m query_cache_size        = 2m # # * logging , replication # # both location gets rotated cronjob. # aware log type performance killer. # of 5.1 can enable log @ runtime! #general_log_file        = /var/log/mysql/mysql.log #general_log             = 1 # # error log - should few entries. # log_error = /var/log/mysql/error.log # # here can see queries long duration log_slow_queries    = /var/log/mysql/mysql-slow.log long_query_time = 1 #log-queries-not-using-indexes #log = /var/log/mysql/testing_req_nec.log # # following can used easy replay backup logs or replication. # note: if setting replication slave, see readme.debian #       other settings may need change. #server-id      = 1 #log_bin            = /var/log/mysql/mysql-bin.log expire_logs_days    = 10 max_binlog_size         = 100m #binlog_do_db       = include_database_name #binlog_ignore_db   = include_database_name # # * innodb # # innodb enabled default 10mb datafile in /var/lib/mysql/. # read manual more innodb related options. there many! # # * security features # # read manual, too, if want chroot! # chroot = /var/lib/mysql/ # # generating ssl certificates recommend openssl gui "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem innodb_buffer_pool_size = 4g innodb_thread_concurrency = 8 innodb_log_file_size = 1g innodb_log_buffer_size = 16m innodb_buffer_pool_instances = 8 innodb_flush_log_at_trx_commit = 0 innodb_file_per_table = 1 innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_additional_mem_pool_size = 16m innodb_max_dirty_pages_pct = 90 [mysqldump] quick quote-names max_allowed_packet  = 16m  [mysql] #no-auto-rehash # faster start of mysql no tab completition  [isamchk] key_buffer_size     = 16m  # # * important: additional settings can override file! #   files must end '.cnf', otherwise they'll ignored. # #open_files_limit = 8192 !includedir /etc/mysql/conf.d/ 

edit:

ok, thought actual numbers not needed since said performance got wasn't enough me. i'm using jmeter performance testing. 1 example be: student can login, select test, submit answers, , end test. i've tried doing ~30 students, , here's got:

• login: ~2 seconds (best case scenario), 8s (worst case)

• select test: ~5s - 7s (best case), ~20s - 25s worst case.

• submit answers question: ~5 - 7s (best case), ~30s worst case.

• test ending, same other submits.

see have best case scenario more students (more threads). problem etesting platform doesn't use tradidional relational db model (it uses rdf triples if you've heard) , stores them mysql tables. there lot of queries, 1 submit ~80 queries. sof if test has ~15 items, 1 student sends ~2k queries.

and i've tried explain. can't since can't change etesting platform source code without breaking else, nor can change table structure (besides changing it's engine, maybe indexes?)

edit: submit queries example: http://codeviewer.org/view/code:3d10

tables structure: http://codeviewer.org/view/code:3d11

mysql> show variables '%buffer_pool%'; +------------------------------+------------+ | variable_name                | value      | +------------------------------+------------+ | innodb_buffer_pool_instances | 8          | | innodb_buffer_pool_size      | 4294967296 | +------------------------------+------------+ 2 rows in set (0.00 sec) 

explain on 1 of more complex queries:

explain select count(*) count statements (predicate = 'http://www.tao.lu/ontologies/taodelivery.rdf#deliveryexecutiondelivery' , (object = 'https://etestas.nec.lt/tao_ssl_dev.rdf#i139266227459751316')) , subject in (select subject statements (predicate = 'http://www.tao.lu/ontologies/taodelivery.rdf#deliveryexecutionsubject' , (object = 'https://etestas.nec.lt/tao_ssl_dev.rdf#i1392637693114892'))) , subject in (select subject statements predicate = 'http://www.w3.org/1999/02/22-rdf-syntax-ns#type' , object in ('http://www.tao.lu/ontologies/taodelivery.rdf#deliveryexecution')); +----+--------------------+------------+----------------+---------------+------+---------+-------------+------+-------------+ | id | select_type        | table      | type           | possible_keys | key  | key_len | ref         | rows |       | +----+--------------------+------------+----------------+---------------+------+---------+-------------+------+-------------+ |  1 | primary            | statements | ref            | k_po          | k_po | 990     | const,const |    1 | using | |  3 | dependent subquery | statements | index_subquery | k_sp,k_po     | k_sp | 990     | func,const  |    1 | using | |  2 | dependent subquery | statements | index_subquery | k_sp,k_po     | k_sp | 990     | func,const  |    1 | using | +----+--------------------+------------+----------------+---------------+------+---------+-------------+------+-------------+ 

you can check mysql tuner , tuning primer, both scripts can optimize server configuration