5.5 million rows and counting... Need better performance! Help!

[tonyd7]

I have a PowerMac G4/733 with 1.5 gigs of RAM, and I have allocated 500 megs of RAM to FileMaker Pro 5.

I have a new database where I imported 5.5 million rows. The import only took about an hour and a half.

I then had to create indexes on three columns (fields). And, by looking at how slow the index build is taking, it will take ~81 hours to complete.

Since I will be updating (and possibly re-creating) this database weekly, I need a faster way to build these indexes.

What do I need to do in order to speed up index-building? Would it help if I installed a fast-wide SCSI drive for the database - or do I need dual processors? I was going to load it all into RAM, but the database is over 1.5 gigs in size and my Mac won't support a RAM disk larger than 256 megs. I can't seem to find a commerical RAM disk product that would allow me to purchase several gigs of RAM and do the processing in RAM.

Would FileMaker version 5.5 do any better?

I am not sure what performance gains I can achieve - but I will try anything. Any performance tips or explainations would be appreciated!

Thanks!

Tony

[Anatoli]

Faster drive will help. If you are not importing again the whole large database and indexing that from zero, I believe just by adding records the autoindexing will be much faster. FM is good at this.

Another worry is that you are near 2GB per file limit. Or is it now larger in Mac world?

If you can split that into 2 related files, maybe everything will be faster.

[Kurt Knippel]

quote:

---

Originally posted by Anatoli:

Faster drive will help. If you are not importing again the whole large database and indexing that from zero, I believe just by adding records the autoindexing will be much faster. FM is good at this.

---

Adding records to the index is very fast, you will never even notice a slowdown. Indexing from scratch is the slow part.

quote:

---

If you can split that into 2 related files, maybe everything will be faster.

---

This is actually a really good idea. Depends upon the structure of the data, but do this if you can.

Also FMServer is being given way too much RAM. Unless you are going to run this in a RAM disk having 1.5GB of RAM is way overkill. FMServer itself does NOT load the databases into RAM, they are still accessed from the disk and having too much RAM allocated will slow down processing as it has to devote alot to power to simply dealing with all the memory paging.

We have very fast and powerful servers (5 of them) running Filemaker Server in a RAM disk with only like 8MB RAM actually allocated to the FMServer application itself.

[Kurt Knippel]

quote:

---

Originally posted by Tony D:

I was going to load it all into RAM, but the database is over 1.5 gigs in size and my Mac won't support a RAM disk larger than 256 megs. I can't seem to find a commerical RAM disk product that would allow me to purchase several gigs of RAM and do the processing in RAM.

---

We use RAMbunctious. I am not sure what the maximum size of RAM Disk it will support, but we have like 750MB ones setup on each of our 5 FMServers and they run FANTASTIC.

[Steven H. Blackwell]

FileMaker Pro 5 on Mac OS 8.6-9.x can not take advantage of more than 32MB application memory. This has changed slightly for FMP 5.5 to accommodate plug-ins requiring more than 1 MB RAM memory on loading. But the 500 MB figure is a waste.

FileMaker Server 5 and FileMAker Server 5.5 on both Windows 2000 Server, Windows NT 4 Server, and MAc OS 9, and MAc OS X can use up to 40MB of RAM memory for cache purpose. Rarely doe it require that much. It is performing optimally when cache hits are between 95 and 100 in normal usage. Montor this thriugh the various Administration windows.

HTH

Old Advance Man

[LiveOak]

Take a look at Clarkwood Software RAMBunctious and Peek-a-Boo. Peek-a-Boo allows you to force the allocation of processor time to FM, even if the Finder is clicked into the foreground. Both are outstanding. I would not use the built-in RAM Disk in place of RAMBunctious in this case. RAMBunctious allows you to schedule write-thru's on a periodic basis to reduce vulnerability. Also, you MUST us a UPS with a RAM Disk.

-bd

[danjacoby]

Finally, some real use from my terabyte hard drive!

[Steven H. Blackwell]

<In case your still wondering. With MacOS 9 and above. the max file size in 2TB (2,000 GB) below OS 9 its 2GB.

The maximum size of a single FileMaker Pro 5.5 file is 2GB however.

Old Advance Man

[falkaholic]

In case your still wondering. With MacOS 9 and above. the max file size in 2TB (2,000 GB)

below OS 9 its 2GB.

[agraham999]

Depending on the number of users accessing the database at any given time...you could setup an array. I've used several arrays...and when you have a lot of users...the access time is quite speedy. Plus...depending on the RAID level, you can hot swap drives and re-build data (automatically) if a drive were to fail.

[SuperMacGuy]

Since some of you are talking performance, would a dual processor Mac speed up calculations, indexing, summaries, etc? Does OSX run faster than OS9 for large databases?

Really off-topic: Could I use a RAIC with Instant Web Pub? I'm thinking one cpu could do the FMP serving, and the other could dole out web pages without much performance hit for our "real users".

Chris