Hi,

I dunno if any of you have ever written an entire IRC Services in FPC before, but for the past few months I've been porting my 5 year old windows (delphi) version to linux.

I'm wondering about databases however... The old windows version used to use a flat-file database to store nickname and channel registrations, the port I'm writing also uses the same thing. However this obviously will be slow if say an IRC network has 10,000 nicks/channels etc...

Is there anything anyone would suggest I do ? I have only a couple of ideas, perhaps you might have a few more than I...

cheers
James

Try the great Firebird (InterBase fork) http://www.ibphoenix.com It is free, open, robust and works fine with FPC and Linux.

jk

Use a KSAM/ISAM [Keyed/Indexed Sequential Access Method] file. They are much faster than databases, particularly relational ones and very easy to code up.

All you need to do is make the size of the file around 40% bigger than you expect it ever to be (and if it gets up to 50% then rebuild an re-populate it) then produce a hash number from the names and write to that address. You have to have a good hash algorithm (tons about) and a policy for migrating secondaries but it is all fairly straightforward. If your records are large you might have two files, one for the keys and one for the data, then you don't even need a hash key, you just insert the names in batches (say each evening or every hour if it is really busy) where you re-sort the whole key file and add the items in alphabetical order, a quick sort [maybe literally if you like it] will yield the name and you simply add the records to the end of the other file.

Another method sometimes used is to create an alphabetic directory hierarchy. Top level directories a,b,c...z and so on so that I would get the directory /p/e/t/e/r/ /b/r/o/o/k/s/ then you just put the records into files in the bottom directory - if there are duplicates then either ask a question to disambiguate them or require somebody to select another name. It is quite quick but relies on your having a disc volume that, on an unix system, has plenty of inodes and a small blocking factor - otherwise this is rather a wasteful option. It is dead simple to code though. The nice thing about this is that, if you have a half decent file system, you can allow arbitrary lengths of name and take case into account. Just remember that human beings are pretty bad at being able to type in even their own name twice without making a mistake. You can do some quite clever offering of alternatives with this system - if one letter is wrong there may only be a few directories that could fit, so, by doing a bit of tree walking you can offer a list of a few names that cope with simple spelling errors and transpositions.

lol, thanks. You sound like you're the person that wrote it :P Was originally thinking of re-writing all my database code and using a mysql layer, oh heck I dunno what I"m going to do. I'll check our the url you suggested however...

cheers
James

lol, thanks. You sound like you're the person that wrote it :P Was originally thinking of re-writing all my database code and using a mysql layer, oh heck I dunno what I"m going to do. I'll check our the url you suggested however...

I can add that I have been using InterBase for more than 10 years and have devoloped large client/server applications. InterBase has never given me, or to my customers, any problems. I have tried Postgres and MySQL but now I definitely have setteled with Firebird. I have written some articles (in italian) about InterBase for Linux just when it became free and open: http://www.oltrelinux.com/forums/list.php?f=223

However, if you need some help to use Firebird with FPC (on Linux or Windows) just let me know.

jk

Thank you very much, most helpfull...

cheers
James

Nothing of what the original poster said is exagerated. Forget MySQL; MySQL is a simple filemanager supporting SQL syntax - that is why it is so fast.

Firebird is a real relational database system (in industrial sense), supporting stored procedures, views and so on.

Michael.

Firebird seems overly complex for my purposes, but then again perhaps I haven't read enough information. I have to tell you all I have never worked with databases much before except years ago using MS Access and SQL, and mySQL on Linux.

cheers
James

Hello, James!

I accidently read your below question. When I understand right, you have a relatively simple data structure but you are afraid about a "bottle-neck" when accessing them?

Well, I am also a programmer over tens of years (I started with TurboPascal 3 for CP/M !), and I also worked all the time with flat files (file of record).

Let me tell you my opinion:
---------------------------
The major question are:
- How many records are assumed to be in the file?
- What kind of file?
- Sorted/unsorted file?

Kind of file:
-------------
The poorest thing you can do is using a TEXT file. doing so, increases the processing time heavily because you always have to parse each line again and again to pick out the particular "fields". -- The only advantage is that you can read it directly with any ASCII editor.
--> So I would use a file of record. It needs the same space but is quicker in handling. Well, you need a small "reader program" to read its content.

If you have continuously many changes in the file, this is another weak point. If you have multiple programs working with the file, you will run into troubles with text files, because you typically will have to re-write the whole file with every modification (except when you use it as untyped file and ro _really_ everything by hands.
--> File of record allows positioning and changing records one by one, but you still have the problem of locking...

Sorted/unsorted:
----------------
When you use an unsorted file, you have to read it sequentially every time you search something. In the worst case you will have to read the whole file every time! - On the other hand, adding a record is a simply 'append', so it is very quick.

Using a sorted file allows you to search extremely efficient *), but you cannot simply append a new record; it must be inserted at the right point. This costs a lot of time/cpu power and you will usually have to lock out all other tasks for that time. So this will be a big bottle-neck.

*) You start searching in the middle. If the found record is smaller, you choose the middle of the upper half, otherwise of the lower half. You repeat that until you have only one record remaining or until you found what you are searching for. EXTREMELY quick, but needs a sorted file.

My idea:
--------
If you
a.) do not have too many records (let's say a file with not more than 256MB) and
b.) do not have to access the data simultaneously from other "normal" programs at the same time...
... I would suggest a completely different way of handling:

Use a very fat machine with a lot of memory, the best will be to do that on the same machine where your IRC application is running, too. Write a "server" part which loads the whole "database file" into a dynamic list on the heap, hereby sorting it. Then it offers some kind of communication to your IRC applications, i.e. an Unix Socket, where it accepts queries and returns the results.

Feature:
o The data have to be loaded only ONCE. This is the most time consuming part
o You access your data in RAM. This is much quicker than any other way. Also, inserting a new record is extremely quick.
o You have exactly ONE process who plays with the data file, so you have no problems with file/record locking and so on.
o Modifications can be written back either on some triggers (if not such important data, you may write back to file only from time to time or only on shutdown of the program)

What do you think?

mfg

Ing. Rainer Hantsch

.---------------------------------------------------------------------. | \\|// Ing. Rainer HANTSCH - Hardware + Software | | (o o) Forget Windoze! -- We focus on L-I-N-U-X... | |--oOOo-(_)-oOOo------------------------------------------------------| | Ing. Rainer HANTSCH | mail: | | Khunngasse 21/20 | www: http://www.hantsch.co.at | | A-1030 Vienna | tel: +43-1-79885380 fax: +43-1-798853818 | | ** A u s t r i a ** | handy: +43-664-9194382 UID-Nr: ATU 11134002 | '---------------------------------------------------------------------'

Thank you so much for replying. It's good to hear other people's opinions...
I'm still reading your post, I have table-tennis (new season tonight) so I cannot fully reply to your post. I will however later tonight. Below are some answers to your questions...

cheers
James

It's an IRC Services program soon to be a daemon.
The number of records in the file will grow. For a small irc network, perhaps 100-200, it may grow larger in time. If a large network chooses to use my program for their large network, then the database may contain thousands of records.

- What kind of file?

Currently they are stored as files in a directory "./data". The files follow the structure of an INI file which I have written my own code to handle.

- Sorted/unsorted file?

Unsorted. Most recent record addition will always be at the bottom of the file.

This is not very good with something that has to be stable. IRC Services for instance. If for some reason the program should die unexpectedly then the data change in memory will be lost before being written out to disk.

Under linux there is mmap() which will you let the kernel load the pages of the database file on demand. See the man page of mmap for more info.

Peter

Hello, Peter!

This sounds good in the first moment (-> mmap), but I am afraid that this includes a small disadvantage:
I can (possibly) feed a complete file with mmap() into memory, but this will be then obe huge data block. And this will make it much more complicated to work with in the case that you have to insert records. (This is at least my personal impression.)
Though, when I load the file "manually" (meaning row by row and building my own dynamic list on the heap), I have available the full bunch of possibilities, can arrange the data as *I* wish, and so on. Gives me more flexibillity. - Am I right? :-)

On Tue, 6 May 2003, Peter Vreman wrote:

| > Use a very fat machine with a lot of memory, the best will be to do that | > on
| > the same machine where your IRC application is running, too. | > Write a "server" part which loads the whole "database file" into a dynamic | > list on the heap, hereby sorting it. Then it offers some kind of | > communication
|
| Under linux there is mmap() which will you let the kernel load the pages | of the database file on demand. See the man page of mmap for more info. |
| Peter
|
| _______________________________________________
| fpc-pascal maillist -
| http://lists.freepascal.org/mailman/listinfo/fpc-pascal |

mfg

Ing. Rainer Hantsch

I have been following this discussion with interest, as it relates to a project of mine very closely.
For my icafe solution, http://akinimod.sf.net I used flatfiles of record.
This worked beautifully, in fact I exploited the lack of real locking on Linux for it, every user logged in, is running a counter program, which reads his record in the database every 45 secconds or so to determine his available time.
Also when he logs out, the counter program changes his status in the database. Yet another program, runs every minute from a cron job, and removes a minute from each user who is currently logged in.

On windows this would obviously not work, because the OS cannot deal with multiple users/processes writing to the same file.

The catch comes in when there is too many users at once (it seems to manifest around nine). The harddisk can no longer field all these requests to the same file very well and it gets corrupted. One user's time runs out and several get their status changed (knocking them off the system) etc. Not to mention that the system becomes unbearable slow as it waits for the harddrive to handle all these write/read requests. The problem of course is that a harddrive needs to make a full revolution to go one record backwards in the same file. So the harddrive is spinning out of control as the users work.

I had in fact done several things to minimize search times etc. For example, the database file is unsorted but when a user first logs in, the counter program finds his record, and stores the file index in a variable - all further reads and writes is then done directly with a seek - saving the trouble of continous searching.

I expect therefore that your IRC services program would have the same problems, and suggest against this approach.

I now need to rewrite the backend of the system in a manner that will protect it from these problems. Which brings me to my question, what do you all reccomend ?
I have seriously considdered mysql, but this has a problem here. Any user who discovers the mysql username/password could easilly edit the database and up his time considderably without paying. The flatfiles were binary, I doubt that many internet cafe users would be able to edit it. Even if they could, they are blocked at another point, I can use unix permissions. The counter app has SGID permissions and runs as the group that owns the database file. The normal users themselves do not even have read access to it.
This option will no longer exist in a mysql backend.

What other suggestions can you make ?
It is vital that the database be frequently updated on disk because an internet cafe could lose a lot of money if a user who simply resets his thinclient workstation every few minutes never actually loses time.

TIA
A.J.

On Tue, 6 May 2003, James Mills wrote:
| > - How many records are assumed to be in the file? | It's an IRC Services program soon to be a daemon. | The number of records in the file will grow. For a small irc network, | perhaps 100-200, it may grow larger in time. If a large network chooses | to use my program for their large network, then the database may contain | thousands of records.
So you must be prepared for huge data volumes from the beginning.

| > - What kind of file?
| Currently they are stored as files in a directory "./data". The files | follow the structure of an INI file which I have written my own code to | handle.
I would keep in mind that with growing complexity of a file, loading it will usually also become slower.

| > - Sorted/unsorted file?
| Unsorted. Most recent record addition will always be at the bottom of | the file.
You have to search for records? Then this is no good choice, because you always have to read sequentially... Costs a lot of time.

| This is not very good with something that has to be stable. IRC Services for | instance. If for some reason the program should die unexpectedly then the | data change in memory will be lost before being written out to disk.

Well, when you keep all changes in memory for hours, this may be true. But what about "dumping" the memory list to file periodically and (possibly) using two files alternating? So even if you crash totally, one of the two files is ok. --> What happens when you use i.e. MySQL as database? This can also crash and has much more complexity (and therefore risks) as a flat file!

My experiences are: When a process DIES, this nearely always caused by poor program code. And this often happens when using pointers or when not cleaning up memory correctly.
But it is much safer to have one "database-daemon" running than having multiple tasks fiddleing concurrently around in shared files!

mfg

Ing. Rainer Hantsch

Hello!

Good arguments....

On 6 May 2003, A.J. Venter wrote:
| The catch comes in when there is too many users at once (it seems to | manifest around nine). The harddisk can no longer field all these | requests to the same file very well and it gets corrupted. One user's | time runs out and several get their status changed (knocking them off | the system) etc. Not to mention that the system becomes unbearable slow | as it waits for the harddrive to handle all these write/read requests. | The problem of course is that a harddrive needs to make a full | revolution to go one record backwards in the same file. So the harddrive | is spinning out of control as the users work.

This is logical! When multiple programs start to edit one file, you are definitely running in such situations. Even in case that you _optimize_ that by only editing one record with seek()/read()/write(), you cause a lot of activity on the disk. Well this will be cached by Linux very well, but it must be finally done anyway!

| I expect therefore that your IRC services program would have the same | problems, and suggest against this approach.

Exactly. Whenever you run code multiple times in parallel (which is the cause here), you run into this problem. Therefore I would split all that into a backend and frontend. While the frontends can run multiple times, there is only one backend, keeping data files up todate and working primarily in memory. So it is fast to the frontends and writes whenever it has time all changes to disk. (A lot of programming, surely, but this has no bottle neck in normal environment sizes.)

| I have seriously considdered mysql, but this has a problem here. Any | user who discovers the mysql username/password could easilly edit the | database and up his time considderably without paying. The flatfiles | were binary, I doubt that many internet cafe users would be able to edit | it. Even if they could, they are blocked at another point, I can use | unix permissions. The counter app has SGID permissions and runs as the | group that owns the database file. The normal users themselves do not | even have read access to it.
| This option will no longer exist in a mysql backend.

Yes and no. - More NO. You can write some kind of "Middleware" which runs on the MySQL Machine and uses a UNIX-Socket instead of a true network connection and talks to small daemons on the workstations, too. So you can setup the SQL server to only accept local connections and nobody will be able to access MySQL from an external machine, because...
a.) no connections are accepted at all
b.) nobody knows username/password because it cannot be sniffed on the LAN. So you have the benefit of SQL, too.

| | What other suggestions can you make ?
| It is vital that the database be frequently updated on disk because an | internet cafe could lose a lot of money if a user who simply resets his | thinclient workstation every few minutes never actually loses time.

An what about monitoring the T.C. from the server side and locking the customer out when he is doing such things? There are many ways to detect such situations!

mfg

Ing. Rainer Hantsch

Thanks for your valuable insights Rainer.

What you say here would normally be true, but not in this case. The system runs on thin-clients, all the processes etc. appear to be runnning on the localhost therefore. What you have here is best thought off as one machine with a lot of monitors, keyboards and mice attached. So host based security is useless.

Of course one could use an additional server, but this system was primarilly developed for poor african schools, they run their labs as icafe's after school to pay for their internet connectivity. It is a vital sustainability product. These people have litle or no money, most of the labs are paid for by sponsors. The reason the code is GPL'd is exactly that - we put it into poor schools, we have no interest in the rest of the icafe market so we give it to the world, in return for code contributions.

But these labs are paid for mostly by sponsors, if we need to add another server machine (e.g. a machine with disks) we would need to start cutting corners on other hardware etc. I am still thinking of the best approach to this problem, your idea of a daemon that maintains the data, and writes it out periodically sounds very good btw, despite the overhead of doing so.
The point is just, there is no networking in the traditional sense of the word here. By the time the applications start running, they think they are running on the server.

A.J.

The quickest and easiest way would be to make a ramdisc, put the relevant files into it and have the programs use that - have another daemon synchronise the ramdisk every so often and fit a UPS. If you use a journal file system you can just sync what has changed which makes it much quicker. That would be a very quick project.

Another approach, also very easy, is not to bother updating timers all the time. Just create a secure log of every use logging in and out - unix does this already in wtmp. Then, on a completely different system, run a batch job that just reconciles the log file. Then update the users with the final totals daily from the log file. wtmp is binary, but you can read it with 'last'. I would use awk for that exercise rather than pascal - it is very good at that sort of job and easy to code.

Even easier, on linux, is to enable system accounting that does all the work for you, then use those totals to update the user times. System accounting does have a performance penalty of around 2%, but your program, from the sound of it, is likely to be impacting performance by more than that so it would be a performance improvement.

Tre.

This is true, my code us mostly 99.9% defect free. We learn a lot about how to engineer software at uni :) Thank you for your ideas, I'll be sure to come up with something. Personally I'd rather implement my own database as you said instead of relying on another process/task to handle the database.

And as you said, if the code is flawless there should be no problem.

cheers
James

Geeez, I can't type anymore!!! Sorry for the typos.

This particular method you are talking about, KSAM/ISAM. Could you point me to some usefull tutorials on the web that explain this fairly well in programming terms (perhaps even in pascal) ?

cheers
James

You might try to use the gdbm units provided standard with Free Pascal. There are examples included.

Michael.

Thank you.

James

Genius, sheer genius. All I would need to do, is to modify the code once, so that the database path is changed. Set up a boottime script which creates a ramdisk, copy the database files into it, and go ahead. A cron job can then copy the database back to the harrd-drive every 10 minutes or so. All my servers allready have UPS's fitted, and they have 1.5 gig's of ram plus 3gb's of swap, so a 20mb ram disk won't be an issue.

Neither of these would work, as the system is meant to be before the fact time control. I.E. A user buys five minutes, and if after five minutes he has not bought more, he is automatically logged out, and cannot log back in until the system is ready for him. The above methods would be fine for after the fact billing, but in african countries fraud is so rife, that no internet cafe would ever see a penny if you did not absolutely have to pay beforehand to work.