Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I want to create a messaging module for the social network web-site project (PHP & MySQL) I've been working on. What substructures do well-known sites use for modules like this?

I think, generating a new record on MySQL for each of new messages will slow down the system and cause problems after a while. What would you suggest me for saving the datas? SQLLite, file system or a high-performance dbms ? (exp: postgresql)

Thanks

share|improve this question
1  
As long at the table holding the messages is properly indexed and using good datatypes, i honestly dont see anything wrong with using a table to store them, the messages need to be stored somewhere and this is what databases are designed to do. – Flosculus Nov 2 '12 at 0:23
After 1-2 million records on MySQL tables various problems occurs, we don't have very powerfull servers. Can you suggest a ligter dbms, unless you insist on MySQL – Mustafa Hergül Nov 2 '12 at 0:39

migrated from stackoverflow.com Nov 4 '12 at 3:09

2 Answers

up vote 1 down vote

You can use a database such as mySQL just fine. Especially if the table structure is simple and indexed well. I would presume a record for each message would create a need for inserts, but not updates, which would keep index rebuilding needs low too.

If you wanted to scale, just create a read-replica for the querying of the messages and send the write queries to the master server.

Want to scale even more, I suppose you could implement some sort of memcached intermediate level, but this would help more on expanding performance on the read side, not the write side particularly (since you'd have to write them eventually, and you could lose them in memcached in the interim potentially).

In short - mySQL is fine. Other options include cloud options from amazon like DynamoDB and/or nosql options, but not essential.

share|improve this answer
I like your suggestion of cloud services – Flosculus Nov 2 '12 at 0:44
Database has already slowed down before this messaging system. Which way will be better for performance: Creating a new database on MySQL Server or installing a new dbms on server apart from mysql? – Mustafa Hergül Nov 2 '12 at 1:17
If optimization of the existing database is not an option, your best best is to upgrade the hardware. Some of the fastest systems use large amount of flash memory which while the database is active, completely fills said memory to get the fastest read/write speeds. Splitting up the database into more that one for the same application 'might' help, but it would be largely superficial. – Flosculus Nov 2 '12 at 1:42
Mustafa - your question is more of a general scaling problem than in particular to a messaging system. Any such answers further would need way more info on your existing setup as we'd essentially be tuning your application which is beyond this scope and could encompass evaluating not just the database, but your application code as well. Solving the problem simply by switching database vendors should be considered after you've done and can explain here your specific application tuning methods. – Ross Nov 2 '12 at 10:53
up vote -1 down vote

You can start with mysql. Keep all your table structures simple, do not use auto increments, and keep to basic data types like varchars, ints, etc. And index the table correctly.

Use pdo to access the database, and also write your own classes to completely abstract out the data access.

This way, when your site gets popular and the performance start to lag, you can easily switch to a higher performance dbms, or better hardware infra to increase performance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.