0

First time i am sorry about my English , but please help me on my.cnf . I read many post about config Mysql for best optimize but not thing to imporve. My server is Data center that contains two windows and linux . window has 5 core 5 GBram and linux has 18 CPU , 20GB ram . My window has two software that was developed by c# and i am using dll mysql.data . two software ,they will craw data from another website and insert into data to linux , sometime call "select , update , delete" . they work every time about 10s to craw and 10s to craw and send data to linux . On my linux with 358 user online same time , they click on link . another link and stay very long , they call always call to Database every time . My server has 60% select and 40% write . I dont know how to config my.cnf . But some time software in window insert data to table of database on linux very long about 30s and some time it show not respoding . I dont know why and i dont know what parameter make table innodb lock or make innodb can not be inserted by c# . Now i would like to know how can i change and what parameter(variable) to change to get best performance for select and insert into with larger data . Thank you very much . This is my my.cnf ( i am using mysql 5.6.31 - MySQL Community Server (GPL))

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip_name_resolve
skip_external_locking
default_storage_engine=InnoDB
max_connections=20000
join_buffer_size=2M
open_files_limit=65535
query_cache_type=ON
query_cache_size=1G
thread_concurrency=36
thread_cache_size=25
table_open_cache=10048
wait_timeout=300
read_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=1000
long_query_time=7
key_buffer_size=128M
max_allowed_packet=268435456
interactive_timeout=400
myisam_sort_buffer_size = 64M
tmp_table_size=64M
max_heap_table_size=64M
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=512M
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10
innodb_log_buffer_size=64M
innodb_thread_concurrency=0
  • I use mysqlturner.pl to turn mysql it show message . *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 1G) [see warning above] table_open_cache (> 10048) – Christian Aug 23 '16 at 19:42
0
max_connections=20000  -- too large; make it only 200
query_cache_size=1G    -- REALLY BAD! -- make it only 50M
table_open_cache=10048  -- too high; try 3000
long_query_time=7  -- Lower to 2, turn on slowlog, keep an eye on it
innodb_buffer_pool_size=10G  -- OK with 16GB of RAM; not ok with 5GB of RAM

On 100

It is not practical, nor efficient, to try perform 100 queries simultaneously. Because of that, there is no way to "configure" it into existence. It is better to do...

  • Combine all the information into a single AJAX call -- the back and forth for AJAX is non-trivial, possibly even more costly than the database queries themselves.
  • If all the INSERTs are into the same table, then use a single "batch" INSERT with 100 rows. This will run 10 times as fast as a 100 1-row INSERTs.

If it is not 100 similar inserts, please provide more details about the queries.

  • Agree with 'too large; make it only 200'. – Md Haidar Ali Khan Aug 24 '16 at 8:10
  • i know it too larger . but i have 335 connection perday . they make many query per second . I can not manage it . I would like to know how can i prevent lock row on INNODB . i am using innodb but select make it lock so another query can not excute . it make my website load very hight and my cpu increase 300%/18 CPUS – Christian Aug 27 '16 at 17:44
  • "per day" is not relevant. "simultaneously" is what max_connections is limiting. – Rick James Aug 27 '16 at 18:57
  • When CPU is high, we need to look at slow queries and bad indexes. Let's see the most common query, plus SHOW CREATE TABLE and EXPLAIN SELECT .... Lowering the high CPU and high memory usage will both help (indirectly) avoid the locking problem you are experiencing. Once those are take care of, we can get back to the lock problem. – Rick James Aug 27 '16 at 19:00
  • I have query select * from trandaumyao where MaTran = '1643830_0' and so_lan = '16966'; . it take more long time excute so mysql slow log catch this query. – Christian Aug 28 '16 at 6:05
0

Insert query loop each 6 second will insert to db with 356 row :

INSERT INTO trandaumyao(MaTran,MaGiai,Doi1,Doi2,TiSo,ThoiGian,KQ_HDP,KQ_Nha,KQ_Khach,
        Kq_BanThang,KQ_Tren,KQ_Duoi,KQ_1x2_1,KQ_1x2_2,
        KQ_1x2_3,Hiep1_HDP,Hiep1_Nha,Hiep1_Khach,
        Hiep1_BanThang,Hiep1_Tren,Hiep1_Duoi,Hiep1_1x2_1,
        Hiep1_1x2_2,Hiep1_1x2_3,BGColor,KetQua,
        KQ_HDP2,Hiep1_HDP2,so_lan,doi_1_co_the,doi_2_co_the,thong_ke,live)
VALUES
    ('1656537_0','26862','NOKIA','SAMSUNG Impact','1-0','AMAZON','0','0.79',
     '-0.87','2.5','1.00','0.90','1.36','8.70','4.30','','','','','',
     '','','','','liveligh',
     'Draw','','','1','','','1656537','eventId=1656537')

In 6s second to insert and delete . Select query always excute any time by customer . each customer call select

select * from trandaumyao
     where MaTran = '1643830_0'
       and so_lan = '16966';

and bind data to html by Ajax.

Problem happend: tables trandaumyao was locked (row locked) eventhough i use INNODB not use MyIsam, this cause make soft ware leech product from another site have Not Responding and can not insert anything to Database , and customer load query select they take too long time to excute , it make web site load over head CPU raise rapidly .

So i need to config some variables to improve query insert into , and improve select query . i dont know why i have DC with 18CPU and 20 rams but it seem like mini Server. This problem i can not resolve 3 month. Thank you very much.

  • Hi @Rick James. this is work folow between my soft ware and website with 435 customer online every day and make query same time . – Christian Sep 11 '16 at 2:03
  • Do you have INDEX(MaTran, so_lan) (in either order)? That will speed up the SELECT, thereby helping keep it out of the way. – Rick James Sep 17 '16 at 5:06

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.