MySQL: 1 DB with 1k Tables ?

Anon

Hi,

I wonder what is better (means: faster, more stable) for a MySQL database with ~700 connections per hour: One Database with around 1.000 tables or 10 databases with 100 tables each? I guess the latter option is smarter?

I am using an Apache Redhat Linux 7.1 an the newest stable MySQL release - while fighting with the problem that sometimes the DB simply hangs when I have ~500 threads running. They aren't processed anymore, standing just there. I can view the processlist, but shutdown & restart wont't work though.

The Server is a dual p3 800 MHZ with 0.5 GB RAM.

Any (urgently needed) help is really appreciated!

Thank You,
Alan

Anon

appendix: the tables have between 50 and 50.000 rows each. when running, most SELECTs are done from small tables with ~50 rows, AND at the same time there are INSERTs to the tables with >10k rows.

[deleted]

If you are using the 'old' mysql, then you're stuck with table-level-locking, which means all the selects on a table have to wait for the inserts to finish.

If you are using MySQL 4.0+, then it really doesn't matter wether you use many small tables of one large table, because the row-level-locking means that inserts nolonger interfere with selects.

a few side notes:
1. what the f*ck do you need 1k tables for? I smell a flawed database design.

0.5 GB is waaaaaay too little for 500 connections. one database thread takes at least 2.5MB, 500*2.5 = 1250MB. That's just the MYSQL threads, then theres mysql's cache and the OS, I recommed at least 2GB or RAM.
Have you thought about move to a real database, such as PostgreSQL?

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

Hi vincent,

thanks a lot for your reply.

re: your side notes...

1) Well, it is an adserver and the tables are used for that many banner accounts (view & click logging) by different customers. The design is thought through although of course optimizable...

2) ah, so thats the weak point. guess, i'll have to upgrade ram. What a pity, RAM prices went up 🙂

3) not yet, i need to do some reading on that. i wonder if migration and compatibility with the current php code is a big issue? well, i'll have to read, thanks for the hint.

[deleted]

With table-level-locking in MySQL, your database 'makes sense' from a locking point of view.
If locking wasn't an issue, then having one table for each account is not good. Not only are you storing the exact same record structure in more than one table, your database model also limits the number of clients. If you want to add a client, you must add a table and tell your application to start using that table.

A more optimized design would use one large table for all the 'hits' and just store the clientid in the record along with the hit information.

2) 'Add more RAM' is the default answer to any performance question about databases 🙂.
Fortunately it is also the cause of 80% of all performance issues.

3) PostgreSQL does not differ much from MySQL from a PHP point of view, you can use many of your current queries on PostgreSQL too. As for migration, PGadmin2 has a mysql2pgsql migration tool (free) that will do the migration for you.