Database loaded?
Brandon Ooi
brandon at hotornot.com
Wed Sep 14 14:48:48 PDT 2005
Hi Brad,
Here's a typical second in time for the database.
050914 14:38:38 5 Query SELECT CONNECTION_ID()
5 Query SELECT fid, lastupdate FROM
unreachable_fids
5 Query SELECT CONNECTION_ID()
5 Query SELECT d.dmid, c.classid,
c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
5 Query SELECT fid FROM file WHERE dmid='1'
AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
9 Query SELECT CONNECTION_ID()
9 Query SELECT fid, lastupdate FROM
unreachable_fids
5 Query SELECT fid FROM file WHERE dmid='1'
AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
5 Query SELECT fid FROM file WHERE dmid='1'
AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
9 Query SELECT /*!40000 SQL_CACHE */ hostid,
status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
altmask FROM host
9 Query SELECT CONNECTION_ID()
9 Query SELECT d.dmid, c.classid,
c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
9 Query SELECT fid FROM file WHERE dmid='1'
AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
9 Query SELECT fid FROM file WHERE dmid='1'
AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
9 Query SELECT fid FROM file WHERE dmid='1'
AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
7 Query SELECT CONNECTION_ID()
7 Query SELECT fid, lastupdate FROM
unreachable_fids
7 Query SELECT CONNECTION_ID()
7 Query SELECT d.dmid, c.classid,
c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
7 Query SELECT fid FROM file WHERE dmid='1'
AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
7 Query SELECT fid FROM file WHERE dmid='1'
AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
7 Query SELECT fid FROM file WHERE dmid='1'
AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
6 Query SELECT CONNECTION_ID()
6 Query SELECT classid FROM class WHERE
dmid='1' AND classname='thumb'
6 Query INSERT INTO tempfile SET fid=NULL,
dmid='1', dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_thumb.jpg',
classid='2', createtime=UNIX_TIMESTA
MP(), devids='1'
11 Query SELECT CONNECTION_ID()
11 Query SELECT classid, dmid, dkey FROM
tempfile WHERE fid='168431'
11 Query SELECT fid, dmid, dkey, length,
classid, devcount FROM file WHERE dmid='1' AND
dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_thumb.jpg'
11 Query INSERT IGNORE INTO file_on SET fid =
'168431', devid = '1'
11 Query REPLACE INTO file SET
fid='168431', dmid='1',
dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_thumb.jpg', length='1900',
classid='2', devc
ount=0
11 Query DELETE FROM tempfile WHERE fid='168431'
11 Query SELECT GET_LOCK('mgfs:fid:168431', 10)
11 Query SELECT COUNT(*) FROM file_on WHERE
fid='168431'
11 Query UPDATE file SET devcount='1' WHERE
fid='168431'
11 Query SELECT RELEASE_LOCK('mgfs:fid:168431')
12 Query SELECT CONNECTION_ID()
12 Query SELECT classid FROM class WHERE
dmid='1' AND classname='full'
12 Query INSERT INTO tempfile SET fid=NULL,
dmid='1', dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_full.jpg',
classid='1', createtime=UNIX_TIMESTAM
P(), devids='4'
13 Query SELECT CONNECTION_ID()
13 Query SELECT classid, dmid, dkey FROM
tempfile WHERE fid='168432'
13 Query SELECT fid, dmid, dkey, length,
classid, devcount FROM file WHERE dmid='1' AND
dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_full.jpg'
13 Query INSERT IGNORE INTO file_on SET fid =
'168432', devid = '4'
13 Query REPLACE INTO file SET
fid='168432', dmid='1',
dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_full.jpg', length='34513',
classid='1', devc
ount=0
13 Query DELETE FROM tempfile WHERE fid='168432'
13 Query SELECT GET_LOCK('mgfs:fid:168432', 10)
13 Query SELECT COUNT(*) FROM file_on WHERE
fid='168432'
13 Query UPDATE file SET devcount='1' WHERE
fid='168432'
13 Query SELECT RELEASE_LOCK('mgfs:fid:168432')
050914 14:38:39 14 Query SELECT CONNECTION_ID()
14 Query SELECT classid FROM class WHERE
dmid='1' AND classname='thumb'
14 Query INSERT INTO tempfile SET fid=NULL,
dmid='1', dkey='197721e75db8a3304439bf8fccd0c8510_thumb.jpg',
classid='2', createtime=UNIX_TIMESTA
MP(), devids='1'
15 Query SELECT CONNECTION_ID()
15 Query SELECT classid, dmid, dkey FROM
tempfile WHERE fid='168433'
15 Query SELECT fid, dmid, dkey, length,
classid, devcount FROM file WHERE dmid='1' AND
dkey='197721e75db8a3304439bf8fccd0c8510_thumb.jpg'
15 Query INSERT IGNORE INTO file_on SET fid =
'168433', devid = '1'
15 Query REPLACE INTO file SET
fid='168433', dmid='1',
dkey='197721e75db8a3304439bf8fccd0c8510_thumb.jpg', length='1898',
classid='2', devc
ount=0
15 Query DELETE FROM tempfile WHERE fid='168433'
15 Query SELECT GET_LOCK('mgfs:fid:168433', 10)
15 Query SELECT COUNT(*) FROM file_on WHERE
fid='168433'
15 Query UPDATE file SET devcount='1' WHERE
fid='168433'
15 Query SELECT RELEASE_LOCK('mgfs:fid:168433')
16 Query SELECT CONNECTION_ID()
16 Query SELECT classid FROM class WHERE
dmid='1' AND classname='full'
16 Query INSERT INTO tempfile SET fid=NULL,
dmid='1', dkey='197721e75db8a3304439bf8fccd0c8510_full.jpg',
classid='1', createtime=UNIX_TIMESTAM
P(), devids='4'
17 Query SELECT CONNECTION_ID()
17 Query SELECT classid, dmid, dkey FROM
tempfile WHERE fid='168434'
17 Query SELECT fid, dmid, dkey, length,
classid, devcount FROM file WHERE dmid='1' AND
dkey='197721e75db8a3304439bf8fccd0c8510_full.jpg'
17 Query INSERT IGNORE INTO file_on SET fid =
'168434', devid = '4'
17 Query REPLACE INTO file SET
fid='168434', dmid='1',
dkey='197721e75db8a3304439bf8fccd0c8510_full.jpg', length='25229',
classid='1', devc
ount=0
17 Query DELETE FROM tempfile WHERE fid='168434'
17 Query SELECT GET_LOCK('mgfs:fid:168434', 10)
17 Query SELECT COUNT(*) FROM file_on WHERE
fid='168434'
17 Query UPDATE file SET devcount='1' WHERE
fid='168434'
17 Query SELECT RELEASE_LOCK('mgfs:fid:168434')
10 Query SELECT CONNECTION_ID()
10 Query SELECT fid, lastupdate FROM
unreachable_fids
10 Query SELECT /*!40000 SQL_CACHE */ hostid,
status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
altmask FROM host
10 Query SELECT CONNECTION_ID()
10 Query SELECT d.dmid, c.classid,
c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
10 Query SELECT fid FROM file WHERE dmid='1'
AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
10 Query SELECT
GET_LOCK('mgfs:fid:168434:replicate', 1)
10 Query SELECT devid FROM file_on WHERE
fid='168434'
10 Query INSERT IGNORE INTO file_on SET
fid='168434', devid='1'
10 Query SELECT COUNT(*) FROM file_on WHERE
fid='168434'
10 Query UPDATE file SET devcount='2' WHERE
fid='168434'
10 Query SELECT
RELEASE_LOCK('mgfs:fid:168434:replicate')
2 Query SELECT CONNECTION_ID()
2 Query SELECT fid, lastupdate FROM
unreachable_fids
8 Query SELECT CONNECTION_ID()
8 Query SELECT fid, lastupdate FROM
unreachable_fids
2 Query SELECT /*!40000 SQL_CACHE */ hostid,
status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
altmask FROM host
2 Query SELECT CONNECTION_ID()
2 Query SELECT d.dmid, c.classid,
c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
2 Query SELECT fid FROM file WHERE dmid='1'
AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
8 Query SELECT CONNECTION_ID()
8 Query SELECT d.dmid, c.classid,
c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
2 Query SELECT
GET_LOCK('mgfs:fid:168432:replicate', 1)
2 Query SELECT devid FROM file_on WHERE
fid='168432'
8 Query SELECT fid FROM file WHERE dmid='1'
AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
10 Query SELECT
GET_LOCK('mgfs:fid:168432:replicate', 1)
8 Query SELECT
GET_LOCK('mgfs:fid:168432:replicate', 1)
2 Query INSERT IGNORE INTO file_on SET
fid='168432', devid='1'
2 Query SELECT COUNT(*) FROM file_on WHERE
fid='168432'
2 Query UPDATE file SET devcount='2' WHERE
fid='168432'
2 Query SELECT
RELEASE_LOCK('mgfs:fid:168432:replicate')
10 Query SELECT devid FROM file_on WHERE
fid='168432'
10 Query SELECT
RELEASE_LOCK('mgfs:fid:168432:replicate')
8 Query SELECT /*!40000 SQL_CACHE */ devid,
hostid, mb_total, mb_used, mb_asof, status FROM device WHERE status IN
('alive', 'down' )
10 Query SELECT fid FROM file WHERE dmid='1'
AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
8 Query SELECT devid FROM file_on WHERE
fid='168432'
10 Query SELECT fid FROM file WHERE dmid='1'
AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
8 Query SELECT
RELEASE_LOCK('mgfs:fid:168432:replicate')
8 Query SELECT fid FROM file WHERE dmid='1'
AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
8 Query SELECT fid FROM file WHERE dmid='1'
AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
10 Query SELECT
GET_LOCK('mgfs:fid:168433:replicate', 1)
10 Query SELECT devid FROM file_on WHERE
fid='168433'
8 Query SELECT
GET_LOCK('mgfs:fid:168431:replicate', 1)
8 Query SELECT devid FROM file_on WHERE
fid='168431'
10 Query INSERT IGNORE INTO file_on SET
fid='168433', devid='2'
10 Query SELECT COUNT(*) FROM file_on WHERE
fid='168433'
10 Query UPDATE file SET devcount='2' WHERE
fid='168433'
10 Query SELECT
RELEASE_LOCK('mgfs:fid:168433:replicate')
8 Query INSERT IGNORE INTO file_on SET
fid='168431', devid='4'
8 Query SELECT COUNT(*) FROM file_on WHERE
fid='168431'
8 Query UPDATE file SET devcount='2' WHERE
fid='168431'
2 Query SELECT fid FROM file WHERE dmid='1'
AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
8 Query SELECT
RELEASE_LOCK('mgfs:fid:168431:replicate')
2 Query SELECT fid FROM file WHERE dmid='1'
AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
20 Connect mogfs at localhost on mogfs
20 Query set autocommit=1
20 Query SELECT dmid, namespace FROM domain
20 Query SELECT classid FROM class WHERE
dmid='1' AND classname='thumb'
20 Query SELECT /*!40000 SQL_CACHE */ devid,
hostid, mb_total, mb_used, mb_asof, status FROM device WHERE status IN
('alive', 'down' )
20 Query SELECT /*!40000 SQL_CACHE */ hostid,
status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
altmask FROM host
20 Query INSERT INTO tempfile SET fid=NULL,
dmid='1', dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_thumb.jpg',
classid='2', createtime=UNIX_TIMESTA
MP(), devids='2'
21 Connect mogfs at localhost on mogfs
21 Query set autocommit=1
21 Query SELECT dmid, namespace FROM domain
21 Query SELECT /*!40000 SQL_CACHE */ devid,
hostid, mb_total, mb_used, mb_asof, status FROM device WHERE status IN
('alive', 'down' )
21 Query SELECT /*!40000 SQL_CACHE */ hostid,
status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
altmask FROM host
21 Query SELECT classid, dmid, dkey FROM
tempfile WHERE fid='168435'
21 Query SELECT fid, dmid, dkey, length,
classid, devcount FROM file WHERE dmid='1' AND
dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_thumb.jpg'
21 Query INSERT IGNORE INTO file_on SET fid =
'168435', devid = '2'
21 Query REPLACE INTO file SET
fid='168435', dmid='1',
dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_thumb.jpg', length='1785',
classid='2', devc
ount=0
21 Query DELETE FROM tempfile WHERE fid='168435'
21 Query SELECT GET_LOCK('mgfs:fid:168435', 10)
21 Query SELECT COUNT(*) FROM file_on WHERE
fid='168435'
21 Query UPDATE file SET devcount='1' WHERE
fid='168435'
21 Query SELECT RELEASE_LOCK('mgfs:fid:168435')
8 Query SELECT
GET_LOCK('mgfs:fid:168433:replicate', 1)
8 Query SELECT devid FROM file_on WHERE
fid='168433'
8 Query SELECT
RELEASE_LOCK('mgfs:fid:168433:replicate')
3 Query SELECT CONNECTION_ID()
6 Query SELECT CONNECTION_ID()
6 Query SELECT classid FROM class WHERE
dmid='1' AND classname='full'
6 Query INSERT INTO tempfile SET fid=NULL,
dmid='1', dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_full.jpg',
classid='1', createtime=UNIX_TIMESTAM
P(), devids='2'
11 Query SELECT CONNECTION_ID()
11 Query SELECT classid, dmid, dkey FROM
tempfile WHERE fid='168436'
11 Query SELECT fid, dmid, dkey, length,
classid, devcount FROM file WHERE dmid='1' AND
dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_full.jpg'
11 Query INSERT IGNORE INTO file_on SET fid =
'168436', devid = '2'
11 Query REPLACE INTO file SET
fid='168436', dmid='1',
dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_full.jpg', length='24336',
classid='1', devc
ount=0
11 Query DELETE FROM tempfile WHERE fid='168436'
11 Query SELECT GET_LOCK('mgfs:fid:168436', 10)
... (there's about 50 more lines of stuff like this for this second in
time).
It looks like a lot of bookkeeping so I'm including my mogilefsd.conf
file here.
db_dsn = DBI:mysql:mogfs:host=127.0.0.1
db_user = mogfs
db_pass = ********
conf_port = 7001
listener_jobs = 10
delete_jobs = 1
replicate_jobs = 5
#mog_root = /mnt/mogilefs
reaper_jobs = 1
debug = 3
Brad Fitzpatrick wrote:
>What are the queries?
>
>
>On Wed, 14 Sep 2005, Brandon Ooi wrote:
>
>
>
>>Hi,
>>
>>We have mogile running to a small percentage of our users. I'm running a
>>single database, 2 trackers and 3 storage nodes. Even when mogile is
>>saving only 1 file/second and serving nothing, database seems to be
>>doing a lot of work. Here is a mytop view.
>>
>>Queries Per Second [hit q to exit this mode]
>>143
>>843
>>812
>>172
>>72
>>153
>>120
>>78
>>191
>>95
>>105
>>181
>>727
>>228
>>198
>>97
>>210
>>131
>>
>>Is this normal behavior? The machine itself has 1GB of main memory.
>>Mogile is storing approximately 200,000 files. Load on the db machine
>>seem to hover around 0.34.
>>
>>Any input is appreciated,
>>
>>Brandon
>>
>>
>>
>>
>
>
>
More information about the mogilefs
mailing list