Why is LS1Tech so SLOW???
11-15-2005, 05:40 PM
#21
Teching In
Join Date: Nov 2005
Posts: 9
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by technical
MySQL is a strange bitch. There are different table types and varying locking strategies (table/record). But your analysis is correct. If you lock a table when someone is updating/inserting then no one else can update/insert on that table.
11-15-2005, 05:52 PM
#23
Teching In
Join Date: Nov 2005
Posts: 9
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by Brains
technical - you've got it, for the most part 
antagonist - you're an idiot
(and a troll, but we'll get to that later) .
antagonist - you're an idiot
(and a troll, but we'll get to that later) .
. Im the proud owner of a very fast 02 Z28. I am just tired of reading all the stupid posts. A different viewpoint is all it is.
Originally Posted by Brains
VB does a *LOT* of database writes; .
Originally Posted by Brains
If two people post at the same time under MySQL, everyone waits.
11-15-2005, 07:16 PM
#26
TECH Senior Member
iTrader: (10)
Join Date: Mar 2003
Location: North Texas
Posts: 8,009
Likes: 0
Received 0 Likes
on
0 Posts
Antagonist, glad to see that you live up to your handle. This thread WAS a constructive conversation until you came in. If you do not want to offer useful information, feel free to stay out of it from here on out. Just b/c you're butthurt b/c of a little comment on the internet doesn't mean you have to retaliate by acting like a 3 year old. Its very apparent to everyone in here you are the one here that don't know what you're talking about.
Seeing as Brains here has been working on the database for the past few years, I'm more inclined to trust him on this one. Argue with us all you want, its just going to provide me with some humor
Seeing as Brains here has been working on the database for the past few years, I'm more inclined to trust him on this one. Argue with us all you want, its just going to provide me with some humor
11-15-2005, 08:02 PM
#27
TECH Fanatic
iTrader: (2)
Join Date: Sep 2004
Location: South Shore, MA
Posts: 1,713
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by cyphur_traq
Seeing as Brains here has been working on the database for the past few years, I'm more inclined to trust him on this one. Argue with us all you want, its just going to provide me with some humor
By arguing with Brains your just going to waste precious time he could put to the site 
11-15-2005, 10:15 PM
#29
TECH Veteran
iTrader: (8)
Join Date: Jan 2002
Location: Destin FL
Posts: 4,298
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by BlueSix
Kind of ironic....i got a database error message while trying to view the second page of this thread.
11-16-2005, 01:36 AM
#30
TECH Senior Member
Join Date: Apr 2004
Location: Texas
Posts: 6,157
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by Antagonist
Please. Databases are not written in SQL . SQL = structured query language. Queries, get it? It doesnt matter what language is used to create the program using the database . You can execute SQL from WITHIN almost any language. 
If you know what you are doing.
. SQL = structured query language. Queries, get it? It doesnt matter what language is used to create the program using the database . You can execute SQL from WITHIN almost any language. If you know what you are doing.
SQL is the language that extracts/updates/maintains the information to and from the database.
You must have a language that executes the SQL commands because it cannot interface with the user by itself in a simplistic form. Think of it more as a script that needs a runtime interpreter. The point I was trying to make is that this site is not just driven by SQL, there is a whole lot more to it.
But what the hell do I know, I just write and maintain database code for a major corporation.
Originally Posted by Antagonist
I am neither an idiot or a troll
VB or any other language will not do anything its not instructed to do.
VB or any other language will not do anything its not instructed to do.
He is talking about Virtual Bulletin not Visual Basic
You remind me of this new guy at work "he has taken a SQL class in college so he knows everything"
Originally Posted by Antagonist
Ever hear of a buffer file? Guess what it does?
11-16-2005, 07:09 AM
#31
TECH Senior Member
iTrader: (7)
Join Date: Jan 2002
Location: Katy, TX
Posts: 12,754
Likes: 0
Received 0 Likes
on
0 Posts
Antagonist definitely sounds like a wet-behind-the-ears still-in-school kind of kid who thinks he's got a handle on everything computer related because he took a beginners programming class. BECAUSE if he had any experience, he'd fully understand what and why, and not make incorrect statements like " There is no way a whole table would be locked when a record is edited or updated. You are mistake. sir."
Since you posess SOO much knowledge and talent, I'm simply AMAZED you haven't written your own forum software and become a millionaire
Anyway, allow me to enlighten you, grasshopper:
Since you posess SOO much knowledge and talent, I'm simply AMAZED you haven't written your own forum software and become a millionaire
Anyway, allow me to enlighten you, grasshopper:
Originally Posted by MySQL Reference Manual :: 7.3.1 Locking Methods
MySQL 5.0 supports table-level locking for MyISAM and MEMORY tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.
In many cases, you can make an educated guess about which locking type is best for an application, but generally it is difficult to say that a given lock type is better than another. Everything depends on the application and different parts of an application may require different lock types.
To decide whether you want to use a storage engine with row-level locking, you should look at what your application does and what mix of select and update statements it uses. For example, most Web applications perform many selects, relatively few deletes, updates based mainly on key values, and inserts into a few specific tables. The base MySQL MyISAM setup is very well tuned for this.
Table locking in MySQL is deadlock-free for storage engines that use table-level locking. Deadlock avoidance is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.
The table-locking method MySQL uses for WRITE locks works as follows:
*
If there are no locks on the table, put a write lock on it.
*
Otherwise, put the lock request in the write lock queue.
The table-locking method MySQL uses for READ locks works as follows:
*
If there are no write locks on the table, put a read lock on it.
*
Otherwise, put the lock request in the read lock queue.
When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue.
This means that if you have many updates for a table, SELECT statements wait until there are no more updates.
In many cases, you can make an educated guess about which locking type is best for an application, but generally it is difficult to say that a given lock type is better than another. Everything depends on the application and different parts of an application may require different lock types.
To decide whether you want to use a storage engine with row-level locking, you should look at what your application does and what mix of select and update statements it uses. For example, most Web applications perform many selects, relatively few deletes, updates based mainly on key values, and inserts into a few specific tables. The base MySQL MyISAM setup is very well tuned for this.
Table locking in MySQL is deadlock-free for storage engines that use table-level locking. Deadlock avoidance is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.
The table-locking method MySQL uses for WRITE locks works as follows:
*
If there are no locks on the table, put a write lock on it.
*
Otherwise, put the lock request in the write lock queue.
The table-locking method MySQL uses for READ locks works as follows:
*
If there are no write locks on the table, put a read lock on it.
*
Otherwise, put the lock request in the read lock queue.
When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue.
This means that if you have many updates for a table, SELECT statements wait until there are no more updates.
11-16-2005, 07:12 AM
#32
I too have noticed it has been slow, mainly at night.
I'm glad to hear that you guys are working on the problem.
With all the sponsors that LS1tech.com has and what each and everyone pays to be a sponsor, money should not be a problem of making this board faster.
I'm glad to hear that you guys are working on the problem.
With all the sponsors that LS1tech.com has and what each and everyone pays to be a sponsor, money should not be a problem of making this board faster.
11-16-2005, 08:18 AM
#33
12 Second Club
iTrader: (3)
Join Date: Mar 2004
Location: Austin, TX
Posts: 318
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by Brains
Antagonist definitely sounds ...
It's usually a thankless job, but I'll say thank you for your time. After all you did respond to my PM about a database issue a while back.
He has no clue what he's talking about, and everyone can see that.
Antagonist - for running my little site (tiny in comparison to LS1 Tech) you have to use SQL, VBA, HTML & JSP, not to mention anything about the Hardware/Operating System/Applications side.
If you feel froggy, try taking on something like my work environment here at the State of Texas. 6000 concurrent connections, 24/7 support, millions of records, over 1 million lines of code. Daily each programmer has to use SQL, PL/SQL, HTML, JAVA, JavaScript, C++.
We have to support Tablet PC's, wireless access, high speed access, dial up access, encryption, and a million other things, just so our users can access a database. There is so much more to it all than just SQL.
Your statement is like saying "Fixing a cracked block is easy, it's just metal." Which in theory is correct, but for practical usage, is total BS.
How about I give you a hot steaming cup of:
11-16-2005, 08:19 AM
#34
TECH Resident
iTrader: (2)
Join Date: Jan 2004
Location: Tulsa, OK
Posts: 788
Likes: 0
Received 0 Likes
on
0 Posts
Ok so lets look at this from a performance stance 
Can we OVERCLOCK the database? Get some watercooling? Or is this just about code efficiency
J/K
What does corvetteforum.com use? They have some strange archiving that archives things very quickly and if you want to search the archives you have to click on a specific section of the forum.
Just throwing that out there. I'm sure you guys have exhausted all possible routes already.
PS. Ban him Travis!
Can we OVERCLOCK the database? Get some watercooling? Or is this just about code efficiency J/K
What does corvetteforum.com use? They have some strange archiving that archives things very quickly and if you want to search the archives you have to click on a specific section of the forum.
Just throwing that out there. I'm sure you guys have exhausted all possible routes already.
PS. Ban him Travis!
11-16-2005, 09:25 AM
#35
TECH Senior Member
iTrader: (7)
Join Date: Jan 2002
Location: Katy, TX
Posts: 12,754
Likes: 0
Received 0 Likes
on
0 Posts
CF does a lot of archiving, which moves things out of the main post and thread tables. Its kind of a pain in the butt to search because of it, and is the main reason we've been hesitant to follow suit. With me converting all the queries to be SQL92 compliant, it opens us up to run any database server that PHP offers support for (in one form or another, be it a library module or ODBC). There's no abstraction layer in the code however, so the conversion is MUCH more involved than simply changing MySQL-specific language/functions in the queries. I'm having to alter a *LOT* of the source because they coded directly against MySQL and its unique way of doing things. For instance, getting the last inserted auto_increment'ed column's value for a record -- MySQL has a function last_insert_id(), that will pull the last ID without having to specify which table you want the value from. Nobody else is that "lazy" about it, so the code should be changed to specify -- and there's a LOT of locations to do so.
Then there's performance tuning differences between database engines. One of the reasons why switching over to even InnoDB on MySQL doesn't pan out well, is the use of the ever-so-common SELECT COUNT(*). With the MyISAM storage engine, this isn't a costly call at all -- it executes instantaneously because with table locking the number of records is always the total number of rows in the table. With a storage engine that supports multi-level concurrency, you could have any number of records in flux -- inserts, deletes, etc. So, a COUNT(*) is very costly - especially on large tables. vBulletin makes heavy use of COUNT(*) as well, almost every page in fact. That will have to be rewritten as well.
The only "easy" solution I've come up with, and its one I may very well do first, is to segment the site. Basically turn the forums into a group of forums. vBulletin stores all the posts in one table, and all the threads in one table. That means we have over 3 million rows in the post table. It also means the whole thing is locked on updates, which is why the entire site freezes up when locks are held. By segmenting, I can reduce that to one pair of post/thread tables per group of forums, or possibly per forum. That would reduce the number of rows considerably, and severe lock times would only affect one or a small set of forums.
Then there's performance tuning differences between database engines. One of the reasons why switching over to even InnoDB on MySQL doesn't pan out well, is the use of the ever-so-common SELECT COUNT(*). With the MyISAM storage engine, this isn't a costly call at all -- it executes instantaneously because with table locking the number of records is always the total number of rows in the table. With a storage engine that supports multi-level concurrency, you could have any number of records in flux -- inserts, deletes, etc. So, a COUNT(*) is very costly - especially on large tables. vBulletin makes heavy use of COUNT(*) as well, almost every page in fact. That will have to be rewritten as well.
The only "easy" solution I've come up with, and its one I may very well do first, is to segment the site. Basically turn the forums into a group of forums. vBulletin stores all the posts in one table, and all the threads in one table. That means we have over 3 million rows in the post table. It also means the whole thing is locked on updates, which is why the entire site freezes up when locks are held. By segmenting, I can reduce that to one pair of post/thread tables per group of forums, or possibly per forum. That would reduce the number of rows considerably, and severe lock times would only affect one or a small set of forums.
11-16-2005, 09:33 AM
#36
TECH Veteran
Originally Posted by SDC
Ok so lets look at this from a performance stance Can we OVERCLOCK the database? Get some watercooling? Or is this just about code efficiency
J/K
What does corvetteforum.com use? They have some strange archiving that archives things very quickly and if you want to search the archives you have to click on a specific section of the forum.
Just throwing that out there. I'm sure you guys have exhausted all possible routes already.
PS. Ban him Travis!
Can we OVERCLOCK the database? Get some watercooling? Or is this just about code efficiency J/K
What does corvetteforum.com use? They have some strange archiving that archives things very quickly and if you want to search the archives you have to click on a specific section of the forum.
Just throwing that out there. I'm sure you guys have exhausted all possible routes already.
PS. Ban him Travis!
11-16-2005, 10:19 AM
#37
TECH Addict
Join Date: Mar 2004
Location: Fat Chance Hotel
Posts: 2,336
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by Antagonist
There is no way a whole table would be locked when a record is edited or updated. You are mistake. sir.
Originally Posted by Antagonist
Please. Databases are not written in SQL . SQL = structured query language. Queries, get it? It doesnt matter what language is used to create the program using the database . You can execute SQL from WITHIN almost any language.
If you know what you are doing.
If you know what you are doing.
11-16-2005, 10:21 AM
#38
TECH Addict
Join Date: Mar 2004
Location: Fat Chance Hotel
Posts: 2,336
Likes: 0
Received 0 Likes
on
0 Posts
Originally Posted by Brains
There's no abstraction layer in the code however, so the conversion is MUCH more involved than simply changing MySQL-specific language/functions in the queries.