We build. You grow.

Get best community software here

Start a social network, a fan-site, an education project with oxwall - free opensource community software

Mysql join indexes | Forum

Christophe Tricaud
I ran mysqltuner to discover two strange things:


1/ I have more tha 300 000 tables open at the same time


2/ 400 join indexes are missing


This is the reason of the slowliness of my server!


Do you know how to create automatically all these indexes (I had a tool of optimisation witth mysql that dit the job, is ther on with mysql)?


Any comment is well come


Christophe,


Paris, France

dave Team
dave Jun 22

If you are running a newer version of php then you most likey wont be able to use a mysql tool to improve things.


Honestly im not sure what you have done with these utilities you have been running, they may have done more harm than good. 


I will need to do some research on that tuner utility.


Also i will need to move this thread to another area as the issue does not seem to be caused by core files and settings.

The Forum post is edited by dave Jun 22
dave Team
dave Jun 22
Topic was moved from Bug reports and troubleshooting.
Christophe Tricaud
This is quite a classic tools running on perl.


For the number of open table juste type the query:

show global status like 'opened_tables';


I don't understand what the php version has to do withe join indexes which are to be defined in the mysql database. I will monitor the queries that take too much time and will have to work one by one to optimize the query and the underlying indexes.


Has anyone implemented a proxysql? Since I host myself on my server, I can do whatever I need easely, but without any support but yours!


Christophe


dave Team
dave Jun 22

I ask because i am not familar with that tool and how it applied to php mysql, now i know it is perl .


Oxwall has always been heavy on IO, pings, joins, and cron.  It was not designed to run million member sites.  I dont know what the exact breakpoint is but at some point when the active user numbers get too high, the crons start tripping over each other and im sure other issues occur such as possible race conditions.   The script will have to be substantially tweaked under that environment.


There was a old discussion about this very thing, ill try to find it for you.

The Forum post is edited by dave Jun 22
Patricia Zorrilla Leader

I have seen websites with 550k members ....

And it works fine.

dave Team
dave Jun 22

Really, thats great...  i have edited my post..   


Patricia Do you remember that conversation way back with tammy like in 2014 i think it was where we all discussed the member limitations?

I cant find it..


And it does make a difference if those members are active or if they are just sitting in the DB doing nothing.

The Forum post is edited by dave Jun 22
dave Team
dave Jun 22

I think i found it...


https://developers.oxwall.com/forum/topic/46944



dave Team
dave Jun 23

in the ow_includes folder in define.php have you all noticed this little code here


if ( !defined("OW_SQL_LIMIT_USERS_COUNT") )
{
    define("OW_SQL_LIMIT_USERS_COUNT", 10000);
}


I dont know anything about it yet, but i just thought i would share.

The Forum post is edited by dave Jun 23
Christophe Tricaud
This parameter is used in user_dao.php to limit the number of users that are returned from the query. There is poor impact in performance. However I have not a lot of members since the website is just open!


But changing some parameters in the mysql.cnf change a lot on the speed. I have 128 GB of ram so I put ll the database in memory

dave Team
dave Jun 23
good info thanks :)
Marcus
Marcus Jun 23
Hi folks currently oxwall limits users output to 500: BOL_SearchService::USER_LIST_SIZE
Marcus
Marcus Jun 23

Christophe Tricaud hi brother. There is something really messed up with your script that prevents mysql closing connection! U gotta investigate that as it will not only stall but also crash your website.


My script opens about 300 to 500 sql connections on each load but they got closed as you'd except. So if your don't do that there is a plugin or custom work that prevents that.


300 000 open connections that sounds like your running a facebook or similar site lol

Christophe Tricaud
Yes with only a couple of members! I suspected that connection where not closed so I think there is a plugin that is missing something. I have to find which one.


I am really enthusiastic with the oxwall platform but I have ton say that the plugins are not at the same level. I have so many troubles with them!


I just had to deactivate the anti-spam one so I can run the blogs!

Senior Developer Leader
Yes, missing indexes are a true issue in some plugins and I think the Oxwall core has some missing as well, I can't confirm right now but I remember that I had this issue fixed on one customer's website.


Marcus, which PHP version and MySQL/MariaDB version are you running in your website?


There was a plugin named "OXART SPEED OPTIMIZER", I had this in a testing website and It was working great for speeding up the full website and the mysql queries. I don't know if this plugin is coming back to the store as it is suspended right now.


Senior Developer.

Chris_W
Chris_W Jun 23
Oxart Speed optimizer is listed a s a free plugin, if it's not in the store I can attatch a copy.
Patricia Zorrilla Leader

I bought the OxArt Speed Optimizer plugin from the author Sardar https://developers.oxwall.com/user/cst

For 35 USD, then it was free and then it was suspended.

The URL was this: https://developers.oxwall.com/store/item/547

When I installed it, my server was small and could not handle so many users and with this plugin installed it still worked slower, so I uninstalled it and I have not worried anymore. I expanded the server and that was a very good solution. More "cores" and more RAM.

I keep the plugin, if someone needs it I send it to them.

Chris_W
Chris_W Jun 23
I didn't see any improvement using memcached in the plugin, and only a small improvement with static content using APCu