Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)

[Joshua Dickerson]

SMF checks if compression is enabled in the ini.

[TechTitan]

SMF checks if compression is enabled in the ini.

the zlib or gzhandler or both?

[008Rohit]

@ TechLib

Though I know a little about php and stuffs, Still I think its zlib in case of php.ini

[Something like that]

zlib and gzhandler are basically the same thing. They both gzip (a compression format) the content as it's sent out.

It's generally better to let the web server (Apache, Nginx, etc) handle the gzipping.

[TechTitan]

zlib and gzhandler are basically the same thing. They both gzip (a compression format) the content as it's sent out.

It's generally better to let the web server (Apache, Nginx, etc) handle the gzipping.

no wonder we can not enable it both simultaneously in php.ini. i have mod_gzip commented in .htaccess as not to double compress the php files...perhaps i should disable it in php.ini and enable the mod_gzip instead...

[Joshua Dickerson]

Yeah, I'd do it with Apache. Less configurations to change.

[Warlock666]

Hi @all,

I tried to put smf_sessions into MEMORY instead if INNODB ( mysql slowlog tells me that a lot of slow querys arre updates in smd_session ).

I tried :

mysql> CREATE TABLE smf_sessions ( session_id char(32) NOT NULL, last_update int(10) unsigned NOT NULL, data text NOT NULL, PRIMARY KEY (session_id)
    -> ) TYPE=MEMORY;

Unfortunaly I just got :

ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

Does someone have an idea what kinds of var type I could use instead ?
Btw : MySQL Version : 5.1.41

Thank you very, very much !

Bye Dirk

[Something like that]

Try:

Code Select Expand


CREATE TABLE smf_sessions ( session_id char(32) NOT NULL, last_update int(10) unsigned NOT NULL, data varchar(16384) NOT NULL, PRIMARY KEY (session_id)) TYPE=MEMORY;


A varchar that long has worked fine for me.

[Warlock666]

Thanks a lot - works perfectly ! 

[Warlock666]

I got some other issue I need a little hint for.

I gow a lot of slow querys when SMF is updateing tables.

Especially when increasing the view counter

# Time: 110610 18:37:51
# User@Host: smf[smf] @ localhost []
# Query_time: 7.376958  Lock_time: 0.000066 Rows_sent: 0  Rows_examined: 0
SET timestamp=1307723871;
UPDATE smf_topics
                        SET numViews = numViews + 1
                        WHERE ID_TOPIC = 27515
                        LIMIT 1;


and updating smf_members

# User@Host: XXX[XXX] @ localhost []
# Query_time: 9.971999  Lock_time: 0.000025 Rows_sent: 0  Rows_examined: 0
SET timestamp=1307723790;
UPDATE smf_members
                SET
                        lastLogin = 1307723780,
                        memberIP = 'XXX',
                        memberIP2 = 'XXX',
                        totalTimeLoggedIn = 7534326
                WHERE ID_MEMBER = 2347
                LIMIT 1;

Does someone have a hint how I could speed this up ?


Here's the output of status.php
Connections per second:    0.6846
Kilobytes received per second:    4.6108
Kilobytes sent per second:    26.3944
Queries per second:    20.9057
Percentage of slow queries:    0.004
Opened vs. Open tables:
(table_cache)    3.3363 (should be <= 80)
Key buffer read hit rate:
(key_buffer_size)    0.0065 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.0773 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    44 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.0032 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.5821 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.5032 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)


Thanks s lot in advance !

[Something like that]

I got some other issue I need a little hint for.

I gow a lot of slow querys when SMF is updateing tables.

Especially when increasing the view counter

Set innodb_flush_log_at_trx_commit=2 in your MySQL configuration file. Also set innodb_flush_method=O_DIRECT if you are using Linux.

[Warlock666]

Both smf_members and smf_topics are myisam tables.
I guess I have to convert them to innodb ?

innodb_flush_method=O_DIRECT is already set and innodb_flush_log_at_trx_commit is set to "1" right now.

[Something like that]

Both smf_members and smf_topics are myisam tables.
I guess I have to convert them to innodb ?

innodb_flush_method=O_DIRECT is already set and innodb_flush_log_at_trx_commit is set to "1" right now.

Set it to 2.

And converting smf_topics is a fantastic idea. I run all InnoDB myself (except a couple tables as MEMORY).

[Warlock666]

Hi Mark,

just tried your suggestions and at the first glance it look perfect.

Thanks a whole lot !

[Warlock666]

It's once again me, sorry guys 

I still have a lot of slow queries when updating smf_log_topics and smf_members.
Both are now InnoDB and I set up the hints of Mark. Are there maybe some more tweaks ?

Thanks a lot !

[Something like that]

Paste the output of these SQL queries:

Code Select Expand


SHOW VARIABLES LIKE  "%innodb%";
SHOW STATUS LIKE  "%innodb%";


Also, how much memory does your machine have? Are you using Apache or Nginx? What tables have you not converted to InnoDB? Are you running anything other than SMF? These will affect my recommendations.

[Warlock666]

Hi Mark,

SMF is running on a vserver with 2 GB RAM ( temporary use of up to 4 GB possible ) Ubuntu 10.04 64Bit LTS
Webserve is Apache


SHOW VARIABLES LIKE  "%innodb%";
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| have_innodb                             | YES                    |
| ignore_builtin_innodb                   | OFF                    |
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 8388608                |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | OFF                    |
| innodb_flush_log_at_trx_commit          | 1                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
37 rows in set (0.00 sec)



mysql> SHOW STATUS LIKE  "%innodb%";
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Innodb_buffer_pool_pages_data     | 455        |
| Innodb_buffer_pool_pages_dirty    | 10         |
| Innodb_buffer_pool_pages_flushed  | 113690     |
| Innodb_buffer_pool_pages_free     | 0          |
| Innodb_buffer_pool_pages_misc     | 57         |
| Innodb_buffer_pool_pages_total    | 512        |
| Innodb_buffer_pool_read_ahead_rnd | 184        |
| Innodb_buffer_pool_read_ahead_seq | 605        |
| Innodb_buffer_pool_read_requests  | 52641258   |
| Innodb_buffer_pool_reads          | 23900      |
| Innodb_buffer_pool_wait_free      | 0          |
| Innodb_buffer_pool_write_requests | 5418980    |
| Innodb_data_fsyncs                | 200234     |
| Innodb_data_pending_fsyncs        | 0          |
| Innodb_data_pending_reads         | 0          |
| Innodb_data_pending_writes        | 0          |
| Innodb_data_read                  | 565006336  |
| Innodb_data_reads                 | 28755      |
| Innodb_data_writes                | 289505     |
| Innodb_data_written               | 3963269632 |
| Innodb_dblwr_pages_written        | 113690     |
| Innodb_dblwr_writes               | 11566      |
| Innodb_log_waits                  | 2          |
| Innodb_log_write_requests         | 321197     |
| Innodb_log_writes                 | 171290     |
| Innodb_os_log_fsyncs              | 177679     |
| Innodb_os_log_pending_fsyncs      | 0          |
| Innodb_os_log_pending_writes      | 0          |
| Innodb_os_log_written             | 234843136  |
| Innodb_page_size                  | 16384      |
| Innodb_pages_created              | 3904       |
| Innodb_pages_read                 | 34352      |
| Innodb_pages_written              | 113690     |
| Innodb_row_lock_current_waits     | 0          |
| Innodb_row_lock_time              | 46013      |
| Innodb_row_lock_time_avg          | 359        |
| Innodb_row_lock_time_max          | 16701      |
| Innodb_row_lock_waits             | 128        |
| Innodb_rows_deleted               | 5224       |
| Innodb_rows_inserted              | 1113868    |
| Innodb_rows_read                  | 20498927   |
| Innodb_rows_updated               | 193718     |
+-----------------------------------+------------+
42 rows in set (0.00 sec)

There is no other Website running.

Other Services

- postfix
- cyrus ( About 10 Mailboxes for the Teammembers )
- clamd
- amavisd

Just in case you'd suggest to move the E-Mailstuff to another machine, no problem, this would be possible.

Here's the first rows of a top, sorted by memory

top - 20:40:50 up 53 days,  6:18,  1 user,  load average: 0.62, 0.69, 0.61
Tasks:  80 total,   2 running,  78 sleeping,   0 stopped,   0 zombie
Cpu(s): 20.4%us,  2.7%sy,  0.0%ni, 76.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2097152k total,  2097152k used,        0k free,        0k buffers
Swap:  2097152k total,        0k used,  2097152k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25656 mysql     15   0 1384m 854m 7868 S  1.3 41.7  10:56.65 mysqld
7509 clamav    18   0  188m 142m 5552 S  0.0  6.9   6:23.78 clamd
9966 amavis    16   0  138m  43m 2544 S  0.0  2.1   0:00.49 amavisd-new
23898 amavis    15   0  136m  42m 2420 S  0.0  2.1   0:05.25 amavisd-new
3755 www-data  15   0  225m  22m 3704 S  6.3  1.1   0:00.21 apache2
4016 www-data  15   0  224m  22m 4268 S  0.0  1.1   0:00.21 apache2
1730 www-data  15   0  225m  21m 3684 S  0.3  1.1   0:00.46 apache2
1965 www-data  15   0  225m  21m 3716 S  0.0  1.1   0:00.32 apache2
3231 www-data  15   0  223m  20m 3684 S  0.0  1.0   0:00.20 apache2
3766 www-data  15   0  223m  20m 3632 S  2.3  1.0   0:00.22 apache2
1865 www-data  15   0  221m  18m 3676 S  0.0  0.9   0:00.38 apache2
1697 www-data  15   0  221m  18m 3692 S  0.0  0.9   0:00.31 apache2
3627 www-data  15   0  221m  18m 3676 S  0.0  0.9   0:00.39 apache2
5397 www-data  18   0  221m  17m 3680 S  4.7  0.9   0:00.14 apache2
5375 www-data  15   0  220m  17m 3708 S  0.0  0.9   0:00.08 apache2
3759 www-data  15   0  220m  17m 3780 S  0.0  0.9   0:00.10 apache2
3232 www-data  15   0  220m  17m 3784 S  0.0  0.9   0:00.07 apache2
5393 www-data  15   0  220m  17m 3604 S  2.7  0.9   0:00.08 apache2
3556 www-data  15   0  220m  17m 3672 S  0.0  0.9   0:00.22 apache2
3237 www-data  15   0  220m  17m 3684 S  0.0  0.9   0:00.32 apache2
3496 root      18   0  217m  17m 6932 S  0.3  0.9   3:10.06 apache2
5146 www-data  15   0  220m  17m 3616 S  0.0  0.9   0:00.04 apache2
3768 www-data  15   0  220m  17m 3632 S  0.0  0.8   0:00.21 apache2
3222 www-data  15   0  220m  17m 3624 S  0.0  0.8   0:00.11 apache2
5399 www-data  17   0  220m  17m 3628 S  2.3  0.8   0:00.07 apache2
3765 www-data  15   0  220m  16m 3376 S  0.0  0.8   0:00.04 apache2
3763 www-data  15   0  220m  16m 3380 S  0.0  0.8   0:00.03 apache2
3748 www-data  15   0  218m  13m 1856 S  0.0  0.6   0:00.00 apache2
3750 www-data  18   0  218m  12m 1568 S  0.0  0.6   0:00.03 apache2
3754 www-data  15   0  218m  12m 1580 S  0.3  0.6   0:00.01 apache2
5140 www-data  15   0  218m  12m 1568 S  0.0  0.6   0:00.00 apache2
5145 www-data  15   0  218m  12m 1580 S  0.3  0.6   0:00.01 apache2
5395 www-data  19   0  217m  12m 1520 S  0.0  0.6   0:00.00 apache2
5396 www-data  18   0  217m  12m 1520 S  0.0  0.6   0:00.00 apache2
5394 www-data  18   0  217m  12m 1520 S  0.0  0.6   0:00.00 apache2
5403 www-data  17   0  217m  12m 1516 S  0.0  0.6   0:00.00 apache2
5404 www-data  17   0  217m  12m 1516 S  0.0  0.6   0:00.00 apache2
5402 www-data  16   0  217m  12m 1516 S  0.0  0.6   0:00.00 apache2

Here are my myisam tables :

`smf_aeva_comments`
`smf_aeva_field_data`
`smf_aeva_fields`
`smf_aeva_files`
`smf_aeva_log_media`
`smf_aeva_log_ratings`
`smf_aeva_media`
`smf_aeva_perms`
`smf_aeva_quotas`
`smf_aeva_settings`
`smf_aeva_variables`
`smf_articles`
`smf_articles_attachments`
`smf_articles_cat`
`smf_articles_catperm`
`smf_articles_comment`
`smf_articles_creport`
`smf_articles_page`
`smf_articles_rating`
`smf_ban_groups`
`smf_ban_items`
`smf_board_permissions`
`smf_boards`
`smf_calendar`
`smf_calendar_holidays`
`smf_categories`
`smf_fc_bans`
`smf_fc_config`
`smf_fc_config_chats`
`smf_fc_config_instances`
`smf_fc_config_main`
`smf_fc_config_values`
`smf_fc_connections`
`smf_fc_ignors`
`smf_fc_messages`
`smf_fc_paypal_log`
`smf_fc_rooms`
`smf_gallery_cat`
`smf_gallery_comment`
`smf_gallery_pic`
`smf_gallery_report`
`smf_log_activity`
`smf_log_banned`
`smf_log_karma`
`smf_log_notify`
`smf_log_polls`
`smf_log_ratings`
`smf_log_search_messages`
`smf_log_search_subjects`
`smf_log_search_topics`
`smf_log_search_words`
`smf_membergroups`
`smf_message_icons`
`smf_messages`
`smf_moderators`
`smf_package_servers`
`smf_permissions`
`smf_personal_messages`
`smf_poll_choices`
`smf_polls`
`smf_smileys`
`smf_tags`
`smf_tags_log`
`smf_themes`


Here are Innodb :

`smf_attachments`
`smf_collapsed_categories`
`smf_log_actions`
`smf_log_boards`
`smf_log_errors`
`smf_log_mark_read`
`smf_log_search_results`
`smf_log_topics`
`smf_members`
`smf_pm_recipients`
`smf_settings`
`smf_topics`

And here's MEMORY tables :

`smf_log_floodcontrol`
`smf_log_online`
`smf_sessions`

status.php looks like this right now :

MySQL Statistics
MySQL 5.1.x
Connections per second:    0.5368
Kilobytes received per second:    13.3048
Kilobytes sent per second:    82.7866
Queries per second:    50.4146
Percentage of slow queries:    0.0029
Opened vs. Open tables:
(table_cache)    8.5057 (should be <= 80)
Key buffer read hit rate:
(key_buffer_size)    0.0392 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size)    0.1212 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size)    528.7283 (should be >= 30 )
Thread cache usage:
(thread_cache_size)    0.0058 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size)    0.537 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer)    0 (should be <= 0.001)
Query cache enabled:
(query_cache_type)    1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit)    0.2864 (should be <= 0.5)
Query cache prune rate:
(query_cache_size)    0 (should be <= 0.05)

Just in case you need some more information I'll be glad to provide you with all you need.
I just happy that you take some time to help me 

Thank you very much in advance !

Bye Dirk

[Something like that]

Set/change this in my.cnf:

innodb_buffer_pool_size = 256M
innodb_flush_log_at_trx_commit = 2

And restart mysql.

[Warlock666]

Very strange, I set innodb_flush_log_at_trx_commit = 2 in my.cnf already some days ago ( after your hint ) but mysql still used innodb_flush_log_at_trx_commit = 1.

Now I checked the line in my.cnf again and even took your line by paste and copy und restartet mysqld.
Mysqld still used innodb_flush_log_at_trx_commit = 1.
Even a stop and start didn't help.

At that points it looks more like a mysql or "human" problem.

I'll try to check this ...

I'll keep you informed how things are going !

Bye Dirk

[Warlock666]

Well, " set global innodb_flush_log_at_trx_commit = 2;" during runtime worked.

SHOW VARIABLES LIKE  "%innodb%"; show now :

| innodb_flush_log_at_trx_commit          | 2