`I’m working on a project where we need to process 5000 concurrent pgRouting requests involving PostgreSQL with the pgRouting extension. The requests will mainly involve route planning and shortest path calculations over a large graph. We need to ensure the system can handle high traffic and provide low-latency responses. System Overview: Database: PostgreSQL 12 with pgRouting and postgis Graph Size: around 2 millions Request Type: Each request will perform routing queries (shortest path, multi-stop, etc.) on the graph. Concurrency: Expected to handle 5000 concurrent pgRouting requests. We’re currently considering the following system configuration: CPU: 8 core RAM: 64 GB Key Questions: PostgreSQL Configuration: What are the best PostgreSQL settings to optimize for high concurrency and pgRouting queries? Specifically, we are looking for recommendations on: these are my current settings : max_connections = 5000 shared_buffers = 16GB effective_cache_size = 48GB maintenance_work_mem = 2047MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 4 work_mem = 419kB huge_pages = try min_wal_size = 1GB max_wal_size = 4GB max_worker_processes = 8 max_parallel_workers_per_gather = 4 max_parallel_workers = 8 max_parallel_maintenance_workers = 4 Connection Pooling: Should we use connection pooling (e.g., PgBouncer) to manage database connections? If so, what is the recommended pool size and pool mode? Query Optimization: Are there specific pgRouting query optimizations (e.g., caching query results, query structure, or parameterization) that we should implement to improve the response time for routing queries? Any recommendations on: Hardware Setup: CPU, RAM, storage options for optimal PostgreSQL and pgRouting performance. Database Settings: PostgreSQL configuration tips to handle 5000 concurrent requests with low latency and high throughput. pgRouting Tuning: pgRouting-specific optimizations for large-scale routing.

Setup: SQL Server 2019 on Azure VM. We have an application that, on a specific date, will execute multiple queries to load data into their system. These queries will be run via Databricks using an ODBC connection, and they will primarily consist of SELECT statements with various joins and filters. I'm looking for guidance on how to assess whether the workload generated by these queries could be problematic. Specifically, I want to understand what metrics I should monitor during this workload to evaluate its impact on server performance. Key Questions: 1. How can I determine if the workload of their queries is causing issues? What signs should I look for? 2. How many queries can be run in parallel without negatively affecting server performance? Is there a way to calculate or estimate this? 3. What are the most important statistics to measure during the execution of these queries? How can I compare server performance during the workload against periods without workload?

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.

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 setting up new server and am a little confused in my research of HD configuration. My main question is : 1. Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same? 2. Do Trans Logs and ldf logs write concurrently or one after the other? 3. My plan is to configure it as follows: >C:\ Raid 1, >Data (Raid 10), >Logs (Trans & .ldf) Raid 10, >TempDB (Raid 1), >Local Backups (Raid 10). Does that make sense or should I separate the Trans & .ldf files onto separate raid configs ?

Using MySQL 8.0.30 on Rocky Linux 9 For slow MySQL queries in general, not for a speciffic one, is there a way to tell if query speed was bottlenecked by storage speed, cpu speed, or maybe even ram memory speed ? (mainly **storage vs cpu**). **Maybe there are certain mysql status variables related to tracking this ?** For example I ran a slow query (~30 seconds) while looking at disk iotop read speed, and because I seen it never read faster than around 20% of maximum ssd read speed, I assume that faster disk would not help speeding up my queries too much, and that maybe CPU (processing the data, not reading it) was the bottleneck. **What are some better ways to do similar tests ?** I also tried similar queries on 2 different SSDs, one having double read speed than the other, but I seen no performance differences, I always thought the storage is "weakest link" but now I need to do more tests.

I have a MySQL Database of 250,000 news articles. With time, it's size will continue to increase by at least 250,000 articles per year. I'm doing full text searches on this database to find articles that have keyword matches (from user input) in the article titles and snippets. For example, the query below searches the database for articles containing the keyword "biden". This database query takes 500ms. Some of my more complex queries which return X articles per publisher take longer, like 1800ms. I have a fulltext index made on the "title" and "snippet" column. I'm trying to minimize the query time and get ahead of it now since this database will only get bigger with time. My server is using an HDD, not an SSD. Would switching to an SSD speed up the database queries? I thought since I am using a fulltext index, this index is already stored in the server's RAM and an SSD won't make a difference. Is the fulltext database query I'm performing the optimal way to search and query the database? All input and advice greatly appreciated. SELECT * FROM news WHERE (MATCH(title, snippet) AGAINST("biden" IN BOOLEAN MODE)) ORDER BY datePublished DESC LIMIT 50

Looking for good, but quick references for underlying OS concepts related to SQLOS (Memory Access, Scheduling, etc...)? I am researching SQLOS and how threads and scheduling are handled, but I would like to review some OS concepts and how SQLOS interacts with the environment. I am aware of the Operating Systems Concepts textbook, but I was wondering if there was a good resource available online that is a little more brief and to the point.

We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if there are any **special** settings or strategies that I should employ in either the server/db settings or processing code (stored procs/indexes etc) to ensure that SS takes **best** advantage of the available ram. The DB is about 70GB and it's a non transactional db (it's a data warehouse). So basically large WRITE followed by massive READ is the normal flow of things.

After upgrading our SQL Server's hardware, we noticed in the Windows Task Manager that the SQL instance is only using half of the threads available to it: . The server has the following hardware and software: - Windows 2008 R2 Enterprise 64bit SP1 - Intel Xeon E7-4870 - 4 processors (40 cores, 80 threads) - Microsoft SQL Server **2012 Enterprise Edition (64-bit)** Running select cpu_count from sys.dm_os_sys_info returns 40. The OS sees all 80 threads. Why is only half the server's processing power being used? We have the same hardware and software on two servers and they both exhibit the same behavior.

Apologies if I am posting this to the wrong place. We are unfortunately having to handle our own server administration in-house and no one here is an expert at servers in any way! (we are web app developers and .Net/SQL/javascript programmers) We have a Dell server with a RAID 1 setup on the C: drive (for the Windows Server system) and D: drives (for our SQL databases), which has worked great in the past when 1 of the 2 drives has an issue.. We recently had the C drive fail completely, (BAD_SYSTEM_CONFIG_INFO) and unfortunately the RAID 1 did not help in this case as the system on both active drives were completely unusable (at least to our knowledge). We are back up and running now but it is very time consuming to have to re-install Windows Server, SQL Server and all the updates. My question : Is there an alternative way we could be setting up or backing up the C: drive to avoid having to reinstall everything from scratch, should we have another such failure?

A asp.net page doing very slow to load a list from SQL Server has increased it's performance very much after the whole database has been restored on a new SQL Server. The asp.net app has moved from and to the same servers. The page's performance has improved to something about 1/5 of the previous load time. The new system even has less Memory (12 GB instead of 30 GB). - on the old SQL Server is restricted to use 20 GB of 30 GB and it claims 20 GB. - on the new SQL Server is restricted to use 8 GB of 12 GB and it actually claims 4.5 GB only The new system has an Intel Xeon E CPU while the old has an Intel Xeon X CPU. Users say the performance has improved since the moving in general. Unfortunately I have no benchmark of the page and/or the queries I am investigating on and also not for the involved servers at all. This behavior is the opposite of my expectations! So the question is, what could possibly have improved this? Do statistics possibly play a role here? I would guess they are part of the db and so included in the restored db - or have they been created from scratch? Any thoughts and ideas are very welcome. EDIT 11-March 2016: ---------------------- I tested disk performance of both systems: The old disk performance made 0,3 MB/S to 55 MB/s with Average of 30 Mb/s. The new system shows 141 Mb/s to 1690 Mb/s with average 803 Mb/s. So since the disk performance seems to be more than 10x better, I guess this maybe a good reason for better performance. We are having a lot of disk usage because of bad design (select *) and lots of blobs and (n)TEXT types. So lots of data is not in cache and has to be loaded repetetively from storage. Additional the transaction log has been separated from the data files to another drive (Average 800 Mb/s), this was not the case on the old server). Would anyone agree that we can assume this is (besides all your interesting thoughts) the main reason for improvement?

We are beginning to provision a set of physical servers for a virtual cluster of SQL Server 2016 nodes within VMware. We will be utilizing Enterprise Edition licenses. We plan on setting up 6 nodes, but there is a bit of a debate on what the ideal way to provision the physical servers with regards to CPU clock speed versus CPU core count. I know this is largely dependent on transaction volume and number of databases stored among other software-specific factors, but is there a general rule of thumb that is advised? For instance, is a dual 8-core, 3.2 GHz physical server (16 cores) more preferential to a dual 16-core, 2.6 GHz server (32 cores)? Has anyone come across a white paper that further delves into this type of topic?

Disk (loosely worded to include not only rotational media but non-rotational media [SSD, NVMe, etc.]) drives are continuing to evolve in their underlying formats and hardware. Part of this was an "enhancement" from 512 byte physical sector sizes to 4k physical sector sizes, which changes the on disk layout (512n, 512e, 4kn). This next evolution is in using 8k physical sector sizes, which some manufacturers are starting to produce and setup in production. Given this next step, is the 8k sector size disk supported in Windows? Does SQL Server care about sector sizes?

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.

I'd like to understand how tuning works on instances that are living inside the same machine/VM. I couldn't find a straight answer to the question if properties of each separate instance have some impact on hardware specs of the machine. Here's what I mean: take a server with 8 cores and 24GB RAM, for example, running 2 instances. If I set the max server memory to 10GB for each instance, Cost Threshold for Parallelism to 50 (to enable/set better multithread) and Max Degree of Parallelism to 3 cores, does that mean that both instances will use * 3+3=6 cores so 2 cores for the OS? * 10+10=20GB so +4Gb for the VM? * Cost Threshold for Parallelism so be set to 25+25 ? I haven't found anywhere this kind of info and it would be nice to know this for better customization of instances I currently run, and for future reference as well.

I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually the searches will each query multiple match against statements as I will need to search multiple columns in the table index. The only write queries will be to update a single field of a single record approximately 10 to 20 times per minute. My question is this... How beneficial would it be to setup an 8 drive NVMe raid 0? I know indexes are usually queried from RAM as this is usually significantly faster than storage drives. But with the newest PCIe 4.0 NVMe SSDs and the crazy speeds of these drives, I could probably setup raid0 that has about twice throughout as the fastest DDR4 ram. Or does none if this even matter? Is throughout the factor that even matters? Can you even force indexes to be queried from the storage drive? Thanks in advance for the advice.

I am planning to build a basic server machine dedicated to running MySQL for my small business. What I need the database for... I will be setting up a database to hold records of registered voters in various states for business purposes. Each table will contain the records for registered voters in one state. The average number of records for each table will be approximately 6 to 7 million records. But the largest one will be approximately 17 million records. I will only be doing 2 things with these tables. Querying them to search for a single record, then marking that record in a separate field to indicate that the record has been found. I will have an average of 10 to 20 users (but as many as 100 users) simultaneously running the aforementioned queries. This should result in as few as 100 queries per hour, or as many as 10,000 queries per hour. These queries need to return results as quickly as possible. Preferably in less than 1 second. Or even close to instantaneous as a user is typing in a search box on the front end. I need to know what kind of hardware resources I should purchase with the above requirements in mind. What kind of cpu? How many cpus? How many cores? How much ram? How much disk space? How many drives? What kind of drives? Raid configuration? Also, how much internet bandwidth should I need? Thanks for helping out a noob.

We are in a scenario where we have a rough idea regarding the expected number of transactions per second in the database server. We don't have the actual (custom built) software in hand. When planning for hardware, how do I determine the number of cores for the CPU for the server in which the software will be housed? Is there any guide or best practices from which I can determine the number of cores for the CPU for the latest processor in use right now?

I have a postgres cluster with two databases - Database A uses the default tablespace. It holds important information but has very few writes per day (< 20) and only has a few tables with a few thousand rows of data. - Database B is on its own tablespace on a separate SSD. It has hundreds of GBs of data and adds millions of rows per day. The data is for analytics and is not important. Recently the SSD holding Database B's tablespace died. Postgres will no longer start up. My priority is dumping the data from Database A. I was thinking that because Database A has few writes or deletes per day, it would be fairly safe to run pg_resetwal, then dump Database A. After dumping Database A I would re-install postgres and load the dumped data from Database A. Is there an alternative method to recover my data. Are there any obvious problems with my plan? (I know that pg_barman should be used to prevent these problems, but my client has refused when I suggested pg_barman after a similar failure in the past. And, yes, RAID would obviously be better than a single SSD, but I don't get to make the hardware decisions) Error log when attempting to start postgres:

-03-13 16:20:16.676 PDT  LOG:  listening on IPv6 address "::1", port 5432
2020-03-13 16:20:16.678 PDT  LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-03-13 16:20:16.686 PDT  LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:16.688 PDT  LOG:  database system was interrupted while in recovery at 2020-03-13 16:19:29 PDT
2020-03-13 16:20:16.688 PDT  HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2020-03-13 16:20:17.016 PDT  LOG:  could not stat file "pg_tblspc/16394": No such file or directory
2020-03-13 16:20:17.134 PDT  LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.135 PDT  LOG:  database system was not properly shut down; automatic recovery in progress
2020-03-13 16:20:17.135 PDT  LOG:  could not open directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.136 PDT  LOG:  redo starts at 7C1/93EB0EA0
2020-03-13 16:20:17.136 PDT  FATAL:  could not create directory "pg_tblspc/16394/PG_11_201809051": No such file or directory
2020-03-13 16:20:17.136 PDT  CONTEXT:  WAL redo at 7C1/93EB0EA0 for Sequence/LOG: rel 16394/26819/26877
2020-03-13 16:20:17.136 PDT  LOG:  startup process (PID 57190) exited with exit code 1
2020-03-13 16:20:17.136 PDT  LOG:  aborting startup due to startup process failure
2020-03-13 16:20:17.139 PDT  LOG:  database system is shut down

Posgresql version is 11.2