What kind of SSD to speed up querying a lot of data?High Cardinality, Time Series Data.. what DB should I...
How do ISS astronauts "get their stripes"?
It took me a lot of time to make this, pls like. (YouTube Comments #1)
Find the next monthly expiration date
Book where the good guy lives backwards through time and the bad guy lives forward
Casually inserting sexuality
Is there any relevance to Thor getting his hair cut other than comedic value?
Is there a low-level alternative to Animate Objects?
How to count words in a line
How to avoid being sexist when trying to employ someone to function in a very sexist environment?
Can I become debt free or should I file for bankruptcy? How do I manage my debt and finances?
Did 5.25" floppies undergo a change in magnetic coating?
What if I store 10TB on azure servers and then keep the vm powered off?
Why does Starman/Roadster have radial acceleration?
How to tighten battery clamp?
Replacement ford fiesta radiator has extra hose
Skis versus snow shoes - when to choose which for travelling the backcountry?
Is divide-by-zero a security vulnerability?
You'll find me clean when something is full
What to do when being responsible for data protection in your lab, yet advice is ignored?
"Murder!" The knight said
When should a commit not be version tagged?
What is the wife of a henpecked husband called?
Does music exist in Panem? And if so, what kinds of music?
Called into a meeting and told we are being made redundant (laid off) and "not to share outside". Can I tell my partner?
What kind of SSD to speed up querying a lot of data?
High Cardinality, Time Series Data.. what DB should I use?Retrieving data speed tweaks SQL Server 2005MySQL on SSD - what are the disadvantages?SSD vs HDD for databasesUpgrading to new cPanel Server with mySQL on SSD — What should I change in my.cnf?MySQL indexes maintenanceintel p3600 SSD no increase in read speed - postgres rdbms vs hddWhat kind of MySQL replication?Will NVME SSD speed up heavy queries, e.g. selects at large offsets / table scans etc?What does “Sending data” mean in the mysql process list?
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an external hard drive. This was sufficient for simple queries. But for more complex tasks the queries take several days. Most of the tasks will be SELECT queries (with multiple joins).
This is why I want to invest into a new internal hard drive (1-2 TB), which will be used in a server setup (128 GB RAM). Due to performance reasons, I think a SSD would be the right choice. The mainboard has both free PCIe x8 and SATA 6.0GB/s slots.
Is there a difference between PCIe SSDs and M.2 SSDs?
What kind of SSD would be the best value for money according to my requirements? I do not want to spend more than 300 $?
mysql query-performance mariadb performance-tuning ssd
asked Feb 20 at 12:57
jstuhhjstuhh
32
add a comment |
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an external hard drive. This was sufficient for simple queries. But for more complex tasks the queries take several days. Most of the tasks will be SELECT queries (with multiple joins).
This is why I want to invest into a new internal hard drive (1-2 TB), which will be used in a server setup (128 GB RAM). Due to performance reasons, I think a SSD would be the right choice. The mainboard has both free PCIe x8 and SATA 6.0GB/s slots.
Is there a difference between PCIe SSDs and M.2 SSDs?
What kind of SSD would be the best value for money according to my requirements? I do not want to spend more than 300 $?
mysql query-performance mariadb performance-tuning ssd
asked Feb 20 at 12:57
jstuhhjstuhh
32
add a comment |
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an external hard drive. This was sufficient for simple queries. But for more complex tasks the queries take several days. Most of the tasks will be SELECT queries (with multiple joins).
This is why I want to invest into a new internal hard drive (1-2 TB), which will be used in a server setup (128 GB RAM). Due to performance reasons, I think a SSD would be the right choice. The mainboard has both free PCIe x8 and SATA 6.0GB/s slots.
Is there a difference between PCIe SSDs and M.2 SSDs?
What kind of SSD would be the best value for money according to my requirements? I do not want to spend more than 300 $?
mysql query-performance mariadb performance-tuning ssd
asked Feb 20 at 12:57
jstuhhjstuhh
32
For research purposes I am using a big MariaDB database (~500 GB) with 20 Tables (engine: myISAM) and around 9 Billion rows. Due to missing free storage on my Laptop (16 GB RAM), I put the data on an external hard drive. This was sufficient for simple queries. But for more complex tasks the queries take several days. Most of the tasks will be SELECT queries (with multiple joins).
This is why I want to invest into a new internal hard drive (1-2 TB), which will be used in a server setup (128 GB RAM). Due to performance reasons, I think a SSD would be the right choice. The mainboard has both free PCIe x8 and SATA 6.0GB/s slots.
Is there a difference between PCIe SSDs and M.2 SSDs?
What kind of SSD would be the best value for money according to my requirements? I do not want to spend more than 300 $?
mysql query-performance mariadb performance-tuning ssd
mysql query-performance mariadb performance-tuning ssd
asked Feb 20 at 12:57
jstuhhjstuhh
32
asked Feb 20 at 12:57
jstuhhjstuhh
32
edited Feb 20 at 13:16
jstuhh
asked Feb 20 at 12:57
jstuhhjstuhh
32
asked Feb 20 at 12:57
jstuhhjstuhh
32
asked Feb 20 at 12:57
jstuhhjstuhh
32
32
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
would be the best value for money according to my requirements
Shopping/recommendations are generally considered off-topic here, so I'll stick with technical differences and not discus value judgements.
Is there a difference between PCIe SSDs and M.2 SSDs?
M.2 is just a form factor: it can hold drives with either SATA or nVME interfaces. A new PCIe based card will almost certainly be nVME but may be a SATA interface and SATA drive soldered to a board.
What kind of SSD to speed up querying a lot of data?
The chief difference between SATA and NVMe drives is the interface speed which imposes the throughput bottleneck. As latency, which affects random access rates, is likely to be similar between the various SSD types you want NVMe (either surface mounted on a PCIe card or in an M.2 slot) for speed.
Of course compared to your current solution with a drive connected via USB, both should be significantly faster.
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
– jstuhh
Feb 20 at 14:19
Probably not but it depends on the adaptor, you'd have to research specific products.
– David Spillett
Feb 20 at 16:13
add a comment |
You can't fix a performance problem by throwing hardware at it.
Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.
In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index. Or even "re-thinking" the problem.
Let's discuss the query and see if it can be sped up for $0.00.
answered 17 mins ago
Rick JamesRick James
43k22259
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230262%2fwhat-kind-of-ssd-to-speed-up-querying-a-lot-of-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
would be the best value for money according to my requirements
Shopping/recommendations are generally considered off-topic here, so I'll stick with technical differences and not discus value judgements.
Is there a difference between PCIe SSDs and M.2 SSDs?
M.2 is just a form factor: it can hold drives with either SATA or nVME interfaces. A new PCIe based card will almost certainly be nVME but may be a SATA interface and SATA drive soldered to a board.
What kind of SSD to speed up querying a lot of data?
The chief difference between SATA and NVMe drives is the interface speed which imposes the throughput bottleneck. As latency, which affects random access rates, is likely to be similar between the various SSD types you want NVMe (either surface mounted on a PCIe card or in an M.2 slot) for speed.
Of course compared to your current solution with a drive connected via USB, both should be significantly faster.
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
– jstuhh
Feb 20 at 14:19
Probably not but it depends on the adaptor, you'd have to research specific products.
– David Spillett
Feb 20 at 16:13
add a comment |
would be the best value for money according to my requirements
Shopping/recommendations are generally considered off-topic here, so I'll stick with technical differences and not discus value judgements.
Is there a difference between PCIe SSDs and M.2 SSDs?
M.2 is just a form factor: it can hold drives with either SATA or nVME interfaces. A new PCIe based card will almost certainly be nVME but may be a SATA interface and SATA drive soldered to a board.
What kind of SSD to speed up querying a lot of data?
The chief difference between SATA and NVMe drives is the interface speed which imposes the throughput bottleneck. As latency, which affects random access rates, is likely to be similar between the various SSD types you want NVMe (either surface mounted on a PCIe card or in an M.2 slot) for speed.
Of course compared to your current solution with a drive connected via USB, both should be significantly faster.
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
– jstuhh
Feb 20 at 14:19
Probably not but it depends on the adaptor, you'd have to research specific products.
– David Spillett
Feb 20 at 16:13
add a comment |
would be the best value for money according to my requirements
Shopping/recommendations are generally considered off-topic here, so I'll stick with technical differences and not discus value judgements.
Is there a difference between PCIe SSDs and M.2 SSDs?
M.2 is just a form factor: it can hold drives with either SATA or nVME interfaces. A new PCIe based card will almost certainly be nVME but may be a SATA interface and SATA drive soldered to a board.
What kind of SSD to speed up querying a lot of data?
The chief difference between SATA and NVMe drives is the interface speed which imposes the throughput bottleneck. As latency, which affects random access rates, is likely to be similar between the various SSD types you want NVMe (either surface mounted on a PCIe card or in an M.2 slot) for speed.
Of course compared to your current solution with a drive connected via USB, both should be significantly faster.
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
would be the best value for money according to my requirements
Shopping/recommendations are generally considered off-topic here, so I'll stick with technical differences and not discus value judgements.
Is there a difference between PCIe SSDs and M.2 SSDs?
M.2 is just a form factor: it can hold drives with either SATA or nVME interfaces. A new PCIe based card will almost certainly be nVME but may be a SATA interface and SATA drive soldered to a board.
What kind of SSD to speed up querying a lot of data?
The chief difference between SATA and NVMe drives is the interface speed which imposes the throughput bottleneck. As latency, which affects random access rates, is likely to be similar between the various SSD types you want NVMe (either surface mounted on a PCIe card or in an M.2 slot) for speed.
Of course compared to your current solution with a drive connected via USB, both should be significantly faster.
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
answered Feb 20 at 13:20
David SpillettDavid Spillett
22.7k23267
22.7k23267
Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
– jstuhh
Feb 20 at 14:19
Probably not but it depends on the adaptor, you'd have to research specific products.
– David Spillett
Feb 20 at 16:13
add a comment |
Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
– jstuhh
Feb 20 at 14:19
Probably not but it depends on the adaptor, you'd have to research specific products.
– David Spillett
Feb 20 at 16:13
Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
– jstuhh
Feb 20 at 14:19
Thanks a lot for your explanation. Will there be performance losses if a M.2 to PCIe adapter will be used instead of mounting it directly in a M.2 slot?
– jstuhh
Feb 20 at 14:19
Probably not but it depends on the adaptor, you'd have to research specific products.
– David Spillett
Feb 20 at 16:13
Probably not but it depends on the adaptor, you'd have to research specific products.
– David Spillett
Feb 20 at 16:13
add a comment |
You can't fix a performance problem by throwing hardware at it.
Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.
In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index. Or even "re-thinking" the problem.
Let's discuss the query and see if it can be sped up for $0.00.
answered 17 mins ago
Rick JamesRick James
43k22259
add a comment |
You can't fix a performance problem by throwing hardware at it.
Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.
In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index. Or even "re-thinking" the problem.
Let's discuss the query and see if it can be sped up for $0.00.
answered 17 mins ago
Rick JamesRick James
43k22259
add a comment |
You can't fix a performance problem by throwing hardware at it.
Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.
In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index. Or even "re-thinking" the problem.
Let's discuss the query and see if it can be sped up for $0.00.
answered 17 mins ago
Rick JamesRick James
43k22259
You can't fix a performance problem by throwing hardware at it.
Even if you can find a fast enough SSD to handle the several-day query today, what will happen tomorrow when the data is bigger and/or you need another JOIN.
In many cases, a "several-day query" can be turned into a "several-hour" query by reformulating the query and/or adding a 'composite' index. Or even "re-thinking" the problem.
Let's discuss the query and see if it can be sped up for $0.00.
answered 17 mins ago
Rick JamesRick James
43k22259
answered 17 mins ago
Rick JamesRick James
43k22259
answered 17 mins ago
Rick JamesRick James
43k22259
answered 17 mins ago
Rick JamesRick James
43k22259
43k22259
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230262%2fwhat-kind-of-ssd-to-speed-up-querying-a-lot-of-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
