need help with special server config for a big board

[Mouth]

How to tell if innodb_file_per_table=1 has already been applied to a table?

 

[MattW]

How to tell if innodb_file_per_table=1 has already been applied to a table?

Check in your MySQL data directory. If it has, each table will have it's own .idb file

 

[Mouth]

Check in your MySQL data directory. If it has, each table will have it's own .idb file

Thanks. I'm all good

 

[CTXMedia]

No need for a dump. Set the variable (on a global basis) then do a no-op alter table:

alter table tbl_name engine=innodb;

Will move it out of the system tablespace.

Ah, I didn't know that - handy tip - thanks.

 

[Deebs]

Ah, I didn't know that - handy tip - thanks.

Only problem is that it cannot reclaim the space in the system tablespace. The only way is to dump every database, and rebuild the MySQL InnoDB datafile and then do a reimport. Maybe in the future....

Then again, if the innodb_file_per_table is set to 1 immediately it is not a problem. Also remember you cannot use compression if innodb_file_per_table = 0.

 

[CTXMedia]

It's something I did when I moved servers. My previous server had a single tablespace and had gone through various software changes (test installs/imports etc.) and bloated to beyond 5GB - the real size was nearer to 1.7GB so I set file_per_table on the new box and imported, getting rid of the gigs of whitespace.

 

[Deebs]

I've inherited some MySQL servers here at work with system tablespaces of several hundred gig, in the process of rebuilding them.

 

[FloV]

Thanks again for all your help.

I've switched from php5-cgi to fast-cgi, which boosted the site a lot. It's way smoother then before!

I've also installed Percona today, but I had a trouble with my.cnf file. It seems that there is a problem with innoDB.if I change the my.cnf file and add something with innoDB the following errors appear:

130208 10:53:21 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 536870912 bytes!
130208 10:53:21 [ERROR] Plugin 'InnoDB' init function returned error.
130208 10:53:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130208 10:53:21 [ERROR] Unknown/unsupported storage engine: InnoDB
130208 10:53:21 [ERROR] Aborting

Does anyone know, what this means?

 

[Slavik]

Remove the innodb_log_file_size from your my.cnf

 

[CTXMedia]

Stop MySQL. Rename your current log files (ib_logfile0 and ib_logfile1 to something else - I generally put old on the end of them) and then restart MySQL. It should create the new log files at the size you've set in my.cnf

Rinse and repeat if you need to change the size again or want to experiment with the impact of different size logs.

 

[HWS]

Stop MySQL. Rename your current log files (ib_logfile0 and ib_logfile1 to something else - I generally put old on the end of them) and then restart MySQL. It should create the new log files at the size you've set in my.cnf

Rinse and repeat if you need to change the size again or want to experiment with the impact of different size logs.

You need to make a clean MySQL server stop with fully flushing the tasks if you intend to delete the log files. Otherwise innodb may complain after the restart.

 

[p4guru]

You need to make a clean MySQL server stop with fully flushing the tasks if you intend to delete the log files. Otherwise innodb may complain after the restart.

+1 very important !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[CTXMedia]

You need to make a clean MySQL server stop with fully flushing the tasks if you intend to delete the log files. Otherwise innodb may complain after the restart.

Good advice - might be worth explaining how it is done for Crazy-Achmet (and others) benefit?

 

[FloV]

It worked now, thanks for all the help!

I still have another problem. From day to day (depends how many users are online) the web-server still freezes. The hosting company says it's because of elasticsearch (version 0.18.7) which takes all the memory.

Maybe it has something to do with the configuration of ES?

 

[agison]

It worked now, thanks for all the help!

I still have another problem. From day to day (depends how many users are online) the web-server still freezes. The hosting company says it's because of elasticsearch (version 0.18.7) which takes all the memory.

Maybe it has something to do with the configuration of ES?

Add more RAM, 8G RAM are cheap now.

 

[FloV]

It's not possible to add more RAM, all slots are already taken.

 

[agison]

In your spec, you have only 12GB now. It's up to 32GB.
On big board, Elastic need around 2 - 8GB. 4GB is not safe for your Web Server to handle 1500 users online. You should double them, 12 to 24GB.

Still on Apache?

 

[MYstIC G]

I'd then look at downloading mysqltuner and running that against the DB to see what else is recommends tweaking.

Interesting tool, thanks for the info!

 

[FloV]

Hey everybody,

sadly, i have to push this thread again. After we migrated to new server (32 GB RAM, SSDs Raid 10,...) we experienced new performance issues in the last couple of days. Since our hoster said, it's elasticsearch and java that is having memory issues, i thought that may be the problem (Thread: http://xenforo.com/community/threads/elastic-search-configuration.48611/).

Since Brogan asked if we're having lot of addons, i've tried to figure it out were the performance issues are coming from. In debug mode i've made a shocking experience.

Home: Page Time: 2.6376s - Memory: 111.8338 MB (Peak: 149.6009 MB) - Queries (26, time: 1.1585s, 43.9%)
Inside a forum: Page Time: 1.6145s - Memory: 110.2555 MB (Peak: 149.5995 MB) - Queries (27, time: 0.1854s, 11.5%)
Inside a thread: Page Time: 2.0033s - Memory: 112.9473 MB (Peak: 149.6002 MB) - Queries (37, time: 0.2016s, 10.1%)

Even with all addons disables ($config['enableListeners'] = false) the memory is still way to high

Home: Page Time: 0.8277s - Memory: 82.7706 MB (Peak: 87.5791 MB) - Queries (7, time: 0.0111s, 1.3%)
Inside a forum: Page Time: 0.6992s - Memory: 82.8908 MB (Peak: 87.5777 MB) - Queries (8, time: 0.0108s, 1.6%)
Inside a thread: Page Time: 0.7009s - Memory: 84.1760 MB (Peak: 87.5801 MB) - Queries (8, time: 0.0119s, 1.7%)

So, with all the addons disabled, why is so much memory needed? How can i figure it out? What should i do? I'm pretty helpless in this situation!

 

[AlexT]

xF uses the memory_get_allocated() function to compute memory usage in debug mode. This function profiles the heap (think of it as the PHP runtime memory container) which not only includes the script operation, but also all PHP extensions. Even unused some loaded extensions may consume heap memory. The more extensions, the more memory consumed, and if you're unlucky, you have a buggy extension that is leaking memory.

I would first check what happens when you restart your PHP parent process. Does used memory go down?

Also check what extensions you have loaded and if you need to have them all or if you could disable some without affecting the functionality of your site.

What version of PHP do you use? PHP 5.4 is considered much more efficient in dealing with memory consumption. For instance, memory consumption for simply parsing PHP scripts (require 'xyz.php') goes down by one half using PHP 5.4.

Other tools I could think of to further debug your PHP memory consumption are the Xdebug, memprof, memtrack extensions, and various OS built-in tools (/proc, pmap).