Can Joomla handle Queries for Millions of Users in

Question

I am almost done developing a large website.

The only problem is i get MySQL gone away errors all the time...

I posted another question here on stackoverflow but did not get my answer

So my Question is is Joomla fit enough or right CMS solution for a large users ( 5 million users)?

I have spent almost 5 months developing this... and now although i am on a Quad dedicated server (2 GB Ram) i feel that Joomla maybe perhaps not a right solution for this large database website..

Edit : I just want to make this clear I am not talking about the traffic...its brand new site. I am talking about the number of rows in some tables

Please advise

MySQL Server info :

MySQL error log: /var/lib/mysql/eta.etalenthunt.com.err

root@eta [~]# cat /etc/my.cnf
[mysqld]
safe-show-database
open_files_limit = 5000
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_limit=1M
query_cache_size=100M ## 32MB for every 1GB of RAM
query_cache_type=1
max_connections=100
collation_server=utf8_unicode_ci
character_set_server=utf8
delayed_insert_timeout=40
interactive_timeout=30
wait_timeout=60
connect_timeout=60
thread_cache_size=64
key_buffer=32M ## 32MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=2048
record_buffer=1M
sort_buffer_size=3M ## 1MB for every 1GB of RAM
read_buffer_size=3M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=3M ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2
myisam_sort_buffer_size=16M
innodb_file_per_table=1
innodb_buffer_pool_size=18M ## (>= 18M)

i am running dedi server XEON QUAD 2 GB

Answer 1

OK! No problem. You have to realize that this is a common probelm with Joomla and Wordpress (as I explained to you in my other answer to your another question here: Is Joomla 2.5 much faster than Joomla 1.5 Querywise )

Since you are working on it for past 6 months, and you already know its a huge huge database behind this website so what I am talking is exactly what can help you in limited boundaries of Joomla CMS.

I see this is an issue that you cant deal easily within any Joomla CMS! But there are some ways (in my opinion) that you can reduce the load a little here and there.

You can follow both of these steps, there could be more but lets try these two first:

Solution #1: Split database into 2 or more databases (I will explain how to do that)
Solution #2: Hack Joomla core that hits user and session tables
Solution #3: Split jos_users table into equal number of records
Solution #4: Write a cronjob that cleans the session table

Solution # 1 :

Step 1: Take your jos_users and jos_session table into two new separate databases. call them db_jos_user and db_jos_session

Step 2: Drop jos_users and jos_session tables from the main database.

Step 3: Create views for each of them with their respective names and point the tables using that db name.

CREATE VIEW jos_users AS SELECT * from db_jos_user.jos_users;
CREATE VIEW jos_session AS SELECT * from db_jos_session.jos_session;

This will actually reduce your database size and actually reduce the load on your database too. Joomla wouldnt be surprised and it wont know if its view or the table behind it.

Solution # 2 :

Hack user authentication plugin and to authenticate users from different database by creating new instances of the database (that you split in following solution). You may develop a logic to determine which table to hit for which users. That way you might be able to reduce the load on your database(s). You might also have to implement a logic to insert/update users into their respective database. This is where you have to work on inside the Core User component and user login modules.

Solution # 3 :

You can split the tables into more database(s). In your custom authentication module, try to hit the databases one by one in a single query to all databases together using 'union' or one by one (that way you might save some database hits). You can sort them on username so that when they login you will know which database to hit. That will reduce your hits in great number.

Solution # 4 :

Write a cron job, that runs on a set interval which cleans the session table. Session table contains all info that belongs to a guest or a user. So you have to keep in mind that you clean it on regular basis. You can also do this if a user session is inactive for max 20 minuets or whatever you want you can delete it. You might need to put a notice on your website telling a user session if active more than 20 minutes will automatically be deleted or something more user friendly.

Conclusion:

Despite it seems silly when you are slicing your database and one table in particular into many pieces but actually its ideal time for you to do that. It would not cost much of your time neither it wont harm your Joomla/Database. Its very easy to reverse though!

I hope all these would work for you. I dont see any problem in doing all these together except it gives you an overhead of work but actually its acceptable when you have +5mn records in your database.

I really really hope this will help you a lot in doing all these things together.