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))

myisam_sort_buffer_size = 64M
  • 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) Aug 23, 2016 at 19:42

2 Answers 2

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'. Aug 24, 2016 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 Aug 27, 2016 at 17:44
  • "per day" is not relevant. "simultaneously" is what max_connections is limiting.
    – Rick James
    Aug 27, 2016 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, 2016 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. Aug 28, 2016 at 6:05

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,
    ('1656537_0','26862','NOKIA','SAMSUNG Impact','1-0','AMAZON','0','0.79',

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 . Sep 11, 2016 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, 2016 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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