| |
| |
| |
|
Page: 1 2 3 4 5 6 7
Comments:
<0> Windows has a significant percentage of MySQL installations ;)
<1> Leithal: i dont know how to find the size of a file in DOS
<1> I dont even use windows, sorry.
<0> but this is a cool script ;p
<0> I'm going to spam now.
<0> E:\MySQL\issues\csc8185>backup.bat
<0> Making Directory admin
<0> Backing up database admin to admin_20060205_2008.sql.gz
<0> Done..
<0> Checking the compressed file
<0> Compressed file OK
<0> Making Directory archive
<0> Backing up database archive to archive_20060205_2008.sql.gz
<0> Done..
<2> how well does mysql scale in regards to how big the database is? For example 10 vs 100 000 posts in a table that has at some Ints and Varchars. I'd bet that there are lot's of factors but what do you think? Are there any benchmarks i can refer to?
<0> Checking the compressed file
<0> Compressed file OK
<0> ...
<1> SELECT SUM( srcpkts ) , SUM( srcdata ) , SUM( dstpkts ) , SUM( dstdata )
<1> FROM counter
<1> WHERE ipaddr = '70.86.176.2'
<1> AND date
<1> BETWEEN "2005-00-01 00:00:00"
<1> AND "2007-00-05 00:00:01"
<1> OOOPS! Sorry Yal.
<1> *Y'all
<0> moonlite: Just fine - that's what indexes are for
<2> Leithal: i'd say that too.
<0> if you hit full scanning issues then it can slow down at times - but you can tune to bring things in to memory if that's the case, or add better indexes, etc.
<0> but it scales to billions of rows
<2> thanks. i have a lot of users at a online forum that belives that our site will magically go twice as fast if we delete half of our posts and they won't give up.
<2> what would full scanning issues be?
<0> check the value of handler_read_rnd_next comared to handler_read_rnd withing SHOW STATUS
<0> or SHOW GLOBAL STATUS
<0> handler_read_rnd_next is the number of rows read via a full scan
<0> harnder_read_rnd via indexes
<2> ah ok
<0> handler_read_rnd * ;p
<2> so basically if i search for a non-indexed value mysql will have to traverse through the whole table?
<0> it's where a query can't find an appropriate index
<2> otherwise not?
<0> yep
<2> yeah ok
<2> thanks a lot
<0> you can try to catch queries like that with the slow query log
<0> then run them through EXPLAIN
<0> and find the best indexes
<0> !man slow query log
<3> (The Slow Query Log) : http://dev.mysql.com/doc/mysql/en/Slow_query_log.html
<0> !man explain
<3> (EXPLAIN Syntax (Get Information About a SELECT)) : http://dev.mysql.com/doc/mysql/en/EXPLAIN.html
<0> !man indexes
<3> (Column Indexes) : http://dev.mysql.com/doc/mysql/en/Indexes.html
<2> i'll save those links.
<2> thanks a lot!
<0> no problem
<4> hello.
<4> can i do this: mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet instead of specifies a table name.
<4> i have a text file with states that have create table and insert .... how do i load the entire data file ?
<4> i've been searching for so long online....
<5> is it possible to grant privileges to multiple tables with one line? I keep running into syntax errors using GRANT list, of, privileges ON db.table1, db.table2 TO 'x@x';
<5> however... when the query is fine if I use 1 table at a time.. uie. GRANT list, of, privileges ON db.table1 TO 'x@x';
<6> anyone for xfrisk?
<5> however... when the query is fine if I use 1 table at a time.. uie. GRANT list, of, privileges ON db.table1 TO 'x@x';
<5> lol
<5> I mean't :
<5> is it possible to grant privileges to multiple tables with one line? I keep running into syntax errors using GRANT list, of, privileges ON db.table1, db.table2 TO 'x@x';
<6> http://tuxick.net/xfrisk/
<7> This script requires mysql user,p***, port and sock. Anyone know what I should fill in for 'sock' ? I use linux and cpanel control panel
<7> getting 'database access error' when I've only filled in user/p***/port
<8> I wish I didn't keep having to resort to dumping tables into flat files, and writing perl hacks to process them to save time.
<9> Can I tell mysql to retreive on random base?
<9> basicly shuffle the records...
<8> something like "insert into dups (select o.*, l.foo, l.bar from orig o join tmp_list l on l.id = o.id); delete from o using orig o join dups d on o.id = d.id" would have taken about 70 hours, and a perl script that does the same thing on flat files is going to complete in a couple hours
<9> bazman:your properly have like 10-50 mil records I would say?
<8> yeah, tmp_list had 22 million, and orig has 75
<8> tmp_list is basically a list of IDs generated from another script, and I'm trying to move all the records with those IDs from one table to another
<9> bazman: that is tricky for anydatabase.. you are removing all duplicates?
<8> ries: and yes, it happens that the IDs in the tmp_list are duplicate records of various sorts, but that's irrelavent
<9> can you remove the index of the table you move to before you do the move operation, and after it rebuild the index?
<8> That would help, but just the select in the first command without the insert I estimated would have taken about 60 hours. 70 hours was actually a pretty generous underestimate
<8> ries: I'm guessing it was having to do a disk seek for every record it was selecting, so it was only pulling about a few hundred per second
<10> hi just stuck with err 2019 "Can't initialize character set utf8 " any ideas? (I'm running linux)
<9> bazman: I can't give you a good solution actually.. then try to rearrange the DB and code in such a way that you don't have to do it.... in mysql5 you might beable to setup a trigger to do the work directly... if possible ofcourse.
<8> my final solution was to load all the IDs in "tmp_list" into a giant perl array, and scan through a dump of the orig table and print each line into one output file or another based on weather the ID was in the giant array
<8> problem is I couldn't fit 22 million perl elements in my memory, so I'm going to have to do about 10 p***es
<8> ries: I wish I could use a trigger. Unfortunately all the data is already here, and I'm just processing it all at once
<8> ries: or maybe I don't understand how I would use triggers?
<9> bazman: do you have to do this often?
<8> bazman: it is a one time thing
<8> er, ries:
<9> bazman: well... then do it one time over the weekend and that's it.... otherwise you spend more timing trying to optimize then the routine does it's work...
<10> hi just stuck with err 2019 "Can't initialize character set utf8 " any ideas? (I'm running linux)
<9> sdr_: we know... I don't know the anser....
<11> What is wrong with this query?
<11> select * from linker where p1 is not null and where p2 is null order by p1 asc;
<8> ries: well, I'm trying to get this finished on a deadline, and 3 days was a little too long to wait. It only took about half an hour to write the perl
<12> htns: select * from linker where p1!=null and p2==null order by p1 asc;
<11> FreeOne3000: Ah gotcha, didn't need that extra "where"
<11> Stupid me
<10> ries, any way to set charset to something present in /usr/share/mysql/charsets?
<8> ries: do you think I was correct on disk seeks being the bottle neck in that first join?
<13> would an upgrade in ram so you can raise your cache and buffer sizes cause any significant performace upside?
<14> bazman: Sounds like an index problem.
<8> Xgc: Hmm, it was a simple join on two indexed bigints...
<9> bazman: you can turn of some flush to speed up the processing, if you see a lot of HD activity then it might be true indeed.... the RDBM needs to build tables for the operation properly that won't fit in some part of the memory...
<8> ries: I increased the key_buffer_size to larger than the index it was using
<8> ries: good suggestion on the flush. hadn't thoguht about that
<9> well.. I am not a mysql guru.... I always work with postgresql except for this occasion that I am here
<14> bazman: How much ram do you have in this system total?
<10> found it!!
<8> Xgc: 2G
<8> Xgc: the index is 1.1G
<14> bazman: Is that the total of both indexes?
<8> Xgc: explain said it wasn't using an index for the smaller table. It had type all
<8> Xgc: but the smaller index is only around 300M
<14> Right, but by the sound of it, it loaded the entire 1st table and not just the index to perform the JOIN.
<8> Xgc: first table being the smaller one?
<14> Just a guess.
<8> Xgc: The smaller table totals around 400M
<8> so both the 1.1G index and the 400M table should be able to fit in the ram
<14> That approaches the total ram size.
<8> but it shouldn't need to put the non-indexed table into the ram
<8> not all at once at least
<8> would it help to remove the index on both tables?
<14> That's probably not going to help.
<8> Xgc: wouldn't think so... :P
<8> But if it loaded the entire column into memory it might go faster than whatever it does with indexes
<8> Doubtful, admittedly
Return to
#mysql
or
Go to some related
logs:
flibberdy
#php
eclipse brainf*ck plugin
#perl
#mysql
oracle logships
postfix 2.3 mysql5 debian
#centos
xf86OpenConsole: setpgid failed: Operation not permitted
#osdev
|
|