说明:mysql动态修改的配置参数有两种:
会话session:只对当前会话产生影响,退出mysql后失效
全局GLOBAL:对以后的mysql的连接都生效的,重启mysql后失效(reload重载不会)
注意:如果mysql重启后这些将失效(reload重新加载不会失效),即mysql的配置文件生效,如想永久保存,必须在mysql的配置文件/etc/my.cnf做修改保存
登录到mysql的命令行后:
查看mysql的参数配置情况:show variables;
查看带有log字符的参数:show variables like '%log%';
set session sort_buffer_size = 524288;(有些配置参数只允许修改全局GLOBAL)
更改参数会话 GLOBAL 设置(如 expire_logs_days):
set GLOBAL expire_logs_days = 10;
How to optimize database to speed Magento up | Small tips
Trying to provide quality support of all our extensions, we work with a great number of Magento stores. Every customer is unique for us and we try to offer individual approach to everyone. We also examine each Magento store performance and the reasons of slow work. We found some common reasons, which can be applied almost to every store. The problem is quite simple. It’s database default settings that don’t allow your Magento to work like you want. These settings are:
- innodb_buffer_pool_size
- innodb_additional_mem_pool_size
- key_buffer_size
Most Magento stores have the default values for them (8Mb, 1Mb and 8Mb accordingly). We’ll try to describe what settings mean and how you can change them.
1) Let’s start with “key_buffer_size”. It is responsible for the buffer of the indexes of MyISAM database. These indexes are used in Magento only for fulltext search products in the tables like “catalogsearch_fulltext” and others. That’s why we recommend to set the size of “key_buffer_size” setting to be equal to the size of this table (“catalogsearch_fulltext”). If you regularly add more products, the size of this table will be increased. It’s better to specify the value for “key_buffer_size” with some reserve for such changes.
If you setup several Magento on one server, this setting should be the sum of sizes of “catalogsearch_fulltext” table in all your Magento stores.
2) The next setting is “innodb_buffer_pool_size”. It’s one of the most important parameters for work with innoDB tables and indexes.
Example: if your server has 1Gb RAM, it’s better to allow the following settings to use 30-40% of this memory: innodb_buffer_pool_size + innodb_additional_mem_pool_size + key_buffer_size
innodb_buffer_pool_size = 320 Mb
innodb_additional_mem_pool_size = 30 Mb
key_buffer_size = 50 Mb
There are also many other minor settings that you should check. But for that we recommend to contact your hosting provider or system administrator. Please note, that none extension will speed up your Magento if you do not optimize your database.