Database loaded?
Brad Fitzpatrick
brad at danga.com
Fri Sep 16 14:21:52 PDT 2005
I see multiple files being created there. Sure you were only doing 1 per
second?
In any case, the traffic doesn't surprise me that much.
- Brad
On Wed, 14 Sep 2005, Brandon Ooi wrote:
> 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