Database performance

Yet another performance question: Here is an output of the command: onstat -g wai which prints out all waiting and sleeping threads. It is consistently this length. Question: Is it normal to have this many sleeping and waiting threads, or is this evidence of a bottleneck somwhere? Why are the AIO threads sleeping? Shouldn't they be doing work, and then when they are done, just be terminated? I consistently have 56 AIO threads running. Is that a lot or is that normal. We have about 10GB database with moderate user interaction (only about 30 users on it at peak time). Informix Dynamic Server Version 7.31.UC2A -- On-Line -- Up 1 days 07:58:14 -- 179104 Kbytes Waiting threads: tid tcb rstcb prty status vp-class name 2 10d427b0 0 2 sleeping forever 4lio lio vp 0 3 10d42980 0 2 sleeping forever 5pio pio vp 0 4 10d42b78 0 2 sleeping forever 6aio aio vp 0 5 10d42d70 0 2 sleeping forever 7msc msc vp 0 6 10d42f68 0 2 sleeping forever 8aio aio vp 1 7 10d43250 10c6c018 4 sleeping secs: 1 3cpu main_loop() 10 10d5cd10 0 3 sleeping forever 1cpu sm_listen 11 10d5d7b0 0 2 sleeping secs: 1 3cpu sm_discon 12 10d5dcb0 0 3 sleeping forever 1cpu tlitcplst 13 10d60278 10c6c504 2 sleeping forever 3cpu flush_sub(0) 14 10d60438 10c6c9f0 2 sleeping forever 1cpu flush_sub(1) 15 10d60630 10c6cedc 2 sleeping forever 1cpu flush_sub(2) 16 10d60828 10c6d3c8 2 sleeping forever 3cpu flush_sub(3) 17 10d60a20 10c6d8b4 2 sleeping forever 3cpu flush_sub(4) 18 10d60c18 10c6dda0 2 sleeping forever 1cpu flush_sub(5) 19 10d60e10 10c6e28c 2 sleeping forever 1cpu flush_sub(6) 20 10d61008 10c6e778 2 sleeping forever 1cpu flush_sub(7) 21 10d617c0 0 2 sleeping forever 10aio aio vp 3 22 10d61650 0 2 sleeping forever 11aio aio vp 2 23 10d61a40 0 2 sleeping forever 12aio aio vp 4 24 10d61c38 0 2 sleeping forever 13aio aio vp 5 25 10d61e30 0 2 sleeping forever 14aio aio vp 6 26 10d74658 0 2 sleeping forever 15aio aio vp 7 27 10d74850 0 2 sleeping forever 16aio aio vp 8 28 10d74a48 0 2 sleeping forever 17aio aio vp 9 29 10d74c40 0 2 sleeping forever 18aio aio vp 10 30 10d74e38 0 2 sleeping forever 19aio aio vp 11 31 10d75030 0 2 sleeping forever 20aio aio vp 12 32 10d75228 0 2 sleeping forever 21aio aio vp 13 33 10d75420 0 2 sleeping forever 22aio aio vp 14 34 10d75618 0 2 sleeping forever 23aio aio vp 15 35 10d75810 0 2 sleeping forever 24aio aio vp 16 36 10d75b40 0 2 sleeping forever 25aio aio vp 18 37 10d75a08 0 2 sleeping forever 26aio aio vp 17 38 10d75df8 0 2 sleeping forever 27aio aio vp 19 39 10d78138 0 2 sleeping forever 28aio aio vp 20 40 10d78308 0 2 sleeping forever 29aio aio vp 21 41 10d78500 0 2 sleeping forever 30aio aio vp 22 42 10d786f8 0 2 sleeping forever 31aio aio vp 23 43 10d788f0 0 2 sleeping forever 32aio aio vp 24 44 10d78ae8 0 2 sleeping forever 33aio aio vp 25 45 10d78ce0 0 2 sleeping forever 34aio aio vp 26 46 10d78ed8 0 2 sleeping forever 35aio aio vp 27 47 10d790d0 0 2 sleeping forever 36aio aio vp 28 48 10d792c8 0 2 sleeping forever 37aio aio vp 29 49 10d794c0 0 2 sleeping forever 38aio aio vp 30 50 10d796b8 0 2 sleeping forever 39aio aio vp 31 51 10d798b0 0 2 sleeping forever 40aio aio vp 32 52 10d79aa8 0 2 sleeping forever 41aio aio vp 33 53 10d79e10 0 2 sleeping forever 42aio aio vp 38 54 10d8c630 0 2 sleeping forever 43aio aio vp 37 55 10d8c828 0 2 sleeping forever 44aio aio vp 36 56 10d79ca0 0 2 sleeping forever 45aio aio vp 34 57 10d8ca80 0 2 sleeping forever 46aio aio vp 41 58 10d8cca0 0 2 sleeping forever 47aio aio vp 35 59 10d8ce98 0 2 sleeping forever 48aio aio vp 40 60 10d8d090 0 2 sleeping forever 49aio aio vp 39 61 10d8d3f8 0 2 sleeping forever 50aio aio vp 47 62 10d8d5f0 0 2 sleeping forever 51aio aio vp 48 63 10d8d7e8 0 2 sleeping forever 52aio aio vp 42 64 10d8d288 0 2 sleeping forever 53aio aio vp 44 65 10d8dbd8 0 2 sleeping forever 54aio aio vp 50 66 10d8ddd0 0 2 sleeping forever 55aio aio vp 52 67 10d90138 0 2 sleeping forever 56aio aio vp 51 68 10d902f8 0 2 sleeping forever 57aio aio vp 45 69 10d8da68 0 2 sleeping forever 58aio aio vp 43 70 10d90858 0 2 sleeping forever 59aio aio vp 53 71 10d90a50 0 2 sleeping forever 60aio aio vp 57 72 10d90c48 0 2 sleeping forever 61aio aio vp 55 73 10d90578 0 2 sleeping forever 62aio aio vp 46 74 10d906e8 0 2 sleeping forever 63aio aio vp 54 75 10d90ec8 0 2 sleeping forever 64aio aio vp 49 76 10d91148 0 2 sleeping forever 65aio aio vp 56 77 10d91890 10c6ec64 3 sleeping forever 3cpu aslogflush 78 10d91b78 10c6f150 2 sleeping secs: 34 1cpu btclean 94 10dd4a50 10c70014 4 sleeping secs: 1 1cpu onmode_mon 96 10dd5278 10c6f63c 2 cond wait sm_read 1cpu sqlexec 394 10e3b130 10c6fb28 2 cond wait netnorm 1cpu sqlexec 404 10e697f0 10c709ec 2 cond wait sm_read 1cpu sqlexec 416 10e969d0 10c72c60 2 cond wait sm_read 1cpu sqlexec 419 10e96f20 10c7314c 2 cond wait sm_read 1cpu sqlexec 423 10ed3c78 10c718b0 2 cond wait sm_read 3c

Threads that are marked as "sleeping forever" are basically in a wait state and will be activated by another thread when there is work for them to do. The AIOVP has one thread which is also called aiovp. If the thread is currently active, then it would show up as 'running'. However, if it is not, then it would be sleeping forever. But that doesn't mean that it is really inactive. It could be waiting for an outstanding IO completion. When one of the CPUVPs has a request for some IO, then an entry is put into the IO queue request and the aiovp is woken up. It then starts the process and then sleeps forever (but is really waiting for the IO completion). We don't stop the AIOVP when it is sleeping because the cost of stopping and starting a program far outweighs the cost of simply letting it sit there. Since oninit is a shared executable, all that the AIOVP really needs is the OS memory to manage a process and the space required for the local stack/heap memory. The executable memory space is shared by all of the oninits. There are several onstats (i.e. onstat -g ioq, onstat -g iof, etc.) that you can use to check to see if you really need all of the AIOVPs. It might be that you are overconfigured, but simply because they are sleeping does not mean that they're not doing work. It's just that their code path is rather short. Paul Jazwierski wrote: > Yet another performance question: > > Here is an output of the command: onstat -g wai > which prints out all waiting and sleeping threads. It is consistently > this length. Question: > Is it normal to have this many sleeping and waiting threads, or is this > evidence of a bottleneck somwhere? Why are the AIO threads sleeping? > Shouldn't they be doing work, and then when they are done, just be > terminated? I consistently have 56 AIO threads running. Is that a lot > or is that normal. We have about 10GB database with moderate user > interaction (only about 30 users on it at peak time). > > Informix Dynamic Server Version 7.31.UC2A -- On-Line -- Up 1 days > 07:58:14 -- 179104 Kbytes > > Waiting threads: > tid tcb rstcb prty status vp-class name > > 2 10d427b0 0 2 sleeping forever 4lio lio > vp 0 > 3 10d42980 0 2 sleeping forever 5pio pio > vp 0 > 4 10d42b78 0 2 sleeping forever 6aio aio > vp 0 > 5 10d42d70 0 2 sleeping forever 7msc msc > vp 0 > 6 10d42f68 0 2 sleeping forever 8aio aio > vp 1 > 7 10d43250 10c6c018 4 sleeping secs: 1 3cpu > main_loop() > 10 10d5cd10 0 3 sleeping forever 1cpu > sm_listen > 11 10d5d7b0 0 2 sleeping secs: 1 3cpu > sm_discon > 12 10d5dcb0 0 3 sleeping forever 1cpu > tlitcplst > 13 10d60278 10c6c504 2 sleeping forever 3cpu > flush_sub(0) > 14 10d60438 10c6c9f0 2 sleeping forever 1cpu > flush_sub(1) > 15 10d60630 10c6cedc 2 sleeping forever 1cpu > flush_sub(2) > 16 10d60828 10c6d3c8 2 sleeping forever 3cpu > flush_sub(3) > 17 10d60a20 10c6d8b4 2 sleeping forever 3cpu > flush_sub(4) > 18 10d60c18 10c6dda0 2 sleeping forever 1cpu > flush_sub(5) > 19 10d60e10 10c6e28c 2 sleeping forever 1cpu > flush_sub(6) > 20 10d61008 10c6e778 2 sleeping forever 1cpu > flush_sub(7) > 21 10d617c0 0 2 sleeping forever 10aio aio > vp 3 > 22 10d61650 0 2 sleeping forever 11aio aio > vp 2 > 23 10d61a40 0 2 sleeping forever 12aio aio > vp 4 > 24 10d61c38 0 2 sleeping forever 13aio aio > vp 5 > 25 10d61e30 0 2 sleeping forever 14aio aio > vp 6 > 26 10d74658 0 2 sleeping forever 15aio aio > vp 7 > 27 10d74850 0 2 sleeping forever 16aio aio > vp 8 > 28 10d74a48 0 2 sleeping forever 17aio aio > vp 9 > 29 10d74c40 0 2 sleeping forever 18aio aio > vp 10 > 30 10d74e38 0 2 sleeping forever 19aio aio > vp 11 > 31 10d75030 0 2 sleeping forever 20aio aio > vp 12 > 32 10d75228 0 2 sleeping forever 21aio aio > vp 13 > 33 10d75420 0 2 sleeping forever 22aio aio > vp 14 > 34 10d75618 0 2 sleeping forever 23aio aio > vp 15 > 35 10d75810 0 2 sleeping forever 24aio aio > vp 16 > 36 10d75b40 0 2 sleeping forever 25aio aio > vp 18 > 37 10d75a08 0 2 sleeping forever 26aio aio > vp 17 > 38 10d75df8 0 2 sleeping forever 27aio aio > vp 19 > 39 10d78138 0 2 sleeping forever 28aio aio > vp 20 > 40 10d78308 0 2 sleeping forever 29aio aio > vp 21 > 41 10d78500 0 2 sleeping forever 30aio aio > vp 22 > 42 10d786f8 0 2 sleeping forever 31aio aio > vp 23 > 43 10d788f0 0 2 sleeping forever 32aio aio > vp 24 > 44 10d78ae8 0 2 sleeping forever 33aio aio > vp 25 > 45 10d78ce0 0 2 sleeping forever 34aio aio > vp 26 > 46 10d78ed8 0 2 sleeping forever 35aio aio > vp 27 > 47 10d790d0 0 2 sleeping forever 36aio aio > vp 28 > 48 10d792c8 0 2 sleeping forever 37aio aio > vp 29 > 49 10d794c0 0 2 sleeping forever 38aio aio > vp 30 > 50 10d796b8 0 2 sleeping forever 39aio aio > vp 31 > 51 10d798b0 0 2 sleeping forever 40aio aio > vp 32 > 52 10d79aa8 0 2 sleeping forever 41aio aio > vp 33 > 53 10d79e10 0 2 sleeping forever 42aio aio > vp 38 > 54 10d8c630 0 2 sleeping forever 43aio aio > vp 37 > 55 10d8c828 0 2 sleeping forever 44aio aio > vp 36 > 56 10d79ca0 0 2 sleeping forever 45aio aio > vp 34 > 57 10d8ca80 0 2 sleeping forever 46aio aio > vp 41 > 58 10d8cca0 0 2 sleeping forever 47aio aio > vp 35 > 59 10d8ce98 0 2 sleeping forever 48aio aio > vp 40 > 60 10d8d090 0 2 sleeping forever 49aio aio > vp 39 > 61 10d8d3f8 0 2 sleeping forever 50aio aio > vp 47 > 62 10d8d5f0 0 2 sleeping forever 51aio aio > vp 48 > 63 10d8d7e8 0 2 sleeping forever 52aio aio > vp 42 > 64 10d8d288 0 2 sleeping forever

I guess the question would be more about the number of AIO VPs running (or sleeping). My onconfig file does not have the variable NUMAIOVPS set which according to the documentation would default to 6 (since there are about 24 chunks in the database). So if that was going to be the default why are the 56 AIO threads starting up?

Yes there are multiple AIOVPs per chunk. The reason is that when an IO is issued via an AIOVP, a seek() must first be issued. Therefor there is a short period of time that the AIOVP is 'blocked'. By having multiple AIOVPs, you can statistically have multiple IOs being done per chunk. The algortem for the number created is not exactly 6 per chunk. I went through the algorthem a while back and discovered that it's a bit more complicated than that. While based on the number of chuncks, it also takes into account things like the total number of chunks. If there is only one chunck then use a higher number per chunck, if there are many, then use a lower number per chunck. As Obnoxio indicated, onstat -g iov would show the number of IO operations per AIOVP. This can be used as an indication of how many AIOVPs you really need. What you want to see is somthing that would look like a descenting logarthemic curve. The first three or four should be carrying the majority of the requests, then the number should drop off. When you reach the point where it appears that the VPs are not doing any work, then thats the number of AIOVPs that you probably need to allocate. Remember also, you should be able to dynamically add and drop AIOVPS via onmode. Paul Jazwierski wrote: > I guess the question would be more about the number of AIO VPs running > (or sleeping). My onconfig file does not have the variable NUMAIOVPS set > which according to the documentation would default to 6 (since there are > about 24 chunks in the database). So if that was going to be the default > why are the 56 AIO threads starting up? -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================