12 Replies Latest reply on Aug 22, 2012 9:01 PM by Avi Miller-Oracle

    Using tmpfs as a ramdrive

      Is it a good idea / really bad idea to mount a folder to tmpfs and allocate an Oracle TEMP tablespace in it - on a server with lots of RAM ? Temp IO is a huge bottleneck for us - so having it be supported by RAM should help.

      Obviously we'll have to deal with the fact that tmpfs goes away upon reboot - I am just questioning the idea for starters.

      Thanks !
        • 1. Re: Using tmpfs as a ramdrive
          Oracle database uses the temporary tablespace for sorting operations, in particular if the operation cannot be performed in RAM.

          Putting the temporary tablespace on a RAM disk my give better performance, but I don't see how it will lower your I/O issues.

          I think it will be a better idea to check the instance parameters of the Oracle Database, check the SQL explain plan or use the SQL Advisors to see if there could be a benefit using Indexes, etc. Being able to avoid a full table scan, for instance, can reduce your I/O needs drastically and speedup performance. Do you have any stale statistics? Have you upgrade the Oracle database? There are many factors that can affect database performance.
          • 2. Re: Using tmpfs as a ramdrive
            Not a good idea IMO. You just as well can assign that RAM to the SGA in order to increase the database's buffer cache.

            Or spend that RAM on PGA in order to decrease the number of slow and expensive disk sorts.

            Besides, before addressing performance issues, one need to accurately determine the actual performance problem itself. Too often people run into performance issues, and think that the symptoms seen are the actual problems. And fixing symptoms do not address the root cause problem.
            • 3. Re: Using tmpfs as a ramdrive
              Guys - thanks. Here is the problem. The database is a very large Data Warehouse supporting a BI tool which generates less than optimal SQL - such as DISTINCTing a Terabyte table as step 1 of its analysis. But that's BI - no application - just a black box that generates SQL we have very little control over if you want to truly give the users the power to look at what they need. Very little ability to tune, constantly changing data model, too many indexes necessary to be realistic.

              A somewhat challenging niche (some say punishing ;) ) of the DBA world - but the only practical solution is to massively table-scan and sort/group in a large high-performance TEMP that takes over from when PGA is exhausted. SSD is better than disk - but RAM is better than SSD.

              PGA is great - but you only get 1/8 of the RAM (Linux takes 50%, Oracle gives about 50% to PGA, and a given SQL gets 50% of remaining PGA - so 0.5*0.5*05) - so not the most efficient use of RAM. Giving more RAM to SGA doesn't begin to cache the many TB of data - and tablescans don't cache in SGA anyway.

              So on a server with 2TB of RAM - you might want to give Linux 1/2TB so it can help with the IO, give Oracle AMM 1/2TB so it has some RAM to work with - but giving the final 1TB also to Linux or AMM doesn't buy you much. However - using it for a 1TB RAMDRIVE and putting the TEMP there - might be great.

              At least worth a try. I am just wondering if technically - you see any Linux show stoppers with trying this.

              Thanks !
              • 4. Re: Using tmpfs as a ramdrive
                I don't know how you make up these numbers - they are not default and it all depends on how you configure the database. Full table scans are read into the buffer cache, which is certainly part of the SGA. It is however placed at the end of the LRU list to be aged out quickly.

                I'm sure Linux doesn't care what you store on a tempfs filesystem. You know the information is lost after a system restart or after a system crash. I doubt it will solve your I/O problem, but may increase performance. If you ask for a recommendation, the answer is to tune the database.

                There are also other ways how you can optimize I/O on a Linux system. For instance, using a kernel I/O scheduler that better suits your storage subsystem. You may find the following link intersting:

                I/O scheduler in Oracle Linux 5.7
                I/O scheduler in Oracle Linux 5.7
                • 5. Re: Using tmpfs as a ramdrive
                  Your I/O scheduler post is very good. We already use the deadline scheduler - which is Oracle's recommendation for DW and offers superior IO for scenarios like ours. We don't have a SAN nor SSD - so we haven't tried noop yet.

                  You are correct about buffer cache content being part of the SGA - but for practical purposes - if you have a 50-100TB DB - much of which is being constantly tablescanned - an SGA of a fraction of a TB will not very likely contain the "previous" SQL's data for use by the "next" SQL - defeating the primary value of the SGA - preventing the need to go to disk. Which is why investing much RAM in the SGA isn't that helpful to the cause. PGA is the key buffer. Reading from disk is a foregone conclusion.

                  As for the percentages - they are in fact how it works. Linux by default takes 50% for tmpfs - and giving much of this to the DB shortchanges your IO quite a bit. Of the other 50% given to AMM - the PGA/SGA split will typically hover around 50/50 or 60/40. Of the PGA - if you MONITOR your SQL - you will see that once your SQL needs > 50% of the available PGA - it will spill to Temp. Hence the claim that a given SQL is lucky to get 1/8 of total RAM. Yes - you can change it - but you will be short changing linux, and forgoing AMM which is indispensible in an environment where you don't know what the next dynamic SQL will bring to the table.

                  Yes, a Ramdrive will be lost upon restart. Hopefully on Linux/Unix this doesn't happen often, and the mount / TEMP TBS creation can be automated at startup. This is challenge #2.

                  Challenge #1 is whether OEL will permit Oracle to even create a TEMP TBS on a folder mapped to /dev/shm.
                  • 6. Re: Using tmpfs as a ramdrive
                    Yes /dev/shm is 50 % by default. You need /dev/shm if your database (11g) uses AMM. Since you apparently have such a huge database, it is apparently not a good idea to use AMM at all. AMM can cause a huge performance bottleneck and a lot of unnecessary I/O if your SGA is more than 8 GB. AMM uses 4k page memory. Setup kernel hugepages instead, which uses 2M and thereby drastically reducing the amount of memory pages required to handle the SGA.

                    I suggest you take a look at the below link (answer to your previous post):
                    /dev/shm on Oracle Linux 6.x to run Oracle 11g R2 - manual configuration?

                    Also, check out the following:
                    Director of Linux Sustaining Engineering explain why HugePages is still a good idea, particularly with 11gR2 and large servers:
                    • 7. Re: Using tmpfs as a ramdrive
                      You make a convincing case against the use of AMM on servers with large RAM. So 2 questions -

                      (1) once you go hugepages - the entire Linux memory is hugepages, right? So while hugepages seems beneficial for SGA - is it also beneficial - or at least not detrimental to PGA usage and Linux IO caching?

                      (2) how do you chose the appropriate size for a hugepage given that it can be anywhere from 4K to 256MB?
                      • 8. Re: Using tmpfs as a ramdrive
                        You need to specify the amount of hugepages and restart the system. RAM for kernel hugepages will be reserved at system startup. The remaining RAM will be used as usual. Under Linux x86-64 and x86 the size of hugepages is 2 MB. (256 MB was only with Linux Itanium.)

                        The following link should be useful:

                        • 9. Re: Using tmpfs as a ramdrive
                          I believe tmpfs files do not persist on a reboot. So if you were to create a temporary tablespace on the ramdrive you could end up having to recreate the tempfiles/tablespace every time you rebooted the server.
                          • 10. Re: Using tmpfs as a ramdrive
                            I've heard that in the latest OEL (6+ or UEK2 or both ?) there will not be a need to specify Huge Pages - but the OS will automatically provide the benefits of this feature as if it were specified.

                            Any corroboration to this? Does this mean that AMM will be available in addition to enjoying the efficiencies of Huge Pages? In other words - the special tweaking for Huge Pages will not be necesary any more?

                            • 11. Re: Using tmpfs as a ramdrive
                              You can configure your system to provide tempfs and kernel hugepages, but you cannot create tempfs out of kernel hugepages. So if you have a database configured with AMM it will use tempfs (/dev/shm) otherwise it will use conventional shared memory and thereby kernel hugepages.

                              What you are referring to sounds like Transparent Hugepages, which was introduced in kernel version 2.6.38. As far as I understand, THP can only map anonymous private memory regions such as heap and stack space. When anonymous memory is requested, a reservation is made against swap. From what I understand, it cannot be used for Oracle SGA, which requires shared memory because it needs to be visible to all database sessions.
                              • 12. Re: Using tmpfs as a ramdrive
                                Avi Miller-Oracle
                                user5749629 wrote:
                                Any corroboration to this? Does this mean that AMM will be available in addition to enjoying the efficiencies of Huge Pages? In other words - the special tweaking for Huge Pages will not be necesary any more?
                                You're talking about Transparent HugePages and no, it cannot be used by AMM. You still need to do specific HugePage configuration for Oracle Database.