Database loaded?
Brad Fitzpatrick
brad at danga.com
Fri Sep 16 15:55:47 PDT 2005
Yeah, you can. Or you can memcached the results of popular get_paths
queries.
On Fri, 16 Sep 2005, Brandon Ooi wrote:
> yeah you're right. that's odd, there should have been only 1. perhaps
> it's more loaded than i thought.
>
> I guess i'm worried that under high load the database will quickly
> become the bottleneck. I dont' expect our database machines to do much
> more than 2000 queries per second. is there a way to reduce the load?
> perhaps configure less jobs on some of the trackers?
>
> Brandon
>
> Brad Fitzpatrick wrote:
>
> >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