September 8, 2003 at 5:21 am
One of my customers has the need to track search criteria entered into their website.
I proposed that the search parameters be blasted into a simple table and that table have no indices, rules or defaults of any kind for optimum insert performance. The number of search hits are vast.
Once a month this table would be copied out to another server, indexed, aggregated and analysed.
The DBA at the hosting company has told my customer that a inserts against such a table will get massive locking problems unless a clustered index is placed against that table.
Under normal circumstances I would always have a primary key and clustered index no matter what but in this case I want absolute maximum insert performance.
This locking behaviour sounds a bit dubious to me. UPDATEs and DELETEs I can believe but INSERTS?
There is an added complication as this runs on SYBASE.
I would be grateful for your thoughts on this.
September 8, 2003 at 5:53 am
Have never heard such a thing as there is nothing to lock other than the row it is going into. I have a table that processes about one hundred thousand rows a day and is a heap until I transfer the data out and truncate the table on a schedualed basis. Also, I have a table we were using for a log of data transfers that had a clustered index on the IP address of were the data was being routed. The problem was after about 1 million records the service would then hit and error and auto shutdown by the way the code was written. The only successfull index was a clustered on the datetime but I saw a higher IO contention than when we removed even that and created a process to move over. ALso it might be worth noting you see few page splits with a heap as well. It will completely fill before a new page is built where a clustered index will fill and split 50/50 so you end up with a lot of deadspace.
However Sybase should still work the same but I beleive they have some recent engine changes that may do something I don't know about. I suggest checking with a sybase usenet or forum gropu for certainty.
Edited by - antares686 on 09/08/2003 05:53:53 AM
September 8, 2003 at 10:05 am
I agree that you'd want to check with a Sybase person, but I'd be skeptical of these claims. If there is no access other than inserts, any RDBMS should be able to handle this.
How "vast" are we talking? Hundreds/sec? thousands? Do you have any idea of peak v avg numbers?
Steve Jones
September 8, 2003 at 10:45 am
quote:
How "vast" are we talking? Hundreds/sec? thousands? Do you have any idea of peak v avg numbers?
I've seen it peak at 4500/sec and this is for a limited audience.
The nature of the app is that it makes calls to the search facility even though it is not strictly a user search.
There are additional portions of the app coming on line and it is being rolled out to a wider audience before Christmas.
September 8, 2003 at 11:55 am
I guess it depends on the setup of their server, but it's possible this might cause them issues.
However, I'd dispute this and then ask that they upgrade their server if this is truly an issue. Shouldn't be a lock issue though, more likely an I/O issue.
Steve Jones
September 8, 2003 at 1:03 pm
You do have to be careful with heaps to make sure the space gets reclaimed. Had problems a couple years ago, putting a clustered index solved it.
Andy
September 8, 2003 at 1:04 pm
David -
I work with Sybase and SQL2K about equally. I think I know what your DBA friend was talking about.
If you have a table with no indexes, then what you get is a "heap." That means that all inserts go to the page on the end of the chain. Maybe you knew that.
While Sybase does allow row level locking, implementing that imposes overhead (I think everyone agrees with this, the arguments start when you try and determine the significance of the overhead). So the default is page level locking. Page sizes are also variable, up to 16K, but the default is 2K.
Worst case scenario is where you have no index (i.e., a heap table), a 16K page size, inserting a jillion small rows. You can get a lot of contention on that last page. An index is one of several ways of getting past this problem. Partitioning is another. There may be more.
Hope this helps.
September 9, 2003 at 12:11 am
I've had the same issue with a heap as Andy Warren. This was on a SQL7 sp2. Now I only REMOVE a clustered index if it's been proven it hurts performance. If you chose a clustered index, you have to focus on either insert or retrieval performance. Therefor you have to know how your data lives in your db because if updated, it can move around and you'll have to do maintenance.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2003 at 1:42 am
Thanks Tom, that is a very clear description.
Does anyone know if this a generic SQL Server/Sybase issue. I see that alzdba has experienced it with SQL7 SP2 but does it still happen in SQL2K?
September 9, 2003 at 2:37 am
I didn't encounter it in sql2k because I create a clustering index by default, and until now I didn't get any complaints from the app-resp. (Or they can live with it or they don't feel the "overhead")
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2003 at 3:48 am
David: I've had a sligtly similar problem. With undersized DB hardware and oversized userbase. Well, not at all 4.5 k/s but a couple of hundred (which with our configuration at that time was a problem).
We figured out that we had some computers along the way (like IIS servers or proxys)that could store multiple result data in in-memory structures (at those computers), dumping to TXT files and Bulkinsert them when they reached a certain size or at certain time intervals. That solved our problem at that time.
I have no idea if it is useful information and I don't know if it can be applied to SYBASE (this was a SQL Server problem but it should hopefully be solvable in a similar fashion) but I hope so 🙂
Regards, Hans!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply