large table issue

--_5bb025a9-2a51-473a-a1b2-3a0a54fe6959_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit


Hi all,

do you guys know how to deal with the large tables?

here's my problem:

I have two web servers( running Nginx ) , two DB servers( running MySQL 5.1.35 ) and a server for load balancing.

What I'm maintaining is a game data tracking system. There's a game_log table which will record all detail info from many games.

here's the structure:

`game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`game_id` int(10) unsigned NOT NULL,
`event_id` int(10) unsigned NOT NULL,
`player_id` int(10) unsigned NOT NULL,
`session_id` varchar(128) NOT NULL COMMENT 'flash session id',
`score` int(10) unsigned DEFAULT NULL,
`handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handle 2:been handle',
`game_end` bigint(20) DEFAULT NULL,
`game_start` bigint(20) unsigned NOT NULL DEFAULT '0',
`event_time` float DEFAULT '0',
PRIMARY KEY (`game_log_id`),
KEY `game_id` (`game_id`),
KEY `event_id` (`event_id`),
KEY `player_id` (`player_id`)

it currently has about 12200000 records( 2 or 3 of the other tables have around a million records for each ). now, it's very slow to query this table even I just query this single table. most of the time it failed.

do you guys know what the problem is? or how to make it more efficient and faster?

thanks in advance

CK

____________________________________________________________ _____
Ò»ÕÅÕÕƬµÄ×԰סª¡ªWindows LiveÕÕƬµÄ¿É°®ÊÓƵ½éÉÜ
http://windowslivesky.spaces.live.com/blog/cns!5892B6048E249 8BD!889.entry
--_5bb025a9-2a51-473a-a1b2-3a0a54fe6959_--

Re: large table issue

--0016e64cbd6e523dde0488089726
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

Hi,
Can you please send us the query along with the explain .

Also , have u thought of partitioning the data.

regards
anandkl

2010/6/2 ²Ü¿­

>
> Hi all,
>
> do you guys know how to deal with the large tables?
>
> here's my problem:
>
> I have two web servers( running Nginx ) , two DB servers( running MySQL
> 5.1.35 ) and a server for load balancing.
>
> What I'm maintaining is a game data tracking system. There's a game_log
> table which will record all detail info from many games.
>
> here's the structure:
>
> `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `game_id` int(10) unsigned NOT NULL,
> `event_id` int(10) unsigned NOT NULL,
> `player_id` int(10) unsigned NOT NULL,
> `session_id` varchar(128) NOT NULL COMMENT 'flash session id',
> `score` int(10) unsigned DEFAULT NULL,
> `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handl=
e
> 2:been handle',
> `game_end` bigint(20) DEFAULT NULL,
> `game_start` bigint(20) unsigned NOT NULL DEFAULT '0',
> `event_time` float DEFAULT '0',
> PRIMARY KEY (`game_log_id`),
> KEY `game_id` (`game_id`),
> KEY `event_id` (`event_id`),
> KEY `player_id` (`player_id`)
>
> it currently has about 12200000 records( 2 or 3 of the other tables have
> around a million records for each ). now, it's very slow to query this ta=
ble
> even I just query this single table. most of the time it failed.
>
> do you guys know what the problem is? or how to make it more efficient a=
nd
> faster?
>
> thanks in advance
>
> CK
>
> ____________________________________________________________ _____
> Ò»ÕÅÕÕƬµÄ×԰סª¡ªWindow s LiveÕÕ=
ƬµÄ¿É°®ÊÓƵ½éÉÜ
> http://windowslivesky.spaces.live.com/blog/cns!5892B6048E249 8BD!889.entry

--0016e64cbd6e523dde0488089726--

Re: large table issue

ç»™æˆ‘çœ‹çœ‹ä½ çš„è¡ ¨çš„=E7=
´¢å¼•åŠä½ çš„慢查 询ç=9A=
=84sql语句

åœ=A8 2010-6-2，下å=885:08ï¼=8C 曹å=87=
=AF 写道ï¼=9A

>=20
> Hi all,
>=20
> do you guys know how to deal with the large tables?=20
>=20
> here's my problem:
>=20
> I have two web servers( running Nginx ) , two DB servers( running =
MySQL 5.1.35 ) and a server for load balancing.
>=20
> What I'm maintaining is a game data tracking system. There's a =
game_log table which will record all detail info from many games.
>=20
> here's the structure:
>=20
> `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `game_id` int(10) unsigned NOT NULL,
> `event_id` int(10) unsigned NOT NULL,
> `player_id` int(10) unsigned NOT NULL,
> `session_id` varchar(128) NOT NULL COMMENT 'flash session id',
> `score` int(10) unsigned DEFAULT NULL,
> `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not =
handle 2:been handle',
> `game_end` bigint(20) DEFAULT NULL,
> `game_start` bigint(20) unsigned NOT NULL DEFAULT '0',
> `event_time` float DEFAULT '0',
> PRIMARY KEY (`game_log_id`),
> KEY `game_id` (`game_id`),
> KEY `event_id` (`event_id`),
> KEY `player_id` (`player_id`)
>=20
> it currently has about 12200000 records( 2 or 3 of the other tables =
have around a million records for each ). now, it's very slow to query =
this table even I just query this single table. most of the time it =
failed.
>=20
> do you guys know what the problem is? or how to make it more =
efficient and faster?
>=20
> thanks in advance
>=20
> CK
> =20
> ____________________________________________________________ _____
> ä¸€å¼ ç…§ç‰‡çš„è‡ªç™ ½â€”=
â€=94Windows Live照片的可爱视=
频介ç»=8D
> =
http://windowslivesky.spaces.live.com/blog/cns!5892B6048E249 8BD!889.entry


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: large table issue

--_996c3fb4-61ea-43fb-8912-c527956e1274_
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit


hi huys,

here's the explain of a query on this table

EXPLAIN SELECT COUNT(game_log_id) AS sum2 FROM game_log AS g, player AS p WHERE g.player_id = p.player_id AND g.game_id=p.game_id=27 AND p.type=1 AND g.event_id = 32
-> ;
+----+-------------+-------+--------+--------------------+-- --------+---------+----------------------------+--------+--- ----------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------+-- --------+---------+----------------------------+--------+--- ----------+
| 1 | SIMPLE | g | ref | event_id,player_id | event_id | 4 | const | 237894 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | trigger_replay.g.player_id | 1 | Using where |
+----+-------------+-------+--------+--------------------+-- --------+---------+----------------------------+--------+--- ----------+
2 rows in set (0.00 sec)

and, index on 'game_id', 'event_id', 'player_id'

CK

____________________________________________________________ _____
ÏëÖªµÀÃ÷ÌìÌìÆøÈçºÎ£¿±ØÓ¦¸æËßÄ㣡
http://cn.bing.com/search?q=%E5%A4%A9%E6%B0%94%E9%A2%84%E6%8 A%A5&form=MICHJ2
--_996c3fb4-61ea-43fb-8912-c527956e1274_--

Re: large table issue

--00235444763795f72f04880a2df0
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

Hi,

MySQL Partitioning will help you a lot.

Try it.

Regards,
Krishna

2010/6/2 ²Ü¿­

>
> Hi all,
>
> do you guys know how to deal with the large tables?
>
> here's my problem:
>
> I have two web servers( running Nginx ) , two DB servers( running MySQL
> 5.1.35 ) and a server for load balancing.
>
> What I'm maintaining is a game data tracking system. There's a game_log
> table which will record all detail info from many games.
>
> here's the structure:
>
> `game_log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `game_id` int(10) unsigned NOT NULL,
> `event_id` int(10) unsigned NOT NULL,
> `player_id` int(10) unsigned NOT NULL,
> `session_id` varchar(128) NOT NULL COMMENT 'flash session id',
> `score` int(10) unsigned DEFAULT NULL,
> `handle_statu` int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1:not handl=
e
> 2:been handle',
> `game_end` bigint(20) DEFAULT NULL,
> `game_start` bigint(20) unsigned NOT NULL DEFAULT '0',
> `event_time` float DEFAULT '0',
> PRIMARY KEY (`game_log_id`),
> KEY `game_id` (`game_id`),
> KEY `event_id` (`event_id`),
> KEY `player_id` (`player_id`)
>
> it currently has about 12200000 records( 2 or 3 of the other tables have
> around a million records for each ). now, it's very slow to query this ta=
ble
> even I just query this single table. most of the time it failed.
>
> do you guys know what the problem is? or how to make it more efficient a=
nd
> faster?
>
> thanks in advance
>
> CK
>
> ____________________________________________________________ _____
> Ò»ÕÅÕÕƬµÄ×԰סª¡ªWindow s LiveÕÕ=
ƬµÄ¿É°®ÊÓƵ½éÉÜ
> http://windowslivesky.spaces.live.com/blog/cns!5892B6048E249 8BD!889.entry=
try>

--00235444763795f72f04880a2df0--

RE: large table issue

Just a note from a kibitzer: if you include an EXPLAIN, why not use \G so that
it is easier to read?

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com


>-----Original Message-----
>From: ?? [mailto:txdyc@hotmail.com]
>Sent: Wednesday, June 02, 2010 6:14 AM
>To: mysql@lists.mysql.com
>Subject: RE: large table issue
>
>
>hi huys,
>
>here's the explain of a query on this table
>
>EXPLAIN SELECT COUNT(game_log_id) AS sum2 FROM game_log AS g, player AS p
>WHERE
>g.player_id = p.player_id AND g.game_id=p.game_id=27 AND p.type=1 AND
>g.event_id = 32
> -> ;
>+----+-------------+-------+--------+--------------------+- ---------+---------
>+----------------------------+--------+-------------+
>| id | select_type | table | type | possible_keys | key | key_len
>|
>ref | rows | Extra |
>+----+-------------+-------+--------+--------------------+- ---------+---------
>+----------------------------+--------+-------------+
>| 1 | SIMPLE | g | ref | event_id,player_id | event_id | 4
>|
>const | 237894 | |
>| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4
>|
>trigger_replay.g.player_id | 1 | Using where |
>+----+-------------+-------+--------+--------------------+- ---------+---------
>+----------------------------+--------+-------------+
>2 rows in set (0.00 sec)
>
>and, index on 'game_id', 'event_id', 'player_id'
>
>CK
>
>___________________________________________________________ ______
>???????????????!
>http://cn.bing.com/search?q=%E5%A4%A9%E6%B0%94%E9%A2%84%E6% 8A%A5&form=MICHJ2




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org