| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Comments:
<0> yup
<0> and we're at 4K blocks so ext3 should hold 2TB files
<1> index has linked blocks... unless monty has reused top bit for somehting else, it should be able to address 4bln*blocksize which is usually 1K
<1> not talking Filesystem.
<1> it's just internal addressing.
<0> well, I'll try dropping an index and see if that fixes it for now. I'll let you know what I find out
<2> arjenAU: hmm... maybe I'll dup the file tomorrow and see if I can make it larger than 16G at the filesystem level...
<1> inviso: it should segment just for practical reasons. not just this.
<1> kibab: think about the future. you want to put infrastructure in place so your dataset can grow further. not just move boundaries
<1> having this single table is not future proof. even if mysql and your fs can handle big files. that's not the point
<2> arjenAU: point taken...
<1> having multiple tables with merge, views or partioning is wise, as it makes searches faster, backups, etc.
<1> good'o.
<0> arjenAU: yep. It's actually all my fault :) Just haven't had time to do much with this project
<0> ooooo, it BUUURRNSSS!
<0> arjenAU: How do merge tables handle indexes?
<2> ok. Well I'm going home for tonight... we'll play some more tomorrow.
<1> inviso: fairly dumbly, but of course you can just search an indiv table since they'll be carved up in time generally
<1> you don't need to talk to the overall merge to find out what happened last week.
<1> partitions (5.1) do that smartly, and you only have access through the one overall table there anyway.
<3> I'm attempting to sort a fairly large dataset, and after an hour or so of crunching, mysql throws: ERROR 3 (HY000): Error writing file '/tmp/MYNvgPiA' (Errcode: 28)
<0> arjenAU: ok. We're already working on some summary/data warehousing type views on top of it too
<0> arjenAU: yeah, for sure. I've been watching that release for a while now :)
<0> !perror 28
<4> System error: 28 = No space left on device
<3> I've tried addjusting all the server variables that relate to sorting, but doesn't fix it
<3> I have 40 gigs of space
<3> If the database is 10 gigs, would you ever need more than 40 to sort it?
<5> abram: but how much in your /tmp partition?
<3> 40 gigs
<3> iMacGyver: I don't have a separate /tmp partition
<3> I adjusted myisam_max_sort_file_size to 40 gigs
<3> does mysql have some sort of internal limitation?
<1> abram: won't solve your prob, if mysql needs >40G to do the sort. it can't magically invent space
<1> abram: nop.
<3> arjenAU: I ***umed it didn't need 40G
<1> abram: depends on the issue. if you have big varchar cols in there you're wasting space during the sort.
<3> arjenAU: does it check for free disk space before it creates the file? Because there wasn't nearly enough time to write that much data
<1> abram: I don't think it knows, because it overflows from a memory sort.
<6> is there a way to insert in 2 different tables at once?
<3> arjenAU: So you're saying it wouldn't error until the partition actually fils up
<6> insert into blabla(blablabla) values(blablabla) AND blabla2(blablaother) values(blavblar) <--- would this work?
<7> thats not 2 table
<6> then how? :/
<7> if your mysql supports view you can create view and use single insertion, just throwing ideas :)
<6> 4.11.1
<7> I am afraid 4 doesnt support view
<6> :/
<6> then how am i am supposed to do it? :/
<6> i want to prevent mixing up of queries
<6> i can't insert into multiple tables by subselecting, can i?
<7> I am afraid no
<1> abram: aye
<1> abram: it just can't know beforehand. disk use is dynamic. many apps use it all the time.
<3> arjenAU: Ok, so free space wasn't the issue (mysql can't write 40g in an hour, and I kept track of the size of /tmp, and it didn't grow). I know it's not a permissions problem, because SELECT ... INTO OUTFILE '/tmp/foo' works
<1> permission != no space left on device
<1> it's a compeltely different errorcode
<1> !perror 2
<4> System error: 2 = No such file or directory
<3> arjenAU: oh
<1> !perror 11
<4> System error: 11 = Resource temporarily unavailable
<1> !perror 12
<4> System error: 12 = Cannot allocate memory
<1> !perror 10
<4> System error: 10 = No child processes
<1> duh I forget. anyway, different ;-)
<3> so, what's going on?
<3> I can't believe that it needs more than 5 times the space that the data takes to sort it
<1> max about 3x depending on y our coltypes
<1> there must've been something else going on.
<8> g2g
<1> why can't mysql write 40g in an hr? ;-)
<3> three varchar, one ID column. But they're not large
<3> arjenAU: my system isn't that fast :-/
<1> abram: for sort purposes, ***ume that the varchars are kept at their max length
<3> arjenAU: ok
<3> ok, so I have a CHAR(5), VARCHAR(64), VARCHAR(255), and a BIGINT I'm sorting on, times about 75 million rows. That's about 25 gigs
<1> varchar(255) is probably nonsense
<3> you mean I should do something like select max(length(foo)) where foo is the varchar(255)?
<3> Anyway, my point is 25 gigs < 40 gigs
<1> select foo from tbl PROCEDURE ANALYSE()
<1> is also informative
<3> but I guess 25 gigs * 3 isn't
<1> quite.
<3> arjenAU: hmm that's not cool. 75 gigs?
<1> abram: didn't say it will. said it can.
<1> abram: the trick is to optimise the query. you can't blame mysql for having to do something you tell it to do ;-)
<3> arjenAU: have any tips on how to optimize the sort?
<3> I could try to cut down on the varchar(255) and (64) obviously
<1> abram: don't know about your layout, dataset or query requirements. so I can only give general hints.
<3> that's fine
<9> Does anyone know a clever way to update many fields at once? I saw one example: UPDATE table (SELECT a.value AS new_value...
<1> ugly example I'd say
<1> dcvkldcvjtjke: many fields in what way
<1> abram: you may be in the realm wher eyou want to consider support (mysql.com/network/) or training (musql.com/training)
<9> arjenAU: I don't want to have to do this: UPDATE table SET a = ?, b = ?,.... WHERE id = ?
<1> abram: doesn't cost that much, and should be well worth it
<3> heh.
<9> arjenAU: Actually now that I think about it, that really is the only way to do it
<3> I think I'm more in the realm here I want to consider fooling around until I get this to work ;). First thing I'm going to do is carve these varchars down. But I have to figure out how much I can exactly
<1> abram: it's your time to spend or waste.
<1> abram: as long as you know that with mysql, there are few hard limits. RDBMS just tend to run into practicalities on the OS level and such
<3> arjenAU: Good point
<10> Is there an option to make a mysqldump of 4.1 compatible to be imported into a 4.0 mysql database?
<11> levander: mysqldump --compatible
<11> levander: mysqldump --help
<10> Therion: thanks, I was looking in the man page, I'll check the --help message and try --compatible
<11> the man pages are ancient and useless
<12> can I ask noobish syntax questions here?
<13> you just did
<12> no thats a stupid noob question, I wanna ask syntax ones :)
<13> go ahead
<0> Can.. An interesting word. I guess we'll see if you can or not
<13> Docteh you may
<12> wondering about SELECT * FROM table WHERE DATE >= 200402 AND title NOT ''
<0> What is 200402? 2004, second quarter?
<12> inviso, I guess I asked for that since I'm talking about syntax
<12> inviso, well its some number, its actually 20060202, I'm having trouble filtering out lines where the title field has nothing in it
<0> Docteh: as in '' or NULL?
<14> hey, how to insert blob text?
<12> lemme check, pretty sure its ''
<0> Docteh: if you're inserting NULL for "empty" rows, use title IS NOT NULL. If it's '', use title != ''
<12> thanks
<14> hello
<9> Is there a way to check the syntax of a file without the statements being executed?
<14> how to insert text in blob field.. insert into tbl (blobfield) values ('blobcontent') not works?
<14> ehm...
<0> exp[a]: why would you put text into a blob?
<14> well it's actually described as text/mediumtext
<14> it's just phpmyadmin saya bloc
<14> b
<0> what's the full error?
<10> When I'm allowing access from a host in the GRANT tables, can I add a wildcard for a specific domain? E.g., *.yahoo.com?
<14> well, that's what mysql_error() writes check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext, sectionid, catid) VALUES (452, 'Lotus Challenge City R
<0> levander: %
<0> !m levander adding users
<4> levander: (Adding New User Accounts to MySQL) : http://dev.mysql.com/doc/mysql/en/Adding_users.html
<0> !m exp[a] keywords
<4> exp[a]: (Treatment of Reserved Words in MySQL) : http://dev.mysql.com/doc/mysql/en/Reserved_words.html
<0> later guys
<14> k
<10> thanks invos, i'm looking at those pages
<15> greetings
<15> is there a grant priv that will create a user with everything set to Y in mysql.user ?
<15> basically a short cut to make an additional super user
<16> hi there
<15> anyone?
<17> what is an easy way to count all records in a database?
Return to
#mysql
or
Go to some related
logs:
debian amavis-new nod32
phpMyAdmin #2002 - The server is not responding (or the local MySQL server's soc
i868 gcc
serial mouse + gpm + udev
#mysql
ssh disable encryption
compile cedega ubuntu
galeon symbol lookup error
linksysmon portage
mysqldump fast
|
|