Question :
i have two table in my mariadb database, A and B :
my request is very slow 3 min 18.028 sec min with index.
sql request :
select Cid,time,step,cpu
from A,B
where B.CID2=A.CID
group by CID;
first table A :
+------+---------------------+--------------------+-----------------+
| CID | time | step | time_in_seconde |
+------+---------------------+--------------------+-----------------+
| 3 | 2017-07-27 06:35:52 | gege | 13.229 |
| 4 | 2017-07-27 06:36:56 | titi | 12.823 |
| 5 | 2017-07-27 06:55:04 | fefe | 12.667 |
344039 rows in set (1.239 sec)
second table B ( :
+------+---------------------+-----------------+
| CID2 | time | cpu |
+------+---------------------+-----------------+
| 3 | 2017-07-27 06:35:52 | 0.01 |
| 4 | 2017-07-27 06:36:56 | 0.05 |
| 5 | 2017-07-27 06:55:04 | 0.03 |
51639 rows in set (0.181 sec)
request result:
+------+---------------------+--------------------+-----------------+-----------------+
| CID | time | step | time_in_seconde | cpu |
+------+---------------------+--------------------+-----------------+-----------------+
| 3 | 2017-07-27 06:35:52 | gege | 13.229 | 0.01 |
| 4 | 2017-07-27 06:36:56 | titi | 12.823 | 0.05 |
| 5 | 2017-07-27 06:55:04 | fefe | 12.667 | 0.03 |
405 rows in set (3 min 18.028 sec)
mariadb config:
+--------------------------------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------------------------------+----------------------+
| aria_block_size | 8192 |
| aria_log_file_size | 1073741824 |
| aria_max_sort_file_size | 9223372036853727232 |
| aria_pagecache_buffer_size | 134217728 |
| aria_pagecache_file_hash_size | 512 |
| aria_sort_buffer_size | 268434432 |
| binlog_cache_size | 32768 |
| binlog_file_cache_size | 16384 |
| binlog_stmt_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| delayed_queue_size | 1000 |
| histogram_size | 0 |
| host_cache_size | 279 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size | 536870912 |
| innodb_change_buffer_max_size | 25 |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_log_buffer_size | 16777216 |
| innodb_log_file_size | 50331648 |
| innodb_log_write_ahead_size | 8192 |
| innodb_max_undo_log_size | 10485760 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 300 |
| innodb_sort_buffer_size | 1048576 |
| innodb_sync_array_size | 1 |
| join_buffer_size | 134217728 |
| key_buffer_size | 134217728 |
| key_cache_block_size | 1024 |
| key_cache_file_hash_size | 512 |
| large_page_size | 0 |
| log_tc_size | 24576 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_heap_table_size | 16777216 |
| max_join_size | 18446744073709551615 |
| max_long_data_size | 16777216 |
| max_relay_log_size | 1073741824 |
| metadata_locks_cache_size | 1024 |
| mrr_buffer_size | 262144 |
| myisam_block_size | 1024 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_sort_buffer_size | 134216704 |
| performance_schema_accounts_size | -1 |
| performance_schema_digests_size | -1 |
| performance_schema_events_stages_history_long_size | -1 |
| performance_schema_events_stages_history_size | -1 |
| performance_schema_events_statements_history_long_size | -1 |
| performance_schema_events_statements_history_size | -1 |
| performance_schema_events_waits_history_long_size | -1 |
| performance_schema_events_waits_history_size | -1 |
| performance_schema_hosts_size | -1 |
| performance_schema_session_connect_attrs_size | -1 |
| performance_schema_setup_actors_size | 100 |
| performance_schema_setup_objects_size | 100 |
| performance_schema_users_size | -1 |
| preload_buffer_size | 32768 |
| profiling_history_size | 15 |
| query_alloc_block_size | 16384 |
| query_cache_size | 0 |
| query_prealloc_size | 24576 |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| rowid_merge_buff_size | 8388608 |
| sort_buffer_size | 2097152 |
| thread_cache_size | 4 |
| thread_pool_size | 2 |
| tmp_disk_table_size | 18446744073709551615 |
| tmp_memory_table_size | 16777216 |
| tmp_table_size | 16777216 |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| wsrep_max_ws_size | 2147483647 |
+--------------------------------------------------------+----------------------+
my config:
[root/]# free -m
total used free shared buff/cache available
Mem: 3788 1177 143 10 2467 2326
Swap: 1535 284 1251
processor : Intel Celeron_4x0 (Conroe/Merom Class Core 2)
my question is how to increase the speed of my request ?
Answer :
“You can’t tune your way out of a performance problem.” Indexes are the key.
Rewrite the query using the modern JOIN..ON:
select Cid,time,step,cpu
from A
JOIN B ON B.CID2=A.CID
group by CID;
You need these indexes (or PRIMARY KEYs):
A: (CID)
B: (CID2)
Also, it is unclear why you need the GROUP BY at all. In fact, it may be ‘incorrect’. Please elaborate.
To discuss further, please provide
SHOW CREATE TABLE -- for each table
EXPLAIN SELECT ... -- for the query.
Suggestions to consider for your mariadb my.cnf [mysqld] section
REMOvE or lead with # 20190309 the following 3 items
join_buffer_size of 128M
thread_cache_size of 4
thread_pool_size of 2
to allow defaults to work for you.
The join_buffer_size is per CONNECTION and not reasonable.
The other two are too restrictive for a reasonable sized server, see refman.