使用mysql每秒8000次更新 [英] 8000 updates per second using mysql

问题描述

我正在使用具有48GB RAM的16核计算机，并且我具有此数据提要，其中它抽取了大约8000个元素，因此我每秒需要大约8000个查询. mysql正在努力奋斗.该表位于MEMORY中而不在磁盘上(我正在尝试尽其所能使它快速运行，并且无论如何该表每天都会被清除)，并且我想知道是否有魔术my.cnf我可以尝试加速此操作.它尝试插入然后进行大量更新，因此读取的数据很少.

i'm using a 16 core machine with 48GB RAM and i have this data feed where its pumping out about 8000 elements that i need so about 8000 queries per second. mysql is struggling pretty hard on it. the table is in MEMORY not on disk (i'm trying what i can to make it fast and the table gets cleared everyday anyway) and i was wondering if there is a magic my.cnf i could be trying to speed this thing up. its trying to do the inserts and then updates a lot, the reads are very little.

这是我所拥有的.继续告诉我这是怎么回事.

here is 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

查询可能是这样的:

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

索引位于XXXID和EID

indexes are on XXXID and EID

推荐答案

1. 使用带有宿主变量的预处理语句.
2. 运行多个线程(否则15个线程或16个内核基本上未使用.)

这篇关于使用mysql每秒8000次更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！