All geek questions in one place

8000 updates per second using mysql

I use a 16-core machine with 48 gigabyte of RAM, and I have this data feed where it pumps out about 8000 elements that I need, about 8000 requests per second. mysql is struggling to handle this. the table is not in disk in MEMORY (I try to do something quickly, and the table clears every day anyway), and I was wondering if there is magic my.cnf, I could try to speed it up. he tries to do inserts, and then updates a lot, reading is very small.

that's what i have. go ahead and tell me how wrong it is.

skip-name-resolve thread_cache_size = 128 table_cache = 4096 key_buffer = 256M sort_buffer_size = 10M read_buffer_size = 10M read_rnd_buffer_size = 10M max_allowed_packet = 1M tmp_table_size=256M query_cache_size=256M query_cache_type=1 open_files_limit=8192 thread_concurrency=4 #used for tables in memory max_heap_table_size=4000M log_slow_queries=/var/log/mysql2/mysqld2.slow.log long_query_time=2

The request may be like this:

 UPDATE `pos` SET `ExtID`='16044', `ECNID`='2814858', `Pos`='-100', `LAcct`='-100', `SAcct`='0', `CBasis`='-3515.00', `EPrice`='359.15', `OR`='XYZ', `UID`='123', `Rte`='AR', `XYZRoute`='AR', `PID`='AR', `Time`='09:27:29', `LXYZ`='R: Remove', `LF`='-0.55', `PF`='-0.00', `TF`='-0.73', `OF`='-0.00', `SF`='-0.6896', `CF`='-0.03', `FF`='-0.0075', `OF`='-0.0000', `V`='AR', `ECID`='AR', `PO`='Y', `Sold`='3515.00', `SSold`='100', `OC`='E', `EA`='OV', `S`='AREWAPL', `U`='AESAPL', `A`='12345', `XXXID`='123471' WHERE `XXXID` = '1071' AND `EID` = '1' LIMIT 1

indexes are on XXXID and EID

+4
source share
2 answers
  • Use a prepared statement with host variables.
  • Run multiple threads (otherwise 15 or 16 cores are mostly not used.)
+3
source

Since you have a lot of memory, you can just store incoming data points in memory (not mysql, just in an array using any programming language that you use). Periodically (say, once per hour or something else) unscrew the new thread to load the current memory database into your mysql database.

If you expect to be able to query this dataset in real time via mysql as it loads, you have a slightly more complicated problem.

+1
source

Source: https://habr.com/ru/post/1339257/

More articles:


All Articles