Posted by Avatar-X So, let's focus on the forum readstates for the moment because they represent only two tables and are much simpler. One of the key problems with the speed is generating the 'category' page (the front page of the forum). In order to get the proper read states, it needs to total up all of the readstates for ALL of the threads in each category, essentially this is all of the unarchived threads on the whole site. These are then joined to the read table. Shrinking the size of the read table will help speed this up, but it is still a LOT of rows to tot up each time. Any ideas on how to speed this up? -av

Without much thinking, one idea off the top of my head is simultaneously use two types of methods in calcuating readstates. A potential new way would be to:

-Create some kind of server-side process that monitors posts and new threads in a category. Create a timestamp field for that category which gets updated with the last time a new thread is started in that category or there is a new posts. This is not user-specific, but for UER as a whole.

-Where ever the user's readstate data is stored also create a timestamp field to log the last time the user was in that specific category. (Perhaps a table specifically dedicated to user readstate timestamps would work better)

-Now when the user returns to the main category page compare the two time stamps for a category. If there has been a new post in the category, the servers 'last new thread in this category' time stamp will be greater than the timestamp logged in the user's read-state data for the last time they were in there thus triggering the green icon. If there have been no new threads, then the users timestamp for that category would be greater than the last new thread time stamp triggering the grey/no new threads icon.

A similar process could be used to check if there's been new posts as well by using the timestamp for the last time a user was in a category compared to another timestamp field for what the server as a whole see the last time a post was added in that category. Then do a comparison between the two.

If an idea like this were implemened, I think it might be able to minimally on the main category screen be able to tell the readstate of a category as a whole with out have to look at every single thread in there. Then when the user goes into that category, then the traditional readstates process can run but now at least the server is only processing readstates for that specific category, not all of UER each time a user load the main page.

Does that make sense?


One negative I can see with this idea though is that pretend on the main category page your favorite category has a new thread posted. You click on that category but you do NOT view that new thread started. Since you visited that category your timestamp has been updated and is not greater than the 'new thread timestamp' for that category, but if you haven't viewed that thread the next time you're on the main category page, pending no other new threads be added, the comparison of the timestamps is showing that there no longer are new threads.

There might be a creative way to tweak the process to prevent that though....


I guess what I'm getting at is on the main category page only use a very basic read state system for categories, like is used on other commercial forum packages. Then you at least know if there's new posts or threads in a category. Then when inside that category, let UER's old read-state system shine. Hopefully if the intensive read states only need to be run inside a category at a time and not UER as a whole there'll be a lighter load.

Posted by Avatar-X I'm not sure where this idea came from, but the two are not one and the same. Giving up LDB read states won't solve the problems of the forum read states.

While understanding they are completely separate animals, from what I'm reading it sounds like that the LDB side of it is pretty intensive to run and keep updated. The assumption is that if the LDB no longer needs to be processed for readstates then the forum read-states will have more available system resources and run more smoothly. If you're saying it doesn't make a difference though, then it doesn't make a difference.



As for Google, I couldn't tell you. I'm sure they're doing something tricky and them simple using s more powerful server cluster for it. They're too smart to waste resources.

_{I also just looked at Google's reader for the first time. I've just been using their iGoogle page for RSS viewing/management. The relevant RSS feeds it defaulted me to were astonishing: It's all content I regularly would view on the internet: LOL cats, politics, and crap from reddit/digg.}

Your idea about the dates on the category wouldn't work, as there would be no way for UER to know if you still had unread posts/threads.

Example:
You read the category on Monday.
New post on Tuesday
New post on Wednesday
You come back Thursday, read the Wednesday post. Even if your "current" date is set to Wednesday (and not Thursday), when you go back to the front page, the "Tuesday" post is no longer counted.

-av

Posted by Avatar-X Your idea about the dates on the category wouldn't work, as there would be no way for UER to know if you still had unread posts/threads. Example: You read the category on Monday. New post on Tuesday New post on Wednesday You come back Thursday, read the Wednesday post. Even if your "current" date is set to Wednesday (and not Thursday), when you go back to the front page, the "Tuesday" post is no longer counted. -av

Yah, my previous post was kind of wordy, but I pointed that out as well. There'd need to be a little bit more done in some tricky way to prevent what you described from happening.

--------

Another wild idea is to create an incremental counter within each category which is increased by 1 upon a new thread being created. A user also has a field available on teh readstate table which acts as a counter and is incremented by 1 upon the user reading a new thread in that category.

If the number of threads = the number the user has read for a category, then display no new threads (gray).

If the number of threads > the number the user has read for a category, then display new threads (green).

If the user clicks the 'clear read state' button for a category, then set the users counter equal to the current value of the thread counter make it appear as there are no new threads.

I believe this might also work for individual posts as well using the same/similar method. Special handling of deleted/removed threads/posts would be required. Of course this would be for the main category page. Inside a category, run the traditional UER readstates.

What about that? Would it work?

This is similar to how the readstates work in the LDB.

The problem with this approach is that it royally screws up if a post is deleted, moved, or merged. For the global total, it is easy to simply the count by one. But, for each user's total, you would need to only decrement the count by one if they had read that thread. Doing this for all users when deleting a thread is slow. Doing it on a schedule to "resync" things is also slow.

-av

Posted by Avatar-X I am extremely grateful for your suggestions. I originally wrote all of this readstates stuff 7~ years ago, when I was 21, with no DB experience or degree or anything. I started this thread to see if people who do this for a living have better ideas than I did 7 years ago, but so far the only really workable solution has been the sharding, and that has its own problems too.

Something that will be a major problem in other people providing assistance is that you're using a web programming technology that pretty much nobody uses anymore which can be extended by components that almost nobody writes anymore (ActiveX is so 2002).
Many scalability/design improvements require features that simply don't seem to be available to you.
I'd be willing to help you rewrite some or all of the board in a decent/modern programming language which would allow you to use some of the other suggestions, but I'm sure you have enough code that that would be a fairly ostentatious undertaking.
I've also been looking for a mobile app to develop since I now have an iPod touch, an iPad, and a Droid X. It might be fun to build a REST interface of some sort if you don't have one already.

I wonder how Google does it -- in my Google reader, I have about 50 RSS feeds. Google knows exactly which posts I have read and which I haven't, and can display that information instantly. They can even tot it up into an "all unread items" count. So whatever system they've implemented, it's got item-level granularity and works very quickly.

I'm not sure which backend Google use for reader (they're tight-lipped about that sort of thing, but I've found reference to them calling it "reactor"), but google use massive clusters of systems for most of their stuff. They're operating at a completely different scale from UER.

From the non-DBA perspective, I'm willing to bet that UER's IO performance would improve greatly on a Server 2008 R2 platform.

We recently upgraded all our SQL backend (Dynamix AX ARP system for retail warehousing) from 2003 X64 to 2008 R2 with a 15% improvement on disk IO and transactional throughput, without touching a single line of code.

I've had a good bit of experience of DB type applications (SQL and Exchange mostly) on Server 2003 X64 and have always found it severely lacking in the responsiveness department. And the stability department. And the driver support department.

Also, as an additional up, I belive IIS 7 is considerably faster on S2008R2 at the basic task of serving web pages.

A 2008 upgrade is expensive though and there are probably code based solutions which would provide more immediate gain to look at first.

Chris...

Posted by TurboZutek From the non-DBA perspective, I'm willing to bet that UER's IO performance would improve greatly on a Server 2008 R2 platform. We recently upgraded all our SQL backend (Dynamix AX ARP system for retail warehousing) from 2003 X64 to 2008 R2 with a 15% improvement on disk IO and transactional throughput, without touching a single line of code. I've had a good bit of experience of DB type applications (SQL and Exchange mostly) on Server 2003 X64 and have always found it severely lacking in the responsiveness department. And the stability department. And the driver support department. Also, as an additional up, I belive IIS 7 is considerably faster on S2008R2 at the basic task of serving web pages. A 2008 upgrade is expensive though and there are probably code based solutions which would provide more immediate gain to look at first. Chris...

2008 R1 is pretty cheap on AWS. You can run a Windows micro instance 24x7 for about $22/month.
http://aws.amazon.com/ec2/#pricing

An instance of Amazon RDS (pre-configured MySQL running on EC2) will run around $80/month for on-demand pricing.
http://aws.amazon.com/rds/#pricing

That'd put you at pretty close to the $100 you've mentioned you pay now in hosting fees, but would give you greatly improved resources and let you stop caring about ever upgrading your server.
Even better, since you're likely to keep UER around for at least another year, you could go with reserved pricing for each -
$227.50 for a year of RDS
$54 for a micro instance
$227.50 for a small instance (if micro isn't enough)

So for <$300/year (or $500 if you go bigger), you would have more capacity than now and it's likely that you would see much better performance to boot.

Edit:
Also note that they're running a deal right now where you can get a Linux micro instance free for a year. It isn't directly helpful for any of your current problems, but you could potentially migrate some of your overnight housekeeping to run from cron on Linux. I could help with that too.

I don't understand this RDS stuff... what are you talking about?

Something that will be a major problem in other people providing assistance is that you're using a web programming technology that pretty much nobody uses anymore which can be extended by components that almost nobody writes anymore (ActiveX is so 2002).

*shrug* Newer is not always better. The problems I am having stem from the database, not from the ASP technology.

-av

Posted by EatsTooMuchJam 2008 R1 is pretty cheap on AWS.

Problem with R1 of course though, is that it's shite.

Chris...

Posted by Avatar-X I don't understand this RDS stuff... what are you talking about?

There was a link in there. RDS is a managed MySQL instance running in the AWS cloud. You'd run a Windows instance with the application and an RDS instance to hold the database.
Then you pay Amazon <$500/year, you have better specs than your system currently has, and you never have to pay for a power supply replacement again.

*shrug* Newer is not always better. The problems I am having stem from the database, not from the ASP technology.

While I agree that the root cause of your performance problems is the database itself, the ASP technology becomes problem when it becomes a roadblock to implementing fixes.

It occurred to me I haven't been clear about my assumptions and why I'd be suggesting AWS and some of the lazy write/caching scenarios that I did earlier.

1) UER currently runs on a SunFire X2100
2) The X2100 could take up to 4G of RAM and up to 4 cores of 64-bit Opteron (2 processor sockets), but the standard package was usually 2G and 1-2 cores.
3) The X2100 can accomodate two internal 3.5" SATA drives with no option for SAS. In general, SATA drives max out at 7,200 RPM.
4) UER is consequently most likely running on a 2-core AMD64 system with 2-4G of RAM and a pair of 7200RPM hard drives mirrored.
5) Since your database tables are too big for the cache to hold in RAM (you mentioned one table was 2.5G), you will need to do real i/o for any sort of larger query.
6) A typical 3.5" 7200RPM SATA hard drive has a sustained transfer rate of ~125MB/s for sequential transfer (which is likely if your disk isn't too fragmented). Most modern hardware and software mirroring technologies are smart enough to distribute reads between drives (writes still need to happen simultaneously to both) so you're looking at 250MB/s of sustained throughput for any query. If you need to do random I/O, you're going to be in hell quickly, though, as the seek time on a 7200RPM SATA drive tends to be in the 8-10ms range and your throughput will likely drop a lot. I'd expect 40MB/s, realistically for a mixed workload.
7) If a single db table that you're dealing with is 2.5G in size, that would then take 10 seconds to perform a full table scan under ideal conditions (2500/250) and possibly more like a minute under less ideal conditions.
8) While indexes are going to help you a lot with the query time to discover where the data is within a table, you may then need to perform random I/O to retrieve all of the values (or sequential I/O and throw out what you don't need).
9) The database is going to do its best to figure out what data is accessed frequently and hold it in RAM, but if your data set is vastly in excess of what RAM can hold, this can be both useless and can even become detrimental to performance.
10) Your database has fairly constant writes happening due to post and updating read states. Frequently, update/insert operations invalidate MySQL query caches for the table being written to which limits the utility of #9. All of those writes also incur random I/O. Since RDBMS solutions write data synchronously (part of ACID compliance), users must wait for these writes to be complete if they're being done synchronously in your code. Also, they may interrupt larger reads/joins and force their sequential I/O to become random as well.

With these assumptions in place, the logical remedies to your performance problem become:
1) Add RAM to the server (are you maxed out at 4G already?)
2) Add disk spindles to the storage (If you already have 2 drives in the X2100, you're maxed out!)
3) Since you understand your data and how it's going to be used, find methods of storing it in RAM yourself in a sane way. Bonus points if you can store it in RAM in a format directly usable by your app without conversion/boxing.
4) Reduce the size of any given table that you need to scan frequently. That way when you need to do any sort of table scan, it'll be over a smaller region of disk.
5) Buffering writes to disk and doing them in batches results in fewer query cache invalidations and greatly reduced random I/O which leads to greatly improved performance.

Aside from the AWS suggestion, if your colo will let you install a second 1U server without extra fees, I have an older Penguin Computing system with 8G of RAM, 2 Opterons, and 3 or 4 Ultra160 SCSI disks and I'd be glad to get it to you somehow. I think I even loaded Windows on it once. You could break your database off to a separate server.

Let me know if you need clarification on any of those points.

If there's a PCI-Ex slot free, it's possible to add a disk box and SAS drives in a nice fast stripe configuration.

Again, COST is the thing I would think.

Chris...

1. We're actually on a Sun Fire X2200 M2 with 2gb of ram and two 1.8ghz Opteron procs (4 cores)
2. It can take up to 32gb of ram, as it has 8 ram slots per processor.
3. Yup.
4. Yup. Two 250gb drives I think, or is it 500?
5. The server never goes above 1gb of ram utilization anyway.
6. Talking in numbers sure is fun.

Anyway, I have no interest in going back to the virtual server (or 'rented' server) world. It was a nightmare while I was in it and it will be a nightmare again. Our server is plenty powerful for what UER needs.

I certainly wouldn't pick Amazon as a host, who just shut down a website because they didn't agree with its politics (wikileaks). A major advantage of having our own server is that nobody can tell me what I can host.

Adding ram to the server is difficult, as the server uses stupid ECC ram which is extremely hard to find and extremely expensive (like 10x the cost of normal ram)

My colo charges by the U, so no, I can't install another server without doubling my monthly cost.

While I agree that the root cause of your performance problems is the database itself, the ASP technology becomes problem when it becomes a roadblock to implementing fixes.

I disagree. The purpose of the database is to store, organize, index, and retrive information quickly. The ASP's function is to assemble the web page, something it does admirably (and extremely quickly, even with UER's less-than-ideal layout template system).

If the site is performing slowly because the database is slow, complaining about ASP will not solve the problem. It is not realistic to upgrade to a whole new language. I do not believe 'memcache' will solve the problem.

The base problem is that it is a huge, huge amount of data no matter how you slice it.

-av

Posted by Avatar-X 1. We're actually on a Sun Fire X2200 M2 with 2gb of ram and two 1.8ghz Opteron procs (4 cores) 2. It can take up to 32gb of ram, as it has 8 ram slots per processor.

Ah, I was thinking M1

3. Yup. 4. Yup. Two 250gb drives I think, or is it 500? 5. The server never goes above 1gb of ram utilization anyway. 6. Talking in numbers sure is fun.

5 is probably bad, but also remember that apparent memory utilization may differ from actual memory utilization. I don't believe that Windows reports memory used for disk buffer (generally all of memory not being used for application data) as "used." Allocating more RAM to InnoDB would probably be a good thing, but gets into more weedy database optimization stuff where a real DBA will probably be more helpful and they could suggest the right balance between InnoDB cache and disk buffer.

Here's a bit on caching...
http://dev.mysql.c...l-query-cache.html
http://adminlinux....ry-for-caches.html
http://wiki.zimbra..._Deployments#MySQL

Anyway, I have no interest in going back to the virtual server (or 'rented' server) world. It was a nightmare while I was in it and it will be a nightmare again. Our server is plenty powerful for what UER needs.

Yeah, UER's availability needs are not higher than those of Zynga and other much higher-volume people who utilize AWS heavily. The state of virtual hosting has changed a lot from where it was 5 years ago.
Seriously, are you really saying that your server is powerful enough despite that this thread that we're in right now clearly demonstrates otherwise? You have serious performance problems. Adding more RAM will mitigate them a bit by giving the database more space for caching, but in the end, your hardware is insufficient for the task at hand (mostly disk and memory).

I certainly wouldn't pick Amazon as a host, who just shut down a website because they didn't agree with its politics (wikileaks). A major advantage of having our own server is that nobody can tell me what I can host.

Your colocation provider can shut you down just Amazon shut down wikileaks and for the same reasons. And to be clear, Amazon didn't shut down wikileaks because they didn't agree with its politics. They shut it down because they believed the information being shared was unlawful. They might be wrong and you're welcome to disagree with their politics (I do as well in this case), but there are other hosting providers out there who aren't Amazon - Rackspace being one of the other major ones.

Adding ram to the server is difficult, as the server uses stupid ECC ram which is extremely hard to find and extremely expensive (like 10x the cost of normal ram)

I may have some laying around. I have oodles of RAM. The Penguin I mentioned, for instance, takes ECC. What speed does the M2 need again?

My colo charges by the U, so no, I can't install another server without doubling my monthly cost.

Not surprising, but unfortunate.

I disagree. The purpose of the database is to store, organize, index, and retrive information quickly. The ASP's function is to assemble the web page, something it does admirably (and extremely quickly, even with UER's less-than-ideal layout template system). If the site is performing slowly because the database is slow, complaining about ASP will not solve the problem. It is not realistic to upgrade to a whole new language. I do not believe 'memcache' will solve the problem. The base problem is that it is a huge, huge amount of data no matter how you slice it. -av

A 2.5G table is not a "huge, huge amount of data. It's a teeny tiny miniscule amount of data. It's a lot of rows, yeah, but I have to assume that you're either being intentionally difficult.

The purpose of the database is to store, organize, index, and retrieve information, but not necessarily quickly. There are physical limitations on what it can do. I explained them to you in fairly clear, simple English in my last post. It will do what it can with what it has available,

But hey, don't take my word for using a product like memcached to alleviate database slowness. You do not know more or have much more difficult site needs than the engineers at facebook, myspace, livejournal, digg, twitter, wikipedia, flickr, typepad, youtube, and craigslist.

I spend most of my time at work helping business teams accelerate their applications, writing demo applications to use the various technologies described and show their benefit (or lack thereof), and reading articles to keep up on the latest trends in web scalability. I've seen much harder problems than yours and fixed them with the sort of solutions I've suggested.

If you want to actually fix your problems, you're going to need to be more open to actually changing things. Thus far, you've blown off every suggestion that requires anything more than some minor rearrangement of tables (which will help, yes, but not nearly as much as a lot of the others). When you've optimized a solution as much as it can be optimized and it's still too slow, then you need to re-architect it.
If your current programming language is too limiting to allow re-architecting the site, then it's time to phase it out in favor of a modern programming language which will.

The specification of that server wouldn't even merit a basic domain controller application here. I know you bought it a while ago and I'm sure it was a great deal at the time, but its specifications are way lower than I'd look to run a DB with tables in the gigabyte range on.

ECC RAM is a huge pain in the cunt, but Kingston now make Value ECC RAM which is very, very good and also exceptionally cheap.

http://www.valuera.../server_memory.asp

I've been using it on a host of Dells here and it works perfectly at around a quarter of the cost of the Dell/Hynix stuff.

If MySql works in any way similar to MS SQL, it'll auto grow the store size in-line with how much RAM is actually available. While it looks all well and good that it never uses more than a gig say, it's actually throttled itself back and installing more memory will allow it to grow, cache more and performance should improve.

Chris...

Please don't think I don't appreciate your suggestions. But, also, keep in mind the following:

• This site is a hobby for me. I get a bit of money from flashlight sales, and the donations generally cover the server costs, but that is it.
  
• I have a full time job and another part time job.
  
• I have a dog that needs to be walked.
  
• Essentially, I am very short on spare time.
  
• My idea of fun is not "lets reprogram the site into a whole new language, just 'cause!"
  
• Especially a new language I am unfamiliar with.
  
• The most important point is that we are talking about a feature that few people on UER will miss, and those that do miss it can simply pay a pittance of money to keep it.
  

I'm not going to do massive server upgrades, huge language changes, or complete server moves to the cloud (!!!) just so 30 people can get their readstates.

I started this thread in the hopes that someone could look at the data and suggest a better way of doing it with the resources at hand. Infrastructure changes are out of the question.

-av

Posted by Avatar-X Please don't think I don't appreciate your suggestions. But, also, keep in mind the following: This site is a hobby for me. I get a bit of money from flashlight sales, and the donations generally cover the server costs, but that is it. I have a full time job and another part time job. I have a dog that needs to be walked. Essentially, I am very short on spare time. My idea of fun is not "lets reprogram the site into a whole new language, just 'cause!" Especially a new language I am unfamiliar with. The most important point is that we are talking about a feature that few people on UER will miss, and those that do miss it can simply pay a pittance of money to keep it. I'm not going to do massive server upgrades, huge language changes, or complete server moves to the cloud (!!!) just so 30 people can get their readstates. I started this thread in the hopes that someone could look at the data and suggest a better way of doing it with the resources at hand. Infrastructure changes are out of the question. -av

There are better ways of doing things with the resources at hand, but many of them require thinking about a database in terms not similar to those that one would find in a circa 1998 computer textbook.

How about I send you like 8G of ECC RAM (assuming that the stuff I have is the right speed/size for your system) and you give me a year or two of the sort of membership which will make read states usable again for me in exchange? Everyone wins! More memory = faster database/site. And then you don't have to deal with me complaining and/or writing a better forum which is more able to handle the load.

Sure, I'll take that deal.

Our server was top-of-the-line for its price range in 2007 when I bought it, so it's hardly 1998.

I'm not sure what the best way is to figure out how much memory the server has without opening it up? I wonder if it would be in any of the documents I received with it...

Update, OK, according to my original quote when I ordered the server:

Sun Fire X2200 M2 x64 Server: 1x AMD Opteron Model
2210 dual core processor (1.8GHz/1MB ), 2x 1GB
registered ECC DDR2-667 memory, no Serial ATA drives,
no DVD, 1x PSU, Service Processor, 4x 10/100/1000
Ethernet ports, 6x USB 2.0 ports, 1x I/O riser card with 2x
PCI-Express x8 slots, no power cords, order Geo-specific xoption.
RoHS-5. Standard Configuration.

So I need some ECC DDR2-667 memory. Is that what you have?

-av

Posted by Avatar-X Sun Fire X2200 M2 x64 Server: 1x AMD Opteron Model 2210 dual core processor (1.8GHz/1MB ), 2x 1GB registered ECC DDR2-667 memory, no Serial ATA drives, no DVD, 1x PSU, Service Processor, 4x 10/100/1000 Ethernet ports, 6x USB 2.0 ports, 1x I/O riser card with 2x PCI-Express x8 slots, no power cords, order Geo-specific xoption. RoHS-5. Standard Configuration. So I need some ECC DDR2-667 memory. Is that what you have? -av

Did you add a second processor? Your quote is for a single dual core CPU. It may be important because chances are that the second bank for RAM won't be usable without the second processor installed.

I'll take a look when I get home, but I wouldn't be surprised if the Penguin used DDR2-667.