2

I have a web application that makes simple queries to a MySQL database. The database is quite large (8.4GB) and the time to lookup an entry needs to be as fast as possible.

The database represents a graph theory problem and I'm running a shortest path algorithm that queries it possibly millions of times to find a path. I'm looking at server options to host my app. I expect very light traffic to the site. (Database is well indexed and optimized.)

I know that an SSD server is a must. (Average time with SSD 10-20 seconds on Dell laptop clocked at 1.6Ghz Intel i5, compared to several minute wait on HDD with same hardware)

I consider a 10-20 second wait acceptable and want to ensure that the server can achieve similar results.

My question:

What kinds of CPU speeds/cores should I be looking at? What is the processor clock/cores that will ensure that the SSD is the bottleneck.

I expect that this is varies depending on the nature of the program. If so, how might I go about testing speeds at different clocks, considering I have only one PC?

Improve this question
3
  • Define "quite large". Gigabytes? Terabytes? Is it well indexed? Have you benchmarked to see how long queries take on average on the current hardware available to you?
    – ceejayoz
    Jan 8, 2016 at 2:51
  • 2
    I'd imagine you'll see the most benefits not from SSD vs. HD but from getting enough RAM to fit the entire database into memory. With 8.4 GB that should be very much doable, even with fairly extensive indexing. Have you considered using something specifically designed for graph data like Neo4J though?
    – ceejayoz
    Jan 8, 2016 at 3:02
  • Thanks for the advice I will look into it later. I'm sure there is a way to force the database into memory but as it stands system memory usage never goes above 20% when running the program (4GB RAM). I'd like to refrain form working any further on the program at this time. Getting it hosted someplace takes higher priority. Assuming DB is on SSD what is optimal processor speed or is it negligible?
    – Robert Hosking
    Jan 8, 2016 at 3:15

1 Answer 1

Reset to default
3

MyISAM or InnoDB? Depending on which, you need to see the appropriate cache buffer. If you had 16GB of RAM, you could set that so that the table will (eventually) be entirely in RAM. That would move it from being I/O bound (as it probably is now) to being CPU bound. At that point, an old, slow, cheap, spinning drive will be almost as good as SSDs.

Can you parallelize the algorithm? If so, split it into as many programs (connections) as you have cores. That will (assuming CPU-bound) give you a nice speed-up.

Let's see the main query. And the SHOW CREATE TABLE for any table(s) involved. There may be simple ways to speed it up.

(I estimate that a table of 8.4GB is about the 96th percentile.)

Improve this answer

Your Answer

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