26

(98 replies, posted in News)

Jérémie thank you for information. Now I don't afraid to change indexes on online table smile

27

(98 replies, posted in News)

I have question about index in table online. Now phpmyadmin says:

UNIQUE and INDEX keys should not both be set for column `user_id`

Is it correct ?

28

(98 replies, posted in News)

Thank you for update in Hdiff smile

29

(98 replies, posted in News)

When Hdiff will be ready ? I need to update my forums manually roll

Smartys it's seems to be working smile Thank you for help.

Hi,

From few dayes I have problem with double posts and double user on online list. Every time double logged user is different.

Did you hear about this problem in 1.2.14 ?

Ex. for user test

mysql> select * from online;
+---------+----------------+------------+------+
| user_id | ident          | logged     | idle |
+---------+----------------+------------+------+
|    2388 | mroczu         | 1176038672 |    0 |
|       1 | 195.254.156.74 | 1176038449 |    0 |
|       2 | tomek          | 1176038713 |    0 |
|      57 | test           | 1176038714 |    0 |
|    1430 | Maciek-Chopin  | 1176038713 |    0 |
|      57 | test           | 1176038714 |    0 |
|    2207 | Ramiro         | 1176038687 |    0 |
|       1 | 66.194.6.96    | 1176038630 |    0 |
|       1 | 217.76.116.77  | 1176038597 |    0 |
|    2145 | Virtuoz        | 1176038436 |    0 |
|       1 | 74.6.71.50     | 1176038414 |    0 |
|       1 | 66.249.72.235  | 1176038700 |    0 |
|       1 | 74.6.70.51     | 1176038614 |    0 |
|       1 | 128.194.135.94 | 1176038667 |    0 |
|       1 | 83.14.166.90   | 1176038510 |    0 |
+---------+----------------+------------+------+
15 rows in set (0.03 sec)

Smartys I find out that sql question in viewtopic.php

SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, ..... LEFT JOIN '.$db->prefix.'reputation as r ON (r.user_id=u.id) ..... GROUP BY p.id

is very slow hmm With this mod my topic with >4000 posts is generated in 132 seconds but without this mod* only 0.63 seconds roll

Have you any ideas what is wrong with this question ?

* - I delete only reputation queries at line 186

Hi,

What should I do to fix incorrectly displaying characters in this mod ?

When I write Polish characters

? ó ? ? ? ? ? ? ?

I see

} ó i ¿ ¦ à Þ k ¨

Colud you help me with that problem ?

I have changed all online tables to MyISAM and now MySQL is using only 563 MB of virtual memory (2 GB before at the same time) smile I hope that is end of my problems wink

How about table online which is using memory (HEAP table) ? Mayby I should change to MyISAM because I have over 9000 forums with memory table online ? Maybe this is why my virtual memory is so high roll

heh, where is my server status from phpmyadmin. Memember that MySQL server has been only running for 1 hour and 48 minutes.

I'm wondering about missing index (Select_full_join = 1,066) and Opened_tables (229 k / 2,048 k).

No, I'm only restarted MySQL.

With reputation mod:

mysql> explain SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) LEFT JOIN reputation as r ON (r.user_id=u.id) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys                      | key                | key_len | ref                    | rows | Extra                           |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | u     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |   26 | Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ref  | online_user_id_idx                 | online_user_id_idx | 4       | pun_klasa3h.u.id       |    2 |                                 |
|  1 | SIMPLE      | g     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |    3 | Using where                     |
|  1 | SIMPLE      | p     | ref  | posts_topic_id_idx,posts_multi_idx | posts_multi_idx    | 8       | pun_klasa3h.u.id,const |    6 |                                 |
|  1 | SIMPLE      | r     | ref  | rep_user_id_idx                    | rep_user_id_idx    | 4       | pun_klasa3h.u.id       |   43 |                                 |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
5 rows in set (0.00 sec)

Without reputation mod:

mysql> explain SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys                      | key                | key_len | ref                    | rows | Extra                           |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | u     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |   26 | Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ref  | online_user_id_idx                 | online_user_id_idx | 4       | pun_klasa3h.u.id       |    2 |                                 |
|  1 | SIMPLE      | g     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |    3 | Using where                     |
|  1 | SIMPLE      | p     | ref  | posts_topic_id_idx,posts_multi_idx | posts_multi_idx    | 8       | pun_klasa3h.u.id,const |    6 |                                 |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
4 rows in set (0.00 sec)

My host haven't idea too sad

mysql> analyze table users;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| pun_klasa3h.users | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.04 sec)
mysql> analyze table posts;
+-------------------+---------+----------+----------+
| Table             | Op      | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| pun_klasa3h.posts | analyze | status   | OK       |
+-------------------+---------+----------+----------+
1 row in set (0.74 sec)
mysql> analyze table groups;
+--------------------+---------+----------+-----------------------------+
| Table              | Op      | Msg_type | Msg_text                    |
+--------------------+---------+----------+-----------------------------+
| pun_klasa3h.groups | analyze | status   | Table is already up to date |
+--------------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

This is query without reputation mod:

mysql> explain SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;
+----+-------------+-------+--------+------------------------------------+-----------------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys                      | key             | key_len | ref                    | rows | Extra                           |
+----+-------------+-------+--------+------------------------------------+-----------------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | o     | system | online_user_id_idx                 | NULL            | NULL    | NULL                   |    0 | const row not found             |
|  1 | SIMPLE      | u     | ALL    | PRIMARY                            | NULL            | NULL    | NULL                   |   26 | Using temporary; Using filesort |
|  1 | SIMPLE      | g     | ALL    | PRIMARY                            | NULL            | NULL    | NULL                   |    3 | Using where                     |
|  1 | SIMPLE      | p     | ref    | posts_topic_id_idx,posts_multi_idx | posts_multi_idx | 8       | pun_klasa3h.u.id,const |    6 |                                 |
+----+-------------+-------+--------+------------------------------------+-----------------+---------+------------------------+------+---------------------------------+
4 rows in set (0.47 sec)
mysql> show indexes from posts;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| posts |          0 | PRIMARY            |            1 | id          | A         |        4555 |     NULL | NULL   |      | BTREE      |         |
| posts |          1 | posts_topic_id_idx |            1 | topic_id    | A         |         198 |     NULL | NULL   |      | BTREE      |         |
| posts |          1 | posts_multi_idx    |            1 | poster_id   | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| posts |          1 | posts_multi_idx    |            2 | topic_id    | A         |         759 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.11 sec)
mysql> show indexes from users;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          0 | PRIMARY              |            1 | id          | A         |          26 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | users_registered_idx |            1 | registered  | A         |          26 |     NULL | NULL   |      | BTREE      |         |
| users |          1 | users_username_idx   |            1 | username    | A         |          26 |        8 | NULL   |      | BTREE      |         |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.01 sec)
mysql> show indexes from groups;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| groups |          0 | PRIMARY  |            1 | g_id        | A         |           4 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.06 sec)

No, only rep_user_id_idx on reputation(user_id)

I have another slow queries with the same question:

# Query_time: 19  Lock_time: 0  Rows_sent: 15  Rows_examined: 158486
use pun_klasa3h;
SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) LEFT JOIN reputation as r ON (r.user_id=u.id) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;
mysql> explain SELECT u.email, u.title, u.url, u.yahoo, u.location, u.use_avatar, u.aim, u.msn, u.signature, u.email_setting, u.num_posts, u.registered, u.admin_note, SUM(r.rep_plus) AS count_rep_plus, SUM(r.rep_minus) AS count_rep_minus, u.reputation_enable, p.id, p.poster AS username, p.poster_id, p.poster_ip, p.poster_email, p.message, p.hide_smilies, p.posted, p.edited, p.edited_by, g.g_id, g.g_user_title, o.user_id AS is_online FROM posts AS p INNER JOIN users AS u ON u.id=p.poster_id INNER JOIN groups AS g ON g.g_id=u.group_id LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0) LEFT JOIN reputation as r ON (r.user_id=u.id) WHERE p.topic_id=101 GROUP BY p.id ORDER BY p.id LIMIT 2100,25;
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys                      | key                | key_len | ref                    | rows | Extra                           |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
|  1 | SIMPLE      | u     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |   26 | Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ref  | online_user_id_idx                 | online_user_id_idx | 4       | pun_klasa3h.u.id       |    2 |                                 |
|  1 | SIMPLE      | g     | ALL  | PRIMARY                            | NULL               | NULL    | NULL                   |    3 | Using where                     |
|  1 | SIMPLE      | p     | ref  | posts_topic_id_idx,posts_multi_idx | posts_multi_idx    | 8       | pun_klasa3h.u.id,const |    6 |                                 |
|  1 | SIMPLE      | r     | ref  | rep_user_id_idx                    | rep_user_id_idx    | 4       | pun_klasa3h.u.id       |   43 |                                 |
+----+-------------+-------+------+------------------------------------+--------------------+---------+------------------------+------+---------------------------------+
5 rows in set (0.00 sec)
mysql> describe reputation;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_id      | int(10) unsigned    | NO   | MUL | 0       |                |
| from_user_id | int(10) unsigned    | NO   |     | 0       |                |
| time         | int(10) unsigned    | NO   |     | 0       |                |
| post_id      | int(10) unsigned    | NO   | MUL | 0       |                |
| reason       | text                | NO   |     |         |                |
| rep_plus     | tinyint(1) unsigned | NO   |     | 0       |                |
| rep_minus    | tinyint(1) unsigned | NO   |     | 0       |                |
| topics_id    | int(10) unsigned    | NO   | MUL | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
mysql> show indexes from reputation;
+------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
| reputation |          0 | PRIMARY               |            1 | id           | A         |         735 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_post_id_idx       |            1 | post_id      | A         |         735 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_multi_user_id_idx |            1 | topics_id    | A         |          81 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_multi_user_id_idx |            2 | from_user_id | A         |         183 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_user_id_idx       |            1 | user_id      | A         |          17 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Thanks for help smile My Handler_read_rnd_next after 4 hours is only 6 688 k big_smile

I think about next index in table online.

LEFT JOIN online AS o ON (o.user_id=u.id AND o.user_id!=1 AND o.idle=0)

Don't you think it should be index on idle in online ?

my.cnf

#
# * Fine Tuning
#
key_buffer        = 128M
max_allowed_packet    = 64M
table_cache        = 2048
sort_buffer_size    = 16M
join_buffer_size    = 16M
read_buffer_size    = 512K
thread_stack        = 128K
thread_cache_size    = 8

#
# * Query Cache Configuration
#
query_cache_limit    = 1048576
query_cache_size        = 128M
query_cache_type        = 1

[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]
key_buffer        = 128M

We have tried to allocate more memory but it didn't help.

Hi,

As you know I've got multiforum server with PunBB and from few weeks I've got problem with crashing MySQL. Usually I have Got error 12 from storage engine (Errno: 1030).

My host told me that MySQL is crashing from time to time because virtual memory isn't unloading sad

Example:

I have 400 MB used of physical memory and 3GB used of virtual memory sad

It's normal ? MySQL is crashing when virtual memory reaches 3 GB.

Please remember that I have 8845 forums at 2 GB ram memory.

Could you help me ?

Ok I will do it.

Here you are

mysql> describe reputation;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_id      | int(10) unsigned    | NO   |     | 0       |                |
| from_user_id | int(10) unsigned    | NO   |     | 0       |                |
| time         | int(10) unsigned    | NO   |     | 0       |                |
| post_id      | int(10) unsigned    | NO   | MUL | 0       |                |
| reason       | text                | NO   |     |         |                |
| rep_plus     | tinyint(1) unsigned | NO   |     | 0       |                |
| rep_minus    | tinyint(1) unsigned | NO   |     | 0       |                |
| topics_id    | int(10) unsigned    | NO   | MUL | 0       |                |
+--------------+---------------------+------+-----+---------+----------------+
9 rows in set (0.19 sec)
mysql> show indexes from reputation;
+------------+------------+-----------------------+--------------+--------------                                             +-----------+-------------+----------+--------+------+------------+---------+
| Table      | Non_unique | Key_name              | Seq_in_index | Column_name                                               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+-----------------------+--------------+--------------                                             +-----------+-------------+----------+--------+------+------------+---------+
| reputation |          0 | PRIMARY               |            1 | id                                                        | A         |         717 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_post_id_idx       |            1 | post_id                                                   | A         |         717 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_multi_user_id_idx |            1 | topics_id                                                 | A         |          79 |     NULL | NULL   |      | BTREE      |         |
| reputation |          1 | rep_multi_user_id_idx |            2 | from_user_id                                              | A         |         179 |     NULL | NULL   |      | BTREE      |         |
+------------+------------+-----------------------+--------------+--------------                                             +-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.11 sec)

Here you are

+----+-------------+-------+--------+------------------------------------+--------------------+---------+-------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys                      | key                | key_len | ref                     | rows | Extra                           |
+----+-------------+-------+--------+------------------------------------+--------------------+---------+-------------------------+------+---------------------------------+
|  1 | SIMPLE      | o     | system | online_user_id_idx                 | NULL               | NULL    | NULL                    |    0 | const row not found             |
|  1 | SIMPLE      | p     | ref    | posts_topic_id_idx,posts_multi_idx | posts_topic_id_idx | 4       | const                   | 2000 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY                            | PRIMARY            | 4       | pun_klasa3h.p.poster_id |    1 |                                 |
|  1 | SIMPLE      | r     | ALL    | NULL                               | NULL               | NULL    | NULL                    |  716 |                                 |
|  1 | SIMPLE      | g     | eq_ref | PRIMARY                            | PRIMARY            | 4       | pun_klasa3h.u.group_id  |    1 |                                 |
+----+-------------+-------+--------+------------------------------------+--------------------+---------+-------------------------+------+---------------------------------+
5 rows in set (0.18 sec)