Forumi lagittaa?
- Keskustelun aloittaja taicaviitta
- Aloitettu
Jokainen voi auttaa ja etsiä vbulletin softan optimoimiseen matskua. Nyt olemme siirtymässä forumimaailmassa "huge" sanan alle, eli pääsemme miljoonan postin jälkeen vbulletin forumin listaukseen suurista forumeista. Pelaamme eri tasolla kuin pikkuforumit, eli tämän softan pyörittäminen on raskaampaa.
- Liittynyt
- 21.10.2005
- Viestejä
- 10 950
- Liittynyt
- 21.10.2005
- Viestejä
- 10 950
On sieltä joku toinenkin kysynyt optimointiapua ja näytti toimivan.
http://www.vbulletin.com/forum/showthread.php?t=177493
http://www.vbulletin.com/forum/showthread.php?t=177493
- Liittynyt
- 29.4.2004
- Viestejä
- 257
- Ikä
- 45
Jonny sanoi:
Tuo ongelma johtuu mysql vain taulukohtaisesta lukituksesta. Eli kun joku haluaa postittaa uuden postin lukitaan koko taulu postin kantaan lisäämisen ajaksi. Tällöin kaikki muut muokkaus/uudet postitukset jäävät jonoon odottamaan lukituksen aukeamaista. Lukituksen aukeamisen jälkeen käsitellään seuraava pyyntö jne. yksitellen. Lisäksi ruuhka-aikoina vbulletinin sisäänrakkennettu haku myös nostaa palvelinkuorman kattoon kun postien määrä lähestyy miljoonaa, eli käsittelyajat sen kun kasvaa ja syöksykierre on valmis. Eli kun jengiä on palstalla paljon ja posteja kirjoitellaan runsaasti voi kuvailemasi asia tapahtua. Ongelman ratkaisisi jos mysql osaisi lukita vai ko. rivin mihin kirjoitetaan kannasta, kuten monet muut fiksut tietokantasoftat. Niin ja ennen kun joku sanoo että miksi ei käytetä jotain muuta tietokantasoftaa niin vastaus on yksinkertainen, vbulletin ei tue tällä hetkellä muita kun mysql:llää. Versio 4.0 on lupailtu uusia tukia.
- Liittynyt
- 21.10.2005
- Viestejä
- 10 950
http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/
The following list describes some ways to avoid or reduce contention caused by table locking:
Try to get the SELECT statements to run faster. You might have to create some summary tables to do this.
Start mysqld with --low-priority-updates. This gives all statements that update (modify) a table lower priority than SELECT statements. In this case, the second SELECT statement in the preceding scenario would execute before the INSERT statement, and would not need to wait for the first SELECT to finish.
You can specify that all updates issued in a specific connection should be done with low priority by using the SET LOW_PRIORITY_UPDATES=1 statement.
You can give a specific INSERT, UPDATE, or DELETE statement lower priority with the LOW_PRIORITY attribute.
You can give a specific SELECT statement higher priority with the HIGH_PRIORITY attribute.
Starting from MySQL 3.23.7, you can start mysqld with a low value for the max_write_lock_count system variable to force MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of inserts to the table occur. This allows READ locks after a certain number of WRITE locks.
If you have problems with INSERT combined with SELECT, switch to using MyISAM tables, which support concurrent SELECT and INSERT statements.
If you mix inserts and deletes on the same table, INSERT DELAYED may be of great help.
If you have problems with mixed SELECT and DELETE statements, the LIMIT option to DELETE may help.
Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter.
You could change the locking code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.
Here are some tips about table locking in MySQL:
Concurrent users are not a problem if you don't mix updates with selects that need to examine many rows in the same table.
You can use LOCK TABLES to speed up things (many updates within a single lock is much faster than updates without locks). Splitting table contents into separate tables may also help.
If you encounter speed problems with table locks in MySQL, you may be able to improve performance by converting some of your tables to InnoDB or BDB tables
The following list describes some ways to avoid or reduce contention caused by table locking:
Try to get the SELECT statements to run faster. You might have to create some summary tables to do this.
Start mysqld with --low-priority-updates. This gives all statements that update (modify) a table lower priority than SELECT statements. In this case, the second SELECT statement in the preceding scenario would execute before the INSERT statement, and would not need to wait for the first SELECT to finish.
You can specify that all updates issued in a specific connection should be done with low priority by using the SET LOW_PRIORITY_UPDATES=1 statement.
You can give a specific INSERT, UPDATE, or DELETE statement lower priority with the LOW_PRIORITY attribute.
You can give a specific SELECT statement higher priority with the HIGH_PRIORITY attribute.
Starting from MySQL 3.23.7, you can start mysqld with a low value for the max_write_lock_count system variable to force MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of inserts to the table occur. This allows READ locks after a certain number of WRITE locks.
If you have problems with INSERT combined with SELECT, switch to using MyISAM tables, which support concurrent SELECT and INSERT statements.
If you mix inserts and deletes on the same table, INSERT DELAYED may be of great help.
If you have problems with mixed SELECT and DELETE statements, the LIMIT option to DELETE may help.
Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter.
You could change the locking code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.
Here are some tips about table locking in MySQL:
Concurrent users are not a problem if you don't mix updates with selects that need to examine many rows in the same table.
You can use LOCK TABLES to speed up things (many updates within a single lock is much faster than updates without locks). Splitting table contents into separate tables may also help.
If you encounter speed problems with table locks in MySQL, you may be able to improve performance by converting some of your tables to InnoDB or BDB tables
