0

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 ?

  • 1
    Can you clarify if CID and CID2 are primary keys? It would also be useful if you could post the execution plan of your query, that is, the result of EXPLAIN SELECT CID ... and even ANALYZE SELECT CID .... – dbdemon Jan 30 at 16:49
  • Why the GROUP BY? – ypercubeᵀᴹ Jan 30 at 20:07
  • Additional information request. Post on pastebin.com and share the links. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Feb 5 at 17:08
  • @dfg ert Let's try a different approach - please post TEXT results of A) SHOW CREATE TABLE cid; B) SHOW CREATE TABLE cid2; C) SHOW INDEX FROM cid; D) SHOW INDEX FROM cid2; E) EXPLAIN SELECT .... Someone will try to assist - but only if you can provide information requested. – Wilson Hauck Feb 26 at 0:35
1

"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.
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.