Gossamer Forum
(Advanced Search)
for
Home : General : Databases and SQL :

use one or more databases

Mar 11, 2005, 1:10 AM
Alba
Enthusiast (567 posts)
Mar 11, 2005, 1:10 AM
Post #1 of 7
Views: 8174
Shortcut
Quote Reply
use one or more databases
Until now I've been using multiple installations of LSQL & DBSQL on the same server but under different accounts.

Programmes will be:
  • 5 x GLinks 3.x (when finally released). 1 x with extensive use of plugins including multilanguage
  • 1 x DBSQL
  • 1 x GCommunity

Server Software:
  • Apache/1.3.33 (Unix)
  • CPanel


I'm about place all the installations under one account and wonder about how best to setup the mySQL databases.

Should I...
  1. Have one database with each programme installation using a different prefix?
  2. Have multiple databases, one for each programme?

Which method would be best in terms of speed and server load?

Thanks for your help.
Mar 11, 2005, 2:15 AM
Andy
Veteran / Moderator (18441 posts)
Andy's avatar
Mar 11, 2005, 2:15 AM
Post #2 of 7
Views: 7931
Shortcut
Quote Reply
Re: [Alba] use one or more databases In reply to
Hi,

I'd recommend just the one database. The good thing about MySQL, is that unlike flat-file databases, you don't have to go through all the data in one go, as they are held in seperate MYI and MYD files.

For example, on Ultranerds we have several LinksSQL installations, as well GComm + GForum. It all runs really well :)

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Mar 11, 2005, 4:00 AM
Alex
Administrator (9387 posts)
Mar 11, 2005, 4:00 AM
Post #3 of 7
Views: 8030
Shortcut
Quote Reply
Re: [Alba] use one or more databases In reply to
Hi,

It's best to use one database. That way if you use mod_perl or some other persistent technology, all your applications can share a single connection to the same database, freeing up resources.

Cheers,

Alex
--
Gossamer Threads Inc.
Mar 11, 2005, 4:28 AM
Alba
Enthusiast (567 posts)
Mar 11, 2005, 4:28 AM
Post #4 of 7
Views: 7899
Shortcut
Quote Reply
Re: [Alex] use one or more databases In reply to
Thanks folks, I'll just setup the one database following your advice.
Mar 12, 2005, 3:21 PM
webmaster33
Veteran (2312 posts)
Mar 12, 2005, 3:21 PM
Post #5 of 7
Views: 7973
Shortcut
Quote Reply
Re: [Alex] use one or more databases In reply to
How about locking type? How much does it matter when traffic is really high (using mod_perl)?
My question affects 2 MySQL tables: MyISAM and InnoDB.

MyISAM has table level locking which is known to be very fast, but locks the whole table when UPDATE is executed.
InnoDB has row level locking which is known to be slower, but locks only 1 row, instead the whole table, when UPDATE is executed.
InnoDB may result less lock conditions, because locks just a row, so at high traffic may save time.

The question is: Which results higher performance under high traffic ?:
a) MyISAM fast performance
b) InnoDB row locking


I did read something like, that this usually matters on local networks, but not really on the web.

Do you have some experience about this?

Best regards,
Webmaster33

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Mar 12, 2005, 4:46 PM
Alex
Administrator (9387 posts)
Mar 12, 2005, 4:46 PM
Post #6 of 7
Views: 7978
Shortcut
Quote Reply
Re: [webmaster33] use one or more databases In reply to
Hi,

Completely depends on the application and the type of queries that are happening. With table-level locking, slow selects mixed with updates is a real killer (i.e. that's why the post view count is a separate table in mysql, as you wouldn't want the frequent updates on the post table as it would lock it too often).

Cheers,

Alex
--
Gossamer Threads Inc.
Mar 12, 2005, 6:11 PM
webmaster33
Veteran (2312 posts)
Mar 12, 2005, 6:11 PM
Post #7 of 7
Views: 7883
Shortcut
Quote Reply
Re: [Alex] use one or more databases In reply to
Quote:
With table-level locking, slow selects mixed with updates is a real killer
Ah, I understand now. Thanks!


Quote:
(i.e. that's why the post view count is a separate table in mysql, as you wouldn't want the frequent updates on the post table as it would lock it too often).
One final question:
What about the Hits field in LSQL app, Links table?
It is placed into the same main table, named Links.
This may cause performance problems, when jump.cgi is used very often (I remember users who needed to use mod_perl just because jump.cgi performance issues).
Well, Votes and Rating column might be also affected by this, but since this feature is not used so often as Hits, that would be not an issue.

In Links::User::Jump following code is used:
Code:
$db->update ( { Hits => \"Hits + 1" }, { ID => $id }, { GT_SQL_SKIP_INDEX => 1 } );
Which updates the main Links table every time a link is clicked through jump.cgi.

Wouldn't be advisable to move the Hits column out to a separate table?


Well, I know compatibility reasons are hard to deny this, but IMHO compatibility problems could be easily solved by:
1) checking if Hits column is existing,
2) if it does not exists, use the Hits table,
3) if Hits table is missing, create it and use it

Best regards,
Webmaster33

Paid Support from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...