Intro: I have a Debian 8 VPS with SSD and 512MB RAM (1024MB Burst) and use it only with MySQL. I have turned off all unnecessary services and gave every resources on db and system. I have 5 workstations only. The ping and network are stable. 1. The problem is that if I do 10 consecutive queries like this: (SELECT IFNULL(SUM(Qtty), 0) FROM operations WHERE blablabla FROM (goods LEFT JOIN store ON goods.ID = store.GdID) WHERE Deleted <> -1 GROUP BY goods.ID; Randomly 5-6 of them are executed for 2s, but the rest for 8s or more! I see no reason for this behavior. When i activate slow_query_log, it have just the same queries, nothing else. 2. My InnoDB data is 98MB does it make sense to give so much RAM(250MB) to innodb_buffer_pool_size? 3. innodb_log_file_size must be 25% - 50%, which size would be better for me? 4. tmp dir is tmpfs, so if tmp_table_size is on it, does it matter if I give him enough memory? 5. table_open_cache - I have about 200 tables, does it make sense the cache to be 20000 for example, as the mysqltuner advises me? 6. Can i tinning something else? Can somebody help me to find it? My settings: # top Tasks: 19 total, 1 running, 18 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.3 us, 0.0 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem: 524288 total, 524288 used, 0 free, 0 buffers KiB Swap: 1048576 total, 59500 used, 989076 free. 430924 cached Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1 root 20 0 28472 2064 1884 S 0.0 0.4 0:00.61 systemd 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd/4821 3 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper/4821 67 systemd+ 20 0 26132 1020 964 S 0.0 0.2 0:00.11 systemd-network 68 root 20 0 38872 1136 1132 S 0.0 0.2 0:00.00 systemd-udevd 71 root 20 0 28816 1456 1356 S 0.0 0.3 0:00.27 systemd-journal 475 root 20 0 27424 1024 952 S 0.0 0.2 0:00.06 cron 476 systemd+ 20 0 25696 812 808 S 0.0 0.2 0:00.00 systemd-resolve 502 root 20 0 12612 716 712 S 0.0 0.1 0:00.00 agetty 503 root 20 0 14184 764 760 S 0.0 0.1 0:00.00 agetty 512 root 20 0 55136 2592 2472 S 0.0 0.5 0:00.00 sshd 837 root 20 0 82676 3368 3152 S 0.0 0.6 0:00.92 sshd 839 root 20 0 21884 1888 1684 S 0.0 0.4 0:00.12 bash 10921 root 20 0 21812 1456 1452 S 0.0 0.3 0:00.02 mysqld_safe 11131 mysql 20 0 781384 86824 8568 S 0.0 16.6 1:20.40 mysqld 11132 root 20 0 23664 804 800 S 0.0 0.2 0:00.00 logger 11496 root 20 0 82680 4020 3104 S 0.0 0.8 0:00.45 sshd 11498 root 20 0 21884 2276 1700 S 0.0 0.4 0:00.02 bash # my.cnf skip-external-locking skip-name-resolve performance_schema = OFF thread_stack = 192K thread_cache_size = 10 #this is managed globally and is equal to max_connections max_connections = 10 max_connect_errors = 10 connect_timeout = 20 wait_timeout = 20 interactive_timeout = 20 sql_mode = TRADITIONAL default_storage_engine = InnoDB innodb_buffer_pool_instances = 1 innodb_buffer_pool_size = 250M innodb_log_file_size = 100M innodb_strict_mode = ON innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_io_capacity = 2000 innodb_io_capacity_max = 3000 key_buffer = 128K delay_key_write = ON tmp_table_size = 10M # 32M = 53% temp max_heap_table_size = 10M query_cache_type = 0 query_cache_size = 0 table_open_cache = 5000 table_definition_cache = 3000
