I've recently increased my General Purpose SSD storage up from 1000Gb to 6000Gb in an accident. Now I'm trying to bring the storage size down to the original 1000Gb but I can't do it over the console. I also tried taking a snapshot and then allocating a smaller storage size down but once the snapshot is restored it goes back to 6000Gb. My next option is to backup 900Gb worth of data from my current db and import into a new RDS instance using workbench import sql data (very slow). Is there something else that I can do to either bring the size down, or a faster way to export and import data from the oversized RDS instance to a new smaller instance?

What are some tunning options that could make MySQL (8.0.36) write more to ram memory and less to SSD ? To have **less SSD wear** not to optimize performance. Do these two affect amount of bytes written to disk at all ? The way I know it, SSDs need to rearange data and constantly move it around when writing new data, so I am thinking if these options delays writes to SSD, then it might write less data ? Since it might already be optimally arranged while in Ram ?

innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_timeout = 3

I also have innodb_buffer_pool_size = 90G What are some [other] variables that I can tune so that it writes less to SSD and more to ram ? Having tables fully stored in memory would be nice, but I think they are too big for that to work properly. The server has 128 GB ram, largest database is around 100-150 GB. Would other variables help ? e.g innodb_log_buffer_size, innodb_redo_log_capacity ?

I've recently come across the notion that a secondary log hard disk can be employed to enhance write operation speed in a database system. I find this concept intriguing but am struggling to grasp the mechanics behind it. Could someone shed light on why a secondary log hard disk is effective in accelerating write operations? Moreover, I've stumbled upon the statement that both SSDs and HDDs can retain all data written, even after a power outage. This caught my attention, and I'm curious to understand the mechanisms that allow both storage types to maintain data integrity post-power outage. If anyone could provide insights into this aspect as well, it would greatly contribute to my understanding.

On a MySQL server (InnoDB tables) with /var/lib/mysql around **350 GB**, I cloned the nvme SSD (using Macrium Reflect) in order to compare query speeds with a faster SSD. But queries are around 4 times slower after cloning to the new disk. I compared by ... 4 computers that have same config and data is sharded equaly, so they all kind of run at same speed, the one that had disk cloned it completes same tasks 4 times slower. I restarted all servers to rule out any existent caching to ram. I also seen that all 3 servers rebooted in seconds, while the one with cloned SSD needed a few minutes to complete some background MySQL tasks before shutting down. But I didn't notice any background SQL tasks running while it was ON. I don't think it was one of those long background processes that reverts long operations that failed. Could **cloning the SSD** cause the data to be somehow **more fragmented** ? I tried to "optimize" tables just to see if it improves anything, but optimizing ~15GB table runs for 24 hours and counting, so that was a bad idea. The SSDs are different, previous one was Kingston DC1000B ("enterprise") and second one is Samsung 980 PRO. From crystaldiskmark benchmark the Samsung should be 2-3x faster reading, 6x times faster writing, double the IOPS on small files.

I am curious what kind of storage hardware is common to see in servers that are dedicated for MySQL ? For example online services like Amazon AWS, Azure, Google, etc that offer(ed) dedicated mysql instances. I know more ram memory always helps, but I am more interested in storage setup. - Is it more common to have mysql on hardware raid cards with many SAS mechanical drives ? - Do they go towards NVME storage ? - Is NVME avoided because of lower endurance and storage wearout ? - Are SAS disks already considered outdated for database servers ? Thank you.

I am working on my local device and have created a schema that makes drive "C:/" (SSD) nearly full (it took up nearly 60 GB from the available space), so I think I should move the data to drive "D:/" (HDD) to free up some space in Drive "C:/" and be able to create more schemas.. The question that comes in my mind before changing datadir value in my.ini file (MySQL Server 5.5) is that: I have many queries that take > 10 minutes to finish, so, does moving the data to an HDD Drive will make the execution time a lot longer than before?, If so, I would not move the data but look for another way to free up the space and be able to create more schemas! --- I am using [UMLS](https://www.nlm.nih.gov/research/umls/licensedcontent/umlsknowledgesources.html) and [restricted](https://www.nlm.nih.gov/research/umls/implementation_resources/scripts/README_RRF_MySQL_Output_Stream.html) to use MySQL Server 5.5 Therefore, I am intrested in the reading speed more than the writing speed.

I'm running MySQL 5.5 on a server with two SSDs in software RAID-1 configuration. The filesystem is ext4. When any process (external to MySQL) produces a large amount of disk IO on the server, I find that MySQL becomes virtually paralyzed, with queries that normally take 30ms taking 10 seconds or more, depending on how long the IO lasts. I can easily reproduce the problem by running a dd write test as follows: dd if=/dev/zero of=tempfile bs=1M count=3024 conv=fdatasync,notrunc This writes a 3GB test file to the same disk and partition where MySQL's data and log files are stored. Here is the output from the command: 3024+0 records in 3024+0 records out 3170893824 bytes (3.2 GB) copied, 16.0938 s, 197 MB/s When I execute the command, I can immediately see queries showing up in the MySQL slow query log. Not surprisingly, they are all insert/update queries, which I assume are waiting to be committed. However, to my huge surprise, the query times are enormous: some are nearly as long as the execution time of the dd command itself! It seems as if MySQL is paused or locked during any intense IO happening on the server. I thought that running MySQL on SSDs in RAID-1 would be exceptionally fast, with other IO operations unlikely to have any large effect on MySQL. 1. Is it normal for a single write process like this to disturb MySQL so profoundly? 2. Should I be looking at problems with the software RAID-1 or the ext4 filesystem? 3. Can MySQL be configured differently? I've read about setting innodb_read_io_threads and innodb_write_io_threads to take advantage of the higher IOPS available with SSDs, but I'm not sure that will solve this problem. 4. Is it necessary to store all MySQL-related files on a dedicated disk to avoid this problem? Thanks very much for any help.

I want to know if it is possible to have a portable Postgresql database on an external hard drive. The idea is to use that hard drive, by plugging it to one of my computers and use the database. *For the moment, it is not possible for me to have a computer that is always running and connecting to it through the network. For some reasons I don't always have access to the same computer but I always have access to my external hard drive. The computers are both running a Linux distribution.*

In SSD, overwriting a data file means SSD has to first erase it and then write data. This can change the data layout in the SSD for the data file. MySQL has some policies to optimize random IO, e.g., read-ahead (pre-fetching). The read-ahead policy will pre-read the remaining x pages in an "extent" if prior y pages are read sequentially. From the perspective of MySQL, using consecutive offsets for pread can make reads sequential. But I doubt is it true for an SSD. In other words, pread can only confirm the **logically sequential** but not **physically sequential**? This points to another important question: does an extent (usually contains 64 pages) in MySQL must represent consecutive space for SSD. So if I am right, why do databases still make a substantial effort in converting random IO to sequential IO?

I have a SQL Server database running on a virtualized Windows Server 2012 R2 (VMware ESXi). I have a task from Accountancy to boost the performance of the database. The database has about 70GB and the RAM allocated is about 90GB. The virtual machine is spining on SAS Disks 10k - RAID10 at the moment. I want to make an upgrade from spinners to SSD but I would like to undertand if this will give a big difference in performance as the uprgade is somewhat expensive as I'm talking about Enterprise SSD. Down bellow I've written what spinners I use at he the moment and the SSD that will be bought eventualy, the SSD's will be used in RAID10. I would like to ask you to help me understand if the upgrade will give me the needed boost in performance? Thank you. Hardware- Server: Lenovo x3650 M5 CPU: 2x - Intel Xeon E5-2630 v4 10Cores 2.2 GHz 25MB RAM: 128 GB Storage: Lenovo Storwize V3700 V2 (SAS Disks 10k - RAID10) **What I Have:** IBM (Seagate ST900MM0168 900GB 10K RPM 12Gbps 2.5" SAS Hard Drive) Brand: IBM Model: 01EJ586 Capacity: 900Gb Interface: SAS 2.5 inch Data Transfer Rate: 12Gb/s Rotational Speed: 10,000rpm External Transfer Rate: 1200 MB/s Sustained Transfer Rate (Outer to Inner Diameter): 215 to 108 MB/s Average Latency: 2.9ms Average Seek Time: 4.6ms Internal Cache: 128Mb Hot-swappable: Yes Caddie: Yes **The Upgrade:** IBM AS7J 1.6TB 12G SAS 2.5" MLC G3HS Enterprise SSD Based on proven HGST Ultrastar SSD1600MM drive technology Uses 20 nm Multi-Level Cell (MLC) NAND flash memory Part number - 2.5" G3HS: 00FN409 Interface: 12 Gbps SAS Capacity: 1.6 TB Endurance (drivewrites per day over 5 years): 10 DWPD Endurance (total bytes written): 29.2 PB Data reliability: 1 in 10(17) bits read MTBF: 2,500,000 hours (0.35% AFR) IOPS reads (4 KB blocks): 130,000 IOPS writes (4 KB blocks): 100,000 Sequential read rate (64 KB blocks): 1100 MBps Sequential write rate (64 KB blocks): 765 MBps Read latency (seq): 100 µs Write latency (seq): 45 µs

I have been given a SQL Server Azure VM that has 512e storage attached for data and log files, according to fsutil fsinfo ntfsinfo [drive letter]:\. It is a DR replication node from an on-premises server with 512 storage drives. The VM is reporting 9012 error, which could be due to the storage or due to the difference between the replication server and the primary server. I am trying to assess if this is a cause for concern for both Availability Groups delay and for optimal SQL Server setup, if I were to turn it into a primary. Everything I am reading seems to say that 512e and SQL Server is not recommended. But the articles are from 2011 - hasn't anything changed in 11 years? Or is 512e still not the way to go. Azure documentation does not mention any of this and I can only gather that 512e may be optional for the new SSD disks. Note: Enabling trace flag 1800 did not resolve the error. Microsoft don't even recommend HDD, listing them as recommended for Dev only now. I am waiting to see if I have ability to open a support ticket, but wanted to know people's experience here too.

I only heard about Robert Martin today, and it seems like he's a notable figure in the software world, so I don't mean for my title to appear as if it's a click bait or me putting words in his mouth, but this is simply how I interpreted what I heard from him with my limited experience and understanding. I was watching a [video](https://youtu.be/Nsjsiz2A9mg) today (on software architecture), on a talk by Robert C. Martin, and in the latter half of the video, the topic of databases was the main focus. From my understanding of what he said, it seemed like he was saying that SSDs will reduce the usefulness of databases (**considerably**). To explain how I came to this interpretation: He discussed how with HDDs/spinning disks, retrieving data is slow. However, these days we use SSDs, he noted. He starts off with "RAM is coming" and then continues by mentioning RAM disks, but then says he can't call it RAM disk, so resorts to just saying RAM. So with RAM, we don't need the indexes, because every byte takes the same time to get. (**this paragraph is paraphrased by me**) So, him suggesting RAM (as in computer memory) as a replacement for DBs (as that's what I interpreted his statement as) doesn't make sense because that's like saying all the records are in-memory processed in the lifetime of an application (unless you pull from a disk file on demand) So, I resorted to thinking by RAM, he means SSD. So, in that case, he's saying SSDs reduce the usefulness of databases. He even says "If I was Oracle, I'd be scared. The very foundation of why I exist is evaporating." From my little understanding of SSDs, unlike HDDs, which are O(n) seek time (I'd think), SSDs are near O(1), or almost random. So, his suggestion was interesting to me, because I've never thought about it like that. The first time I was introduced to databases a few years ago, when a professor was describing the benefits over regular filesystem, I concluded the primary role of a database is essentially being a very indexed filesystem (as well as optimizations, caching, concurrent access, etc), thus, if indexes aren't needed in SSD, this kind of does make databases less useful. Regardless of that though, prefacing that I'm a newb, I find it hard to believe that they become less useful, as everyone still uses DBs as the primary point of their application, instead of pure filesystem, and felt as if he was oversimplifying the role of databases. **Note**: I did watch till the end to make sure he didn't say something different. For reference: [42:22](https://youtu.be/Nsjsiz2A9mg?t=42m22s) is when the whole database topic comes up, [43:52](https://youtu.be/Nsjsiz2A9mg?t=43m52s) is when he starts off with "Why do we even have databases" [This](https://dba.stackexchange.com/a/59831/97975) answer does say SSDs speed DBs up considerably. [This](https://softwareengineering.stackexchange.com/q/73683/168903) question asks about how optimization is changed. To **TL;DR** my question, does the advent of widespread SSD use in the server market (whether it's upcoming or has happened already) reduce the usefulness of databases? It seemed like what the presenter was trying to convey was that with SSDs, one can store the data on disk, and not have to worry about how slow it would be to retrieve it as with older HDDs, as with SSDs, seek times are near O(1) (I think). So, in the event of that being true, that would hypothetically lose one of the advantages it had: indexing, because the advantage of having indexes for faster seek times is gone.

Now that SSD is a standard de-facto. Can the maintenance job of rebuilding indexes and updating statistics, using Ola Hallengren https://ola.hallengren.com/ jobs for instance, be done only on demand instead of regularly such as on weekly basis with Microsoft SQL Server 2017 or lower? If it is still required, what would be the threshold of the database size to decide when it is necessary ?

We recently changed our production database disk to SSD. To clarify, all data paths, caching, tmp, ... directory references in the MySQL configuration point to that database disk, now SSD. The boot disk is still a regular HDD (in GC terms: Standard Persistent Disk). Will the change to a SSD boot disk affect overall performance of our database system? The system is high read, low write; mostly analytics.

I consider to use one of those fancy new SSD-only SAN systems for our new Microsoft SQL Server. "Fancy" means, that it supports / use stuff as block compression / deduplication / 0x00-recognitation / disk space overprovioning etc. When I drop or truncate a table the SQL server usually only marks the pages in the page allocation map as free, but does not touch the data in the pages itself (e.g. overwriting it with 0x00). This is very fine (because fast) with HDDs but SSDs prever to know if a block is free and could be overwritten by its internal routines or if the data are still important and needs to be preserved. So my question: does the Microsoft SQL Server supports the SSD-Trim-Command to deallocate space in some way? Of course without shrinking shrinking the file or doing other manual / slow stuff which fragments my indexes, blocks my queries etc. Or is there a way to force the SQL server to overwrite the unassigned data by 0x00 (which will be recognized by the SAN, so it frees up the space internal), even if this is the worser option compared to the SSD-TRIM? PS: I know that it usually makes sense to have some (!) unassigned space in the database files to use for new data, but just assume that I delete more than I will add in the next 6 months or that I work with partitions and the deleted stuff resists in other files than the current hot data (but the old filegroup is still not empty, so it can't be dropped / shrinked).

I made a web crawler that is split on 3 computers, each of them makes many db queries, I think around 200 **3000-4000 queries per second** (each server) non-stop, with short spikes to 12000-14000. - I am using Centos 7.9 and Mysql 5.7 Community Server. - The database is around 40-60 GB on each server, all tables are **InnoDB** - Total records are 100 million urls split on 3 servers, 100 million "links", 100 million url_meta. - 13 million domains, etc. - Each table is around 15 GB on each server (links, urls, url meta, etc). - CPUs are Ryzen 5 3600 with 12 threads - 64 GB ram on each server - nvme SSDs: - 1x GIGABYTE GP-ASM2NE6500GTTD 500GB (not "enterprise" type) - 2x KINGSTON SEDC1000BM8480G 480GB "enterprise" nvme. My biggest concern now is that **the Gigabyte nvme shows as 30% wear**, after just 3 months. The kingston enterprise ones says they are at 2% raid command **smartctl** says I read around 10 TB and I wrote around 70 TB on each nvme. I had my innodb_buffer_pool_size set to around 1 GB if I remember correctly, I increased it now and forgot previous value :confused: The crawler constantly reads urls to crawl from the table of 30-40 million records on each server, sorts them by last crawl date ASC, reads remote url content, updates url_meta in database (title, description, etc). Updates links found in that url, link titles, etc. This quickly makes the tables very fragmented and unless I run "optimize table", they return queries very slowly. I tried creating a copy of 2-3 most important tables and only update that once a week, and use it for reads, so it remains defragmented. This is when I noticed the worn SSD, the 2 servers with enterprise Kingston nvme completed (copy +optimize tables) in 3 hours, the Gigabyte one in 9 hours. A search query in the crawler is **returned in 10 seconds** using the "live" fragmented tables, **vs around 0.2 seconds** after tables are optimized/defragmented. **What should I do in order to optimize this and avoid destroying the nvmes ?** I am thinking eider: 1) try a hardware setup with HDDs and only use the nvme SSDs for read-only cache. **Do I have a chance to run all these queries from HDDs ?** 2) optimize all the caching options in order to write to disk as rarely as possible. Can I get tips on this please ? 3) just use a SSD with more TBW ? For my second option ... I am not familiar with the tuning options at all, which ones should I look into besides **innodb_buffer_pool_size** ? 32GB out of the 64 GB is a good start for this situation ? And I seen there are some options that control how often the cached data is "flushed" / written to SSD ? Can I get some info on this please ? Ideally I would like it to use the ram as much as possible and write very rarely to SSD. Losing data is not a huge deal but I would lose time while crawling it again. If I switch to HDDs because of all the write commands, would the 64 GB memory help ? Or will the queries become unusable slow ? I seen a raid card with flash cache and HDDs is faster than HDDs alone, but raid card flash cache worns just like the SSDs, no ?! I am kind of lost :/

I have a PostgreSQL12 on Ubuntu 20.04 with following tablespaces: The default uses a HDD and there is also another tablespace on an SSD. Then I run a test to see the differences in insert/update speed for tables placed in different tablespaces: create table test_hdd (v int); create table test_ssd (v int) tablespace ssd1; insert into test_hdd select * from generate_series(1, 10000000); --~10sec insert into test_ssd select * from generate_series(1, 10000000); --~10sec update test_hdd set v = 1; --~25sec update test_ssd set v = 1; --~25sec The expected outcome is that the operations on table test_ssd should be faster, but that is not the case. All the operations on both tables take almost exactly the same amount of time to complete. **My question is: why? How do I get a write speed boost from using an SSD?** *Bonus:* I compared the disk write speed using dd to make sure that the disk for /dev is faster than the HDD disk, and it indeed is. The HDD write speed was around 224 MB/s, and for SSD it was around 1.3 GB/s.

Recently, I've started playing around with an old Raspberry Pi 3 b+, and I thought it would be good practice to host a Postgres database on my local network and use it for whatever I want to work through. I understand that running Postgres on a Raspberry Pi with 1GB of memory is not ideal and can take a toll on the SDcard, but I've updated the postgresql.conf file and specified that the data directory path is to utilize a 1TB SSD. Additionally, I've installed zram and log2ram to try and curb some of the overhead on SDcard. Overview of tech I'm working with: - Raspberry Pi 4 B - Postgres 12 - Ubuntu server 20.04 (no gui, only working from terminal) - 1TB SSD Yesterday, I was writing to the Postgres db from a python notebook without any issue, but once I restarted the Raspberry Pi, I was unable to reach the db from DataGrip and would receive the following error from my terminal in Ubuntu:

psql: error: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I checked the status of the postgres server and that seemed to be alright...:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2021-01-28 13:34:41 UTC; 20min ago
    Process: 1895 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1895 (code=exited, status=0/SUCCESS)

Jan 28 13:34:41 ubuntu systemd: Starting PostgreSQL RDBMS...
Jan 28 13:34:41 ubuntu systemd: Finished PostgreSQL RDBMS.

This is what is provided in the postgresql-12-main.log:

2021-01-28 13:17:23.344 UTC  LOG:  starting PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2021-01-28 13:17:23.362 UTC  LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-01-28 13:17:23.362 UTC  LOG:  listening on IPv6 address "::", port 5432
2021-01-28 13:17:23.365 UTC  LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-01-28 13:17:23.664 UTC  LOG:  database system was shut down at 2021-01-28 01:43:38 UTC
2021-01-28 13:17:24.619 UTC  LOG:  could not link file "pg_wal/xlogtemp.1899" to "pg_wal/000000010000000000000002": Operation not permitted
2021-01-28 13:17:24.670 UTC  FATAL:  could not open file "pg_wal/000000010000000000000002": No such file or directory
2021-01-28 13:17:24.685 UTC  LOG:  startup process (PID 1899) exited with exit code 1
2021-01-28 13:17:24.686 UTC  LOG:  aborting startup due to startup process failure
2021-01-28 13:17:24.708 UTC  LOG:  database system is shut down
pg_ctl: could not start server
Examine the log output.

I think it could have something to do with permissions. If I try uploading a ~30MB csv to the postgres db, it will crash and the server will not start again because files cannot be written to the pg_wal directory. The only file in that directory is 000000010000000000000001 and does not move on to 000000010000000000000002 etc. while writing to a table. Please let me know if you have any questions or if you would like for me to include any additional information. I appreciate any pointers you may have for head ahead of time.

in your experiences does of real world testing of RAID5 come "close" or "far" behind RAID0 in terms of performance as the underlying storage for a heavy write OLTP workload? Please for the sake of the question ignore any concerns of the usage RAID0 because it lacks durability, as this is out of the scope of the question. It can be assumed the environment is virtualized in VMWare, the storage median is SAS SSD, the DBMS SQL Server 2017, the OS for the VM is Ubuntu, there is high bandwidth between storage and guest vm 10Gbps for network and 12Gbps for storage. Thanks you

I understand the double-write-buffer enhances the reliability of data, so it makes transactions slower. But it is amazing that the slow down is such severe in the newest Samsung 980 pro (M.2 PCIe 4.0, which is about 400$ for 1TB). Workload: https://github.com/Percona-Lab/tpcc-mysql Configurations: other parameters are defaults. CPU: AMD Ryzen 3900XT MEM: 64GB, 3200MHz OS: Ubuntu 20.10, all disks are ext4 MySQL: 8.0.22 **The boxplot on the left**: - **Y-axis**: max Latency (ms) of 95% of the transactions in 10 sec period (the lower the better). Each rectangle represents 20 repeated runs (in 200 sec). The red short line in the middle of the rectangle represents the median of the 20 data. The whole rectangle represents for the distribution of the 25%-75% data. The **small circles** are outliers that can be ignored. - **X-axis**: There are 8 rectangles, the first 4 are the latencies on the 4 different disks (which are shown in the legend on the right boxplot) respectively when --innodb-doublewrite=OFF; the last 4 are when --innodb-doublewrite=ON. **The boxplot on the right**: - **Y-axis**: normalize the data in the left boxplot by divining the median of the first 4 rectangles (makes the red short line of them =1). In this way, I can compare the relative performance drop (i.e., how many times do the performance drop, this is what I wrote in the title: "8x" and "2x~3x") after turning on innodb-doublewrite. As shown, the red line in the Samsung 980's rectangle are ~8 times larger after using the doublewrite buffer. - **X-axis**: same as the boxplot on the left. Why does this happen? Did I hit a performance bug? Thanks!