Jérémie thank you for information. Now I don't afraid to change indexes on online table
27 2007-04-11 19:16
Re: PunBB 1.2.15 (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 ?
29 2007-04-11 14:43
Re: PunBB 1.2.15 (98 replies, posted in News)
When Hdiff will be ready ? I need to update my forums manually
30 2007-04-10 07:38
Re: Double posts and double user on online list (2 replies, posted in PunBB 1.2 troubleshooting)
Smartys it's seems to be working Thank you for help.
31 2007-04-08 13:24
Topic: Double posts and double user on online list (2 replies, posted in PunBB 1.2 troubleshooting)
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)
32 2007-04-07 10:24
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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 With this mod my topic with >4000 posts is generated in 132 seconds but without this mod* only 0.63 seconds
Have you any ideas what is wrong with this question ?
* - I delete only reputation queries at line 186
33 2007-01-30 17:48
Topic: AJAX Chat 1.2 - problem with Polish characters ? (1 replies, posted in PunBB 1.2 modifications, plugins and integrations)
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 ?
34 2007-01-30 10:48
Re: Problem with virtual memory (7 replies, posted in PunBB 1.2 troubleshooting)
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) I hope that is end of my problems
35 2007-01-30 09:28
Re: Problem with virtual memory (7 replies, posted in PunBB 1.2 troubleshooting)
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
36 2007-01-26 00:55
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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).
37 2007-01-26 00:49
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
No, I'm only restarted MySQL.
38 2007-01-26 00:40
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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)
39 2007-01-25 23:30
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
My host haven't idea too
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)
40 2007-01-25 23:09
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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)
41 2007-01-25 22:40
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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)
42 2007-01-25 22:26
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
No, only rep_user_id_idx on reputation(user_id)
43 2007-01-25 19:28
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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)
44 2007-01-25 16:07
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
Thanks for help My Handler_read_rnd_next after 4 hours is only 6 688 k
45 2007-01-25 07:13
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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 ?
46 2007-01-24 12:29
Re: Problem with virtual memory (7 replies, posted in PunBB 1.2 troubleshooting)
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.
47 2007-01-24 11:53
Topic: Problem with virtual memory (7 replies, posted in PunBB 1.2 troubleshooting)
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
Example:
I have 400 MB used of physical memory and 3GB used of virtual memory
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 ?
48 2007-01-24 11:52
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
Ok I will do it.
49 2007-01-24 11:29
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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)
50 2007-01-24 06:16
Re: Very big Handler_read_rnd_next when using PunBB ? (33 replies, posted in PunBB 1.2 troubleshooting)
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)