StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A few thoughts:</p> <p>If it's true that the amount of data exceeds your disk write speeds, then you will have to either increase your disk write speeds (eg: RAID, faster disks, ram disks) or distribute the load across many servers. And if scalability is your main concern, then distribution is the key. Unfortunately, I cannot provide more wisdom on the issue than that (Larry K has some links that may help).</p> <p>I can get 30MB/sec sustained write to a 2.5" 7200 rpm drive without trying very hard, so I'd suspect you'd need a lot more search engine queries than "hundreds per second" to exceed that. In any case, most relational databases don't do very well with lots of singe row writes. Here are some alternatives:</p> <ol> <li><p>Investigate if your DBMS supports some sort of batching or bulk insert option (SQL server's BulkCopy classes dramatically improves insert performance). Buffer some items into one batch and write them in the background.</p></li> <li><p>Remove indexes, foreign keys from your table. These slow down inserts.</p></li> <li><p>Minimise the amount of data you need to write. Maybe have one table per half hour of the day, then you won't need to save the timestamp (if your aggregation only needs half hour resolution). Compress the search string (using gzip or even just UTF8 might help). See if using some tricky bit mashing can let you store more data in a smaller space.</p></li> <li><p>Ditch the DBMS altogether. Open a file exclusively and append fixed length records. Rotate the file every half hour. Then get some other process (or even other server) to read these files and aggrigate them as required. All DBMS's lose some performance over plain files because of type checking, parsing, transactions, etc. And if performance is your top priority, then you'll have to do without all the bells and whistles DBMS's provide.</p></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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