MySQL, heavy use, and PHP
-
Basically right now I run various web sites that kind of all tie into one another to make a large community. They share one database and I use various caching methods so it hasn't really become a problem, ... yet. One of the web sites is also a lyrics web site, and two other sites can grab said lyrics because they're music related. I recently got a database dump of 100K lyrics, totaling about 250MB in size. I'm wondering if adding this to my database will cause any read/write latency with other tables - and if there is a better method to storing this than just adding it to my current database.
I had an idea that I could use another database server just for music-related stuff, lyrics, songs, etc and connect to it, or use XML or something, to grab the lyrics and such when I need them. I'm not sure if this is a good idea or what should be done at all. If anyone has any suggestions I'd greatly appreciate it. I have never worked with large databases before that will get a lot of read/writes (especially reads).
-
@helpfulcorn said:
I'm wondering if adding this to my database will cause any read/write latency with other tables
It shouldn't, MySQL stores each table as its own file/set. A database is just a directory on the file system.
As for how databases actually work for reading/writing don't worry about it, read operations tend to be quite fast as the actual file structure of the database is designed with frequent reads in mind. You shouldn't hit any kind of speed barrier unless you're using giant sql statements or push a freakishly large amount of data in (which just gets appended anyways afaik).
-
I agree with Ling.
Pump the data in during off hours. It should just take a few minutes, then you're off to the races again, assuming you've built useful indices.
-
-
Errr, the replies so far are a little naive, it seems to me.
The performance of your database almost entirely depends on the indexes you have on each table compared to the queries/updates you are going to run on them,. If your queries "hit" an index (and you have up-to-date indexing and statistics), then performance will remain generally good. If you run queries that cannot use an index, then, as your tables grow, you will get sudden degradation in performance as the queries start to run table scans that data cache can no longer cope with. You're probably doing the right thing but understanding your indexes is everything. There's another Forum entry here somewhere called "MySQL Indexes.." or something, which should help you with this (including a long diatribe by me).http://forums.thedailywtf.com/forums/t/10887.aspx
-
@vr602 said:
...The performance of your database almost entirely depends on the indexes you have on each table compared to the queries/updates you are going to run on them,. If your queries "hit" an index (and you have up-to-date indexing and statistics), then performance will remain generally good...
Thanks for the other forum post, but I'm fairly familiar with indices, I use them whenever I know I'll need them and I try to keep things running as smoothly as possible. Main main worry was dealing with databases that are very, very large. I've read a lot about sharding and all that, but it just seems to make things more complicated for myself, instead I'd rather do all I can to avoid that for the main time, and maybe go to load balancing, memcached, etc when the time happens.
-
@helpfulcorn said:
@vr602 said:
...The performance of your database almost entirely depends on the indexes you have on each table compared to the queries/updates you are going to run on them,. If your queries "hit" an index (and you have up-to-date indexing and statistics), then performance will remain generally good...
Thanks for the other forum post, but I'm fairly familiar with indices, I use them whenever I know I'll need them and I try to keep things running as smoothly as possible. Main main worry was dealing with databases that are very, very large. I've read a lot about sharding and all that, but it just seems to make things more complicated for myself, instead I'd rather do all I can to avoid that for the main time, and maybe go to load balancing, memcached, etc when the time happens.
100K isn't "very very large", not even "very large", perhaps only just "large".
If however you are actually working with very large tables with a few milion rows then a good step is to see if partitions might help you. Supported since mysql 5.1.
-
@stratos said:
@helpfulcorn said:
@vr602 said:
...The performance of your database almost entirely depends on the indexes you have on each table compared to the queries/updates you are going to run on them,. If your queries "hit" an index (and you have up-to-date indexing and statistics), then performance will remain generally good...
Thanks for the other forum post, but I'm fairly familiar with indices, I use them whenever I know I'll need them and I try to keep things running as smoothly as possible. Main main worry was dealing with databases that are very, very large. I've read a lot about sharding and all that, but it just seems to make things more complicated for myself, instead I'd rather do all I can to avoid that for the main time, and maybe go to load balancing, memcached, etc when the time happens.
100K isn't "very very large", not even "very large", perhaps only just "large".
If however you are actually working with very large tables with a few milion rows then a good step is to see if partitions might help you. Supported since mysql 5.1.
I'm glad someone mentioned this. 100K records isn't large by any stretch of the imagination. The only way you are going to see performance problems is if you have this on a really slow drive, or you are doing full text searches of the lyrics.
-
