bigsoccer tech
Thu 23rd Feb '06, 1:14am
Our site is huge - with a 20 minute cookie we are 1400-2000 people online simultaneously all day long, 24 hours a day. And the World Cup is coming up which will make traffic, er, triple.
We're having massive table locking issues. Its been discussed at length in this thread. We have moved search onto its own server (spec below) and the problems went away but traffic quickly filled up the released capacity. Below you will find a summary of advice in this thread regarding it, all of which is leading me to switch some tables to InnoDB. Yet I am not convinced...
Here is a summary of our problem (discussed at length with Eva2000 here http://www.vbulletin.com/forum/showthread.php?t=171957 (http://www.vbulletin.com/forum/showthread.php?t=171957%29:) )
Our server specs. For example:
4x Web servers: Intel Dual Xeon 2.8 GHz, 2 GB Ram, 2 X 80 GB with Raid 1
2x DB servers: Intel Dual Xeon 2.8 GHz, 8 GB Ram, 3 X 73 GB SCSI U320 10K RPM with Raid 5
2x Load balancers: Dual P4 3.0 ghz, 1gb Ram
We are using replication for search/backup. 3.5 slave/master is not ready for primetime as you know, and we have no eta on 3.6 when it will be implemented.
We upgraded to MySQL 4.1.16. Pconnect is off. Max threads always reached.
In the meantime, our host seems to feel that this is the process that gets locked and brings us to our max_connections limit:
6693045 root 10.10.0.12:49017 bigsoccer_vb_353 Query 32 Copying to tmp table SELECT DISTINCT user.userid, user.username, post.ipaddress\n\t\tFROM post AS post,\n\t\tuser AS user\n\t\tWHE Any idea why this would lock for so long/so badly? Any way to avoid it?
Any and all help advice etc appreciated. We are suffering.
-------------
We run user and thread as innodb which solves 90% of our contention/locking problems. This means no fulltext searching on thread titles, but whatever, I'd rather have less table locking.
-------------
I've been battling locking issues for the last 3 weeks. When I'd hit 900 - 1200 users online mysql threads would back up becuase of locking, I'd hit max connections, and get vbull can not connect errors. After a lot of investigating and SHOW FULL PROCESSLIST during these times it was always queries running against the thread table. My guess is that table gets hit a lot for concurrent reads and writes, even more so than post.
-------------
You can't use innodb for post / thread as they dont support fulltext searching.
-------------
I think you might benefit most from:
Innodb Post / Thread table
Slave server running post / thread as myisam so you can use fulltext. In this case all searches would be deferred to the slave server not slowing down your board but being slightly out of date.
----
We decided to convert all our tables to InnoDB. The good news is the new post query is now down to 0.15 and feels snappy. Threads come up fast, the forum home comes up fast, we no longer see catastrophic lockups, reply is fast and our users think it went into hyper drive except....search. Searches are now, as expected, 15-45 seconds. Woof.
We offer a Google search option that most of our users like because it's so fast and does 2 & 3-letter words. We'll probably either optimize the queries that now use count(*) and so many table joins, or do the full-text search slave as the next step.
----
We ended up switching to INNODB as well... our locks were through the roof. Everything is running MUCH faster now, but we can't merge threads or delete users:
----------
We have a script that runs in cron once a minute and kills processes if there is a certain threshold of locked processes. It looks for queries that have been sending data or copying to temp table for over x seconds.
It’s a patch job and not a real fix.
--------
When I changed my table types to InnoDB, which use row-level locking, the queries were no longer locking up access to the database. I don't know if it has made the forum faster overall, BUT, one query is no longer holding up an entire queue of others waiting for access to the same table.
----------
Also, when I used InnoDB, I found it slower than MyISAM - it was only "faster" because of row-level locking - take away the locking issue and I found it to be slower overall.
-------
One thing I have read is that the MySQL master post table can be InnoDB and the slave post table can be MyISAM with the full text index. This is because how MySQL handles replication using binary logging. It’s worth a try on a couple of our large sites to eliminate the table locking problems.
We're having massive table locking issues. Its been discussed at length in this thread. We have moved search onto its own server (spec below) and the problems went away but traffic quickly filled up the released capacity. Below you will find a summary of advice in this thread regarding it, all of which is leading me to switch some tables to InnoDB. Yet I am not convinced...
Here is a summary of our problem (discussed at length with Eva2000 here http://www.vbulletin.com/forum/showthread.php?t=171957 (http://www.vbulletin.com/forum/showthread.php?t=171957%29:) )
Our server specs. For example:
4x Web servers: Intel Dual Xeon 2.8 GHz, 2 GB Ram, 2 X 80 GB with Raid 1
2x DB servers: Intel Dual Xeon 2.8 GHz, 8 GB Ram, 3 X 73 GB SCSI U320 10K RPM with Raid 5
2x Load balancers: Dual P4 3.0 ghz, 1gb Ram
We are using replication for search/backup. 3.5 slave/master is not ready for primetime as you know, and we have no eta on 3.6 when it will be implemented.
We upgraded to MySQL 4.1.16. Pconnect is off. Max threads always reached.
In the meantime, our host seems to feel that this is the process that gets locked and brings us to our max_connections limit:
6693045 root 10.10.0.12:49017 bigsoccer_vb_353 Query 32 Copying to tmp table SELECT DISTINCT user.userid, user.username, post.ipaddress\n\t\tFROM post AS post,\n\t\tuser AS user\n\t\tWHE Any idea why this would lock for so long/so badly? Any way to avoid it?
Any and all help advice etc appreciated. We are suffering.
-------------
We run user and thread as innodb which solves 90% of our contention/locking problems. This means no fulltext searching on thread titles, but whatever, I'd rather have less table locking.
-------------
I've been battling locking issues for the last 3 weeks. When I'd hit 900 - 1200 users online mysql threads would back up becuase of locking, I'd hit max connections, and get vbull can not connect errors. After a lot of investigating and SHOW FULL PROCESSLIST during these times it was always queries running against the thread table. My guess is that table gets hit a lot for concurrent reads and writes, even more so than post.
-------------
You can't use innodb for post / thread as they dont support fulltext searching.
-------------
I think you might benefit most from:
Innodb Post / Thread table
Slave server running post / thread as myisam so you can use fulltext. In this case all searches would be deferred to the slave server not slowing down your board but being slightly out of date.
----
We decided to convert all our tables to InnoDB. The good news is the new post query is now down to 0.15 and feels snappy. Threads come up fast, the forum home comes up fast, we no longer see catastrophic lockups, reply is fast and our users think it went into hyper drive except....search. Searches are now, as expected, 15-45 seconds. Woof.
We offer a Google search option that most of our users like because it's so fast and does 2 & 3-letter words. We'll probably either optimize the queries that now use count(*) and so many table joins, or do the full-text search slave as the next step.
----
We ended up switching to INNODB as well... our locks were through the roof. Everything is running MUCH faster now, but we can't merge threads or delete users:
----------
We have a script that runs in cron once a minute and kills processes if there is a certain threshold of locked processes. It looks for queries that have been sending data or copying to temp table for over x seconds.
It’s a patch job and not a real fix.
--------
When I changed my table types to InnoDB, which use row-level locking, the queries were no longer locking up access to the database. I don't know if it has made the forum faster overall, BUT, one query is no longer holding up an entire queue of others waiting for access to the same table.
----------
Also, when I used InnoDB, I found it slower than MyISAM - it was only "faster" because of row-level locking - take away the locking issue and I found it to be slower overall.
-------
One thing I have read is that the MySQL master post table can be InnoDB and the slave post table can be MyISAM with the full text index. This is because how MySQL handles replication using binary logging. It’s worth a try on a couple of our large sites to eliminate the table locking problems.