Databases
StackOverflow2013
Postsdbo
PO8000 updates per second using mysql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO8000 updates per second using mysql
    primarykey
    data
    text
    <p>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.</p> <p>here is what i have. go ahead and tell me how wrong it is.</p> <pre><code>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 </code></pre> <p>a query could be like this:</p> <pre><code>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 </code></pre> <p>indexes are on XXXID and EID</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMchl
    1. USTheDayIsDone
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PO8000 updates per second using mysql
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO8000 updates per second using mysql
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO8000 updates per second using mysql
      1. This table or related slice is empty.
      1. VTUpMod
    1. COupdate mean update or inserting?
      singulars
      1. PO8000 updates per second using mysql
      1. USSvisstack
    2. COIncluding your query and the table structure would go a long way to answering this.
      singulars
      1. PO8000 updates per second using mysql
      1. USAnthony Skipper
    3. COthe queries are basically not the same, they are dynamically built based on what data comes in.
      singulars
      1. PO8000 updates per second using mysql
      1. USTheDayIsDone
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload