SQL Experts only

[ASTRAPI]

Hello

I have 2 tables locking and i optimize the slow query's but still locking and i was read that i can fix this problem on a few ways that some members recomend me:

Tables:

ibf_posts
ibf_topics

Solutions that i found and have no idea how to add the best one:

1)The best way to improve the mysql query performance is to split the data across maximum tables.
2)Convert tables to Innodb
3)mysql balancing


Your opinion and a solution to keep those query's and also to run fast?

Also how many access a table must have per second to lock?

Thank you

[Mat B]

Careful consideration is paid to optimal performance when developing IP.Board, so modifying indexes alone is not likely to improve performance (else it would have been done already). Although I specialise in MSSQL, not MySQL, my understanding is that many semantics are the same.

There are nonetheless several things you can do to improve performance. The first is that you can install cache engines like Memcache, APC, or Xaccelerator. This means there are less hits to the database period, which can lend a significant boost for frequently used tables. Second if money is of little to no object is you can split reads and writes to different database servers or clusters. Although there's no built in functionality to do that, there is an article in the resources area describing how you can do it.

In response to your question of how many accesses per second can result in a table lock, the answer depends on your engine. My understanding is that MyISAM does not support row locking, which means when a lock occurs it must be a full table lock. These are obviously very bad. InnoDB apparently supports row level locking, which should reduce issues with concurrency, but it has the footprint of a brontosaurus for large tables - make sure you have lots of RAM!

Simple ways you can improve performance if you haven't already include using an engine like Sphinx for search on top of your DB, so search does not need to FULLTEXT scan the posts and topics tables. As a bonus, it has really good results quality. You can also enable "delayed view counter" so that IPB only updates the view count on a thread once an hour or so - as this activity requires a write to the topics table, it necessitates a lock (remember, that's a table lock if you use MyISAM) on every page view!

[ASTRAPI]

What do you think is causing this lock?

| 178881 | cpuser_cpusera | localhost | usercp_mydatab | Query | 21 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=144046 |

[Mat B]

As I mentioned, go to ACP > System Settings > CPU Saving and Optimization and set "Update Topic Views Immediately" to "No".

[ASTRAPI]

Ok i will test it.

How can i set it to run every 1 hour and not 3?

It seems i can't edit the cron from acp :(

Thank you

[Gary.]

Once you change the setting the cron will also change, You may have to update your caches.

[ASTRAPI]

Ok thanks :)

[yacenty]

There are nonetheless several things you can do to improve performance. The first is that you can install cache engines like Memcache, APC, or Xaccelerator. This means there are less hits to the database period, which can lend a significant boost for frequently used tables. Second if money is of little to no object is you can split reads and writes to different database servers or clusters. Although there's no built in functionality to do that, there is an [url=http://community.invisionpower.com/resources/articles.html/_/server-resource-management/split-mysql-readswrites-to-separate-databases-r236]article in the resources area[/url] describing how you can do it.

is it working also with the gallery where there are huge ammount of calls? especialy to update views and comments?
Is it somehow connected with built-in MySQL replication?

Thanks and regards
YacentY

[Mat B]

Reading through it, it seems it would work with any IPB application (even third party ones). I can't answer your question about MySQL replication as I deal with MSSQL which is a whole different beast.

[yacenty]

is there anybody on IPS that could help us with such topics with MySQL?
Unfortunately I have such feeling that You (IPS) do not focus on the performance enough.
On the IPB 2.3 You had more interesting and helpfull topics, but last time I can not see any usefull hints how to improve the speed of my community. I'm sad with this :(

[yacenty]

Hi guys anybody using replication on IPB?

[cthree]

@yacenty: I answered another of your topics but IPS has put more effort into optimizing the software to run pretty well than you have put into running on a server or servers of sufficient capacity for your site. When you run it on a capable hardware you'll see that the effort IPS put into optimizing the software covers a good 80-90% of what can be realistically done with software of this type.

@OP:

Table locks are a feature of MyISAM. Switch to InnoDB and the locking issues will go away. MyISAM is only useful on very low activity sites. Table locks are a message to you: "Use InnoDB".

[yacenty]

but You wrote me somewhere else do not use innodb because it's slow - so now I'm stupid - what should I do? I have about 700 users in gallery and everything is going on 2 tables - pictures and comments, sometimes something on users