Many updates against MySQL

Hello! My first post here, so be patient please. I am developing site where I have lots of static content. But on many pages I have query to update count of views. I would say this is may cause lots of problems and was interested in another solution like storing these counts somewhere else. As my knowledge is bit limited in this way, I am asking you. I can say I understand PHP(OOP ofc) and MySQL. Nowadays I am getting into servers.

Other question I have is:
I read about making lots of things static.(in Flickr Architecture) and am interested how they do static sites? Lets say they make photo page static? And rebuild when tagg or comment is added? I am bit interested in it as I want to learn Smarty better(newbie) and serving content.

Moreover, how about PHP? I have read many books about PHP theoretically but would love to see some RL example of using objects and exceptions(mainly this as I don't completely understand it) to learn some good programming habits. So if you can help me with some example or resource, please do :)

I know I've covered huge area of things but these are what makes me mad everyday. So please be patient :) Greetings.

Comments

[Todd Hoff]

Take a look at http://highscalability.com/strategy-serve-pre-generated-static-files-instead-dynamic-pages">
Serve Pre-generated Static Files Instead Of Dynamic Pages and http://highscalability.com/bunch-great-strategies-using-memcached-and-mysql-better-together">A Bunch of Great Strategies for Using Memcached and MySQL Better Together.

Counts can be cached in memcached so you are just hitting memory for reads and writes. Lazily write the counts to the database every X seconds or calculate the counts from the database on startup.

[JAM3SoN]

Memcache seems pretty interesting. I did not ask about it because I don't exactly understand how to work with it(will probably have low-cost hosting and doubt it will be possible). Serving static content is the way. At least particular caching with Smarty, must try it.

AWS looks very good. Also I've found very nice PHP library to work with AWS. My only problem is that I don't understand images and server-related things. I wanted to combine S3, SQS and EC2 to image processing(with imagemagick) but haven't found a way how to do that. Better said, how to make EC2 working with ImageMagick and process uploaded photos(on the fly).

I will be pleased if anybody can give me some resources to topics I want to learn.

[JAM3SoN]

Okay, I was reading a lot and I think my knowledge has increased :)

But I don't understand this(from Carl Henderson's Flickr presentation):

Why PHP is great
•Stateless
–We can bounce people around servers
–Everything is stored in the database
–Even the smarty cache
–“Shared nothing”
–(so long as we avoid PHP sessions)

What does the stateless mean? I am using Smarty in my project, because 90%+ operations are read only. So I use smarty cache for almost whole site, but I have to manage users. What is the best way to handle users? Sessions ? Cookies? As cookies can be changed I can't imagine appropriate way how tu use it; on the other hand, I heard that sessions are not so great(don't know why) and I have to store only some informations. I will be using only one server on shared hosting(since start) but I want to have it as optimized as possible. I think my code is optimized on good level. I use expire headers on JS and CSS files(not to download them every time) in my .htaccess and gziping content.. In PHP I am going to use APC, and already using Smarty to cache content into static. So my dilema is now users.. How do you do it? Sessions? Cookies? and how about validation? Thanks.

[Mark Rose]

Stateless means that no specific state is stored between one request and the next, meaning any server in a distributed system can handle the next request.

Sessions aren't bad, but you have to take special care when using a distributed system. First, you have to have a common store for them (in a database, memcached, etc.). Second, you have to take care not to store too much information in the session, or their size can become unmanageable. Third, you have to pay attention to concurrency issues, where a user makes more than one request at the same time. Some sites avoid sessions completely and only store an encrypted user key in a cookie, then retrieve information based on that key from databases as needed.

If you'll only ever use one server though, sessions are an excellent way to go. There is a small security risk to using the default file-based setup on a shared host, so you may wish to look into storing them in the database.

[JAM3SoN]

Thanks for your answer, Mark.

So I would use sessions. I want to have on my site things like bookmarks, playlist etc so I thought I can store this information altogether with user ID and session expiry time in session.

I am right now thinking about this:

I have an artist page and to display it I need 3-4 queries to DB(fetch data, images etc). So i decided to make it cacheable(with Smarty) and destroy cache only when info is updated. Those are "static" information.

On the right panel of this artist page, I have user menu. It is div with welcome {nickname} text, with links to profile etc. As it is artist page, there is interactive menu add/remove to bookmarks etc.

So this is my bottleneck. I want to make it as simple and light as possible. I can't cache this 1 artist page for different users, it would generate me milions of pages.. So I thought about having artist ID's stored in session, and when user comes, simple IF condition will decide to show add/remove. What do you think about it? It is possible to have this USER div in some no_cache block(custom Smarty function)? Or would you recommend me another way?

I think this is good way as I don't have so much data in sessions and having all important DB-related things cached..

[Mark Rose]

I'm not very familiar with Smarty.

For very small pieces of highly used data, such as a nickname, it makes sense to store it in a session. The extra memory involved is minimal compared to the time it would take to retrieve it from the cache.

I would cache the user's playlist and bookmarks. These things take up more space and are accessed relatively infrequently. Only 1 user out of however many you have will access these items, and they should be quick to recreate if you've designed your database correctly (i.e. simple SELECT statements), so rather than have all that space permanently occupied in your sessions, just cache and recreate if necessary.

I would also cache artist information for the same reasons. And while I don't know your application, I'd imagine it would be possible to retrieve everything with a single SELECT statement using JOINs as necessary.

If you're building an application where information about the current user is displayed on every page, it makes no sense to cache whole pages. You're far better off to cache by the section (like your menu div, your main content div). I don't know if smarty can do that. In fact, unless you're rendering tables or generating a lot of HTML based on the data you retrieve, I'd suggest just caching the results of your queries. That way, if you update a layout on the live server, you don't invalidate all your cached data. The only exception would be a very highly accessed page, such as your home page.

[JAM3SoN]

Of course I'll try to minimalize session data. Right now I am storing only nickname, stamp and privileges. I think I have sessions secured as I use automatic ID regeneration, users got timeout after 30mins of inactivity and I've disabled session id in urls..

Page with bookmarks can be cached, don't know how about playlist, I am afraid of lots of invalidation. I was thinking about storing bookmarks in session, because e.g. on artist's page user will see if he has bookmarked this artist yet. But I will sacrifice this feature, because in other case I would have to make another block of website dynamic instead of whole page would be cacheable.

Of course I have artist page already cached, I have around 3 queries but they are relatively simple(fetch data from artist table, images table, comments table) but I don't think they could be merged into one.

Of course Smarty can cache only parts of page, I know some techniques how to do it, but don't know how does in handle with no-cache blocks.

I'd suggest just caching the results of your queries.
How would you do that? Can you post some links about it? I heard about that but can't imagine how it works(and performance issues and gains). :)