Author Topic: messages table  (Read 14195 times)

Offline flash

  • Jr. Member
  • **
  • Posts: 49
messages table
« on: August 08, 2006, 12:33:34 PM »
What is in the messages table (I know the data in it, that is not the question)? I have caching turned FALSE. The caching table is empty. But there are inbox messages in the messages table that show a cache key. What is this for? How do the rows get removed (I assume this table does not grow forever)?

Offline bpat1434

  • Administrator
  • Hero Member
  • *****
  • Posts: 673
Re: messages table
« Reply #1 on: August 10, 2006, 09:57:56 AM »
Caching I believe is defiend in the main.inc.php file when you set the lifetime to "10d" or however long you want (default is 10d).

Couldn't really tell you what's in the messages table, except maybe messages that are on your server. Possibly in an effort to keep the IMAP server free, RC downloads the messages automatically, inserts their info into the database and when you read it, it reads from the database. Well isn't that caching? Kind of but not really. The caching is used for everything, and if you have it enabled, everything is cached. Without caching even though the messages, I guess, are stored in the database, the call for the number of messages, message headers and such are all done on the IMAP server.

Attached is an image of what I suspect is how RC works. It may not be accurate, but it's my best guess.

The difference between cached and uncached is the load on the IMAP server. Cached typically reduces the load; however, you won't get the grand update. Uncached increases the load; but, you get the total update and see the new mails as they arrive. It's a toss-up situation.

[edit]Yes yes... I know... I spelled cache wrong in the image. But I don't care. It was sloppily thrown together.[/edit]
 
  

Offline flash

  • Jr. Member
  • **
  • Posts: 49
But I still wonder about the messages table
« Reply #2 on: August 10, 2006, 12:37:14 PM »
OK, I just see no reason why the DB would still populate the messages table with cache off (does not seem to populate the cache table). That is why I was asking what that table is used for (obviously not cache). I noticed this and will just keep an eye on it to see if it grows. I emptied the table to get a fresh start.

To me, cache is a simple trade-off. I just check webmail now and then and do not leave a lot of messages on the server. So cache just uses more resources. If you have a lot of messages and go back and forth in the list, then caching would be a good thing.

Offline bpat1434

  • Administrator
  • Hero Member
  • *****
  • Posts: 673
Re: messages table
« Reply #3 on: August 10, 2006, 02:01:16 PM »
Okay, let me try this again:

The messages table is for quicker reading of the IMAP messages. Rather than reading the messages multiple times from the IMAP server, it is more than likely faster to use a database.

So the retrieval and count of messages is done with the IMAP server. When messages are downloaded, they are stored in the database for later retrieval. The contents of a message won't change. So storing it in a database speeds up the application in that you don't always have to converse with the IMAP server and interpret the responses. This way, you can just get a quick answer from the database.
 
  

Offline flash

  • Jr. Member
  • **
  • Posts: 49
Yes, we are not on the same page here
« Reply #4 on: August 10, 2006, 05:33:49 PM »
Yes, I understand, that is called caching.

But I have caching turned off. The cache table is empty, as it should be. But RC is still putting mesage information into the messages table. So back to the original question - if caching is off, why is it caching message information into the message table (which is different than the cache table). With caching off it should be going back to the server for each request.

Offline gordian

  • Newbie
  • *
  • Posts: 1
Re: messages table
« Reply #5 on: August 10, 2006, 09:55:10 PM »
can a newbie had a go at it?

Here it goes,

I think the caching is to store the messages in the cache table to queue for processing by the IMAP.
While the messages are stored in the message table for RC to retrieve and display so that it do not need to go to the IMAP everytime RC loads up...

Offline flash

  • Jr. Member
  • **
  • Posts: 49
Re: messages table
« Reply #6 on: August 10, 2006, 10:31:51 PM »
I don't follow . . . please explain further. You are saying the messages table is really the message caching? And I don't get what you think the cache table is. But remember, I have caching turned off and the message table is beong populated.

Offline bpat1434

  • Administrator
  • Hero Member
  • *****
  • Posts: 673
Re: messages table
« Reply #7 on: August 11, 2006, 11:09:35 PM »
Here, I'll try and explain it in a more in-depth matter:

With caching disabled, a minute amount of caching happens!!

Messages are cached for reading for a specified time (detailed in main.inc.php). When you view a message, Roundcube downloads all the information about it (unfortunately including attachments). Now, once the message is down, you have to ask yourself this question: Would it be wise of me to store this information in case it were called again in the future? Or continually access the IMAP server for every mail read call?

The answer to that question should be quite obvious. You would want to use a database for a couple of reasons. The first being that it would be faster. A database is designed to store, retrieve, and modify information. An IMAP server isn't typically the fastest, nor is it meant to constantly be bombarded with requests for the same mail.

So what Roundcube does is store the message in the table `messages` for that specified amount of time. When you initially read the mail, it is downloaded from the IMAP server. Once it's downloaded, RC "caches" the message in the `messages` database. When you go to re-access the message, it is read from the database. This is not only faster, but it helps reduce the load on the IMAP server.

The cache table is storing all of your other IMAP information. Here, let's do it this way:

Messages Table (For Message Caching)
- Stores the IMAP message and headers
- Stores the IMAP ID for comparison
- Stores a couple flags for future reference
- References ONLY messages, and if caching is enabled the user's cache.

Cache Table (Session & Info Cache)
- Stores the folder-list
- Stores the ID of the user & Session info
- Stores the message counts

So basically, here's how it works.

Caching Enabled
With caching enabled, one call to the IMAP server will suffice for quite some time. The only time information will be retrieved, parsed, and viewed is with changes. So new messages and such. Deleting messages, marking read and such happen both DB side and IMAP side.

Caching Disabled
With caching disabled, each call to the IMAP server grabs the folder list, the message counts, the read/unread status, the message flags, the message list and some other info about your mailbox(es). There is no "cache" for this.

Caching Enabled & Disabled
Messages are cached no matter what. This is done so that re-opening a message after closing it is faster, and the load on the IMAP server is reduced. With 50 users on at once, all reading a message at once, calling the "open" method at the same time, your IMAP server would buckle. With the database system, the database is meant to handle that load and can serve the information much more quickly. Now, if they are reading a "new" message, then the IMAP server is polled; otherwise, it's read from the message cache.

Does that help you understand? The cache part is basically ALL your IMAP information plus session info. The message caching is for just messages, and happen regardless.

Just for reference, here are the database schemas:
Table: cache
CREATE TABLE `cache` (
 `cache_id` int(10) unsigned NOT NULL auto_increment,
 `session_id` varchar(40) default NULL,
 `cache_key` varchar(128) NOT NULL,
 `created` datetime NOT NULL default '0000-00-00 00:00:00',
 `data` longtext NOT NULL,
 `user_id` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY (`cache_id`),
 KEY `cache_key` (`cache_key`),
 KEY `session_id` (`session_id`),
 KEY `User_ID_FK_cache` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Table: messages
CREATE TABLE `messages` (
 `message_id` int(11) unsigned NOT NULL auto_increment,
 `del` tinyint(1) NOT NULL default '0',
 `cache_key` varchar(128) NOT NULL,
 `created` datetime NOT NULL default '0000-00-00 00:00:00',
 `idx` int(11) unsigned NOT NULL default '0',
 `uid` int(11) unsigned NOT NULL default '0',
 `subject` varchar(255) NOT NULL,
 `from` varchar(255) NOT NULL,
 `to` varchar(255) NOT NULL,
 `cc` varchar(255) NOT NULL,
 `date` datetime NOT NULL default '0000-00-00 00:00:00',
 `size` int(11) unsigned NOT NULL default '0',
 `headers` text NOT NULL,
 `body` longtext,
 `user_id` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY (`message_id`),
 KEY `cache_key` (`cache_key`),
 KEY `idx` (`idx`),
 KEY `uid` (`uid`),
 KEY `User_ID_FK_messages` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
  

Offline flash

  • Jr. Member
  • **
  • Posts: 49
Re: messages table
« Reply #8 on: August 11, 2006, 11:35:00 PM »
Very complete and helpful discussion of the topic. Thank you. :)