Re: Performance advice

You had a lot of questions here :-) I will leave the tuning options to David, but have some comments: "L.Shaw" <lyndons@enterprise.net> wrote: :We have Informix 7.13 online engine which up until now has been running :perfectly on a single processor sun server. What model? Old Sun servers are slow. :However the demand on the server has increase as users(up to 20 max) are :running large population programs. These jobs are taking hours to run and I :am looking for ways to increase the performance. : :I have already moved the transaction log out of the root dbspace, placing :the even numbered logs on one drive and the odd numbered logs on another :for increased concurrency, as these jobs are all writing to databases with :buffered logging, although it is possible to remove the logging completely :from the databases any referential integrity problems can be resolved by :simply resubmitting the jobs. From what I have heard and seen removing the logging will normaly gain you a relatively small increase in performance. This does of corse assume your transactions are handled ok. They shouldn't be too small (no commit for every row inserted or updated - do it for every 100 to several thousand). :When I analyze the current user sessions (onstat -u) I see that many users :have a status flag of Y and looking in greater detail I see that the :condition they are waiting on is net norm. Fearing the network to be the :bottle neck I ran two experimental jobs, one using a TCP/IP loopback :connection and the other using shared memory. The resulting timings were :very similar. : :As these population applications are using concurrent transactions and :therefore cannot run over shared memory I have changed the NETTYPE for the :VP class to CPU in order to make it higher priority. I have changed the :isolation level of the transactions to dirty read and removed the :constraints from the database. : :I have created indexes on all searchable fields as used in the SQL where :criteria. You do not necessarily need it on all fields, but the right fields. Use "set explain on" to see your results. :I update the statistics every night, free unused shared memory using a CRON :script and defrag the database using dbexport dbimport. oncheck report the :data to be in contiguous space. : :I have written three test programs to insert, select then delete 300 :records from a test table. Each record has a 30K fixed char field(just to :make the database do some work, and because these jobs build 30K fixed char :fields an write them to the database - any advantage to be gained using :Blobs?) and the select statements are aimed at retrieving each numeric :instance in turn by there handle. The delete is basically delete every :record in the table. :One version uses standard selects, the second using prepared statements and :the third using stored procedures and there seems to be little to no gain :from choosing either of these approaches. Infact, to my surprise, over 20 :runs the procedures where slower than the standard select. Stored procedures would normaly only help when they do some processing (several sql statements in each). : Although the :procedures where created and then used for population so I am unsure if I :created the select and delete procedures after population if it would have :made any difference. Another peculiar fact I noticed was that if I created :a standard index on the searchable column in the select, the test program :increased significantly in speed. i.e. from 3000secs to approx. 90 sec. I :then removed the standard unique index I had created and created a :clustered index which I expected to run quicker. To my surprise a clustered :index was about the same as no index at all, I am confused by this result. Are you sure you did update statistics after you created the clustered index and before you run the test? Again did you use "set explain on" to see the results? :What is the internal difference between a prepared select and a store :procedure in there method of execution? Whery little. A stored procedure would normally only help if you can reduce communication traffic between your program and the database engine. This also doesn't have a significant effect unless that communication goes over some kind of a network. If your program and the database engine is on the same machine the difference will not be very large as far as I know. :I have analyzed the queue length using onstat -g ioq and they do not seem :to be growing, nor do the requests to each VP displayed using onstat -g :rea. The disk writes seemed equally balanced across all chunks. : :Many entries in an onstat -u have an address in the wait column, but these :addresses do not map to locks (onstat -k nor latches onstat -s) is this :common? and where should I look to identify the resource indicated by this :address if not here. : :Running test at night I notice a significant decline in performance when :running one job and then running two jobs simultaneously. Sometimes :applications are locking with a condition that indicates they are reading :shared memory (sm_read). If thee jobs are stopped then run again, they very :often work the second time. This may be a locking problem. If you several programs insert into the same table you should make sure you have set it up with row level locking. Page level locking may result in one program locking a page where others also want to insert. If you do updates there are also several locking issues. :The database is installed within the network(Ouch) and I believe we will :get better performance if the database is given its own machine and own :drives to run on. Although I could not possibly quantify the improvement I :would expect. As the database is embodied within the network it is :competing for resources with many other applications and this can only be a :bad thing. You should be able to use normal Unix tools to see how much load there is on the machine where the database is running. If there is any paging (swapping) activity you definitely have a problem. Similarly if the cpu or disk channels are overloaded. : The only consideration I have by separating the database on its :own machine is the ability to run applications through shared memory, but :as I said before there is little difference between this and a TCP/IP :loopback. Yes, and why can't some applications that use the database still run on the same machine? :I am running out of ideas, but cannot believe a database accessed by a mere :20 user, although each one of them is doing batch insert an retrieval could :cause such slow performance. Is this what everyone would expect or do I :have a serious bottleneck in my system and if so how can I identify it. All :suggestions are welcome. You haven't realy said much, if anything about your real performance. I can easily write programs that run for several hours and I would call them fast. You have to describe both your database and what your program are doing in quite some detail for anyone to be able to comment on the performance. 20 users isn't much. : :Thank you for reading this far. :