This is very helpful.
Database consists of approximately 220,000,000 records. Raw data total (roughly) 5.5 GBytes. I don't know how large the database will be loaded (don't know about overhead, etc.)
Running InnoDb. Probably split 90% read and 10% writees.
If nothing else replication might be good for disasters.
RAM costs are down to about $350 per Gbyte. I was thinking of using internal RAM. I know I can get to 12 Gbytes for sure; maybe 24 Gbytes.
Any further ideas?
Creigh
At 09:42 AM 8/18/2003 -0500, you wrote:
At 09:17 AM 8/18/2003, you wrote:Have a very large database and due to performance requirements it seems that running MySQL in RAMDisk might be a good solution. Access to the database is through PHP. Any suggestions or experiences?
Creigh 305-541-1122
Creigh, How large is the database? How many rows? How many MB? Are you using MyISAM or InnoDb? Are you mainly reading or writing to the database?
External RAM disks that are around 8g are extremely expensive. Some have built in in battery protection and operate independently from the OS so if the OS reboots, you still have your data. A couple of years ago I priced these devices at around $25,000 and that is quite a lot to spend for only 8g. There are disk caches that work almost as fast as a RAM disk by speeding up writes by caching them, of course if your computer goes down you are very likely to get corruption.
MySQL 4.x has a read cache that is very good for retrieving repetitive queries so in this respect a RAM disk may not help that much if your application is mainly reading from the tables. (The cache is reset if a write is done to the table so if you are doing and equal number of reads and writes to the same table, the MySQL cache won't speed things up that much.)
Speed up suggestions:
1) Use a LIMIT 100 on all select statements because users usually are only willing to scroll through that many rows. If they want to see more than that, use LIMIT 100,100 to get the next 100 rows etc..
2) Don't use Persistent connections because that doesn't speed things up.
3) PHP takes a great deal of time loading scripts off of the hard disk so if you want to use a RAM disk, put your PHP scripts and web page images on the RAM disk (around 5-10 MB). This will increase the speed of your scripts by about 30%-50%.
4) Replicate the data to other database(s) and use those for read only queries. You can have your PHP script use a different MySQL connection for read queries and another for updates. The updates gets written to the master database and the changes then gets replicated out to your readonly databases. This takes the load off of the master database (since it is only doing updates), and the replicated databases will likely have rows cached longer.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
