IRC Logs

Network: freenode Channel: #mysql

	Enter your search terms Submit search form
	Web www.irclog.org

[00:01]<zj2__>is it possible to do COUNT(*) on two tables in one query?
[00:02]<xra>rob__: Sure.
[00:02]<tjmjigtx>select (count(t1.*)+count(t2.*)) from t1, t2;
[00:02]<rrsnd>Can anybody tell me what this means? It shows up when I change the hostname of the machine:
[00:03]<xra>TodoInTX: Not quite what he wanted.
[00:03]<rrsnd>ERROR 1033 (HY000): Incorrect information in file: './wikidb/page.frm'
[00:03]<rrsnd>when I try to do, say, a select * from page;
[00:05]<zj2__>TodoInTX: when I do that, I get incorrect results
[00:05]<zj2__>Xgc: what is the correct way?
[00:05]<xra>rob__: There are several ways: SELECT v1.cnt, v2.cnt FROM (SELECT COUNT(*) cnt FROM table1) v1, (SELECT COUNT(*) cnt FROM table2) v2;
[00:06]<zj2__>Xgc: ahh, so it has to essentially be multiple queries or a subquery?
[00:06]<jd>casey, my guess is you downgraded version of mysql?
[00:06]<xra>rob__: What version of MySQL do you use?
[00:06]<rrsnd>Jy: no, I changed the hostname.
[00:06]<zj2__>Xgc: 4.1.x right now, although i'm moving this site to a VPS where I can use whatever version I want
[00:07]<jd>casey, did you try restarting mysql? after the hostname change?
[00:08]<xra>rob__: SELECT (SELECT COUNT(*) FROM table1) cnt1, (SELECT COUNT(*) FROM table2) cnt2;
[00:08]<xra>rob__: Either of those forms will work for you.
[00:08]<zj2__>Xgc: okay, i'll try that. thanks
[00:09]<zj2__>Xgc: is that optimal? i.e. can mysql perform that query quickly or will it manually dig through and count each record?
[00:10]<xra>rob__: That's how you obtain the count of rows. I don't understand what you are asking.
[00:10]<xra>rob__: MySQL does what it needs to do.
[00:10]<tjmjigtx>myisam will maintain an internal counter of the number of rows. InnoDB will need to actually count the rows each time.
[00:10]<zj2__>Xgc: i just wonder whether that's the most efficient way of doing things. I know some queries can be rewritten so mysql is 'smarter' about how it retrieves the results.
[00:11]<zj2__>TodoInTX: for speed/efficiency, should I be using myisam tables?
[00:12]<tjmjigtx>rob__: it depends.
[00:12]<xjzfnv>rob__: If you're using SELECTs mainly, MyISAM is for you.
[00:12]<zj2__>this is for forum software - users, threads, forums etc.
[00:12]<xjzfnv>InnoDB is more geared towards INSERTs due to transactions and other things...it's meant for different purposes than MyISAM.
[00:12]<xjzfnv>MyISAM then
[00:13]<zj2__>i'm not using transactions
[00:13]<xjzfnv>The majority of your queries will be SELECTs...to use InnoDB without a true need would only slow your queries and load your server.
[00:13]<zj2__>Xorlev: okay. I'm going to do some load testing later so I'll change it over
[00:13]<wusnz>What's the default database dir for mysql in linux?
[00:14]<xjzfnv>/var/lib/mysql
[00:14]<xjzfnv>If you have access to the server, just do SHOW VARIABLES LIKE 'datadir'
[00:14]<wusnz>Xorlev: thx!
[00:17]<mcz>any idea why running a check tables makes my system almost crawl to a halt? admittedly the tables cuasing problems are quiet large, but even when it takes an hour, I don't see why it shoul dhave an efefct on the other mysql threads working on other tables/databases.
[00:17]<mcz>(no, not running out of ram)
[00:17]<hzzzysjgf>disk i/o most likely
[00:18]<mcz>load average is somewhere around 1, so cpu , ah, yeah good idea
[00:18]<xjzfnv>vmstat it
[00:19]<mcz>hrm I avg 150 blks/sec, and during this crawl(weekly server amintenance script where I check all the tables and repai rif necessary), it went to 610/sec for 15 minutes or so
[00:19]<tjmjigtx>dkr: do you also run into a case where queries start piling up in LOCK state?
[00:19]<mcz>oh, hrm, that wasa ctualyl after bulk of problems, weird.
[00:20]<yscjzlvywrs>does somebody using openoffice with mysql ?
[00:20]<mcz>TodoInTX: no, that used to happen, but I got smart and used a lock to prevent inserts on the tables being checked/repaired
[00:20]<mcz>I mean, a user level lock
[00:21]<mcz>select get_lock("dbname.being.maintained"), and my code checks for the lock before insertnig, and delays the input
[00:22]<mcz>oh, my graph for paging looks flat, but I think that's because th enumber was so high mrtg discarded it, heh
[00:23]<mcz>I wonder of using myisamchk instead of "check table" would be better
[00:23]<mcz>then I could nice the proc
[00:26]<czzznzz>hi
[00:27]<czzznzz>why is the static version of Linux (non RPM package) 5.0.22 binary linked with glibc-2.2
[00:28]<czzznzz>but the dynamic 5.0.22 binary uses glibc-2.3?
[00:31]<mcz>maybe they just forgot to upgrade the static one, :)
[00:31]<czzznzz>heh :)
[00:31]<czzznzz>dkr, does the static binary run any faster?
[00:32]<mcz>re: my i/o, yeah, i was silly, my mrtg graphs are cutting off the numbers sinc eso high, sar -b shows I was up to 19000 blocks per sec at one point, 512 byte blocks means almost 10MB/sec
[00:33]<mcz>Carrera: I doubt it
[00:33]<czzznzz>dkr, is there documentatios anywhere outlining the pros and cons of each kind?
[00:33]<czzznzz>dkr, which one should I install then?
[00:33]<mcz>I'd recommend dynamic
[00:33]<czzznzz>i guess there's less hastle with the static
[00:34]<czzznzz>ah, why?
[00:34]<mcz>that way th libraries can be shar dwith other processes on your machine
[00:34]<zzzjgczdf2nff>I'm trying to set up a UDF using "CREATE FUNCTION levenshtein RETURNS INTEGER SONAME 'mysqllevenshtein.so'" but I keep getting: #1127 - Can't find function 'levenshtein' in library
[00:34]<mcz>can be shared
[00:34]<mcz>so you us eless ram total
[00:34]<zzzjgczdf2nff>Does that mean that the .so is messed up? Or that it's looking for the function like it was already installed?
[00:35]<czzznzz>dkr, and there's no performance loss?
[00:35]<mcz>is 10MB/sec a lot, I haven't looekd at hardware in ages, I have hardware raid 0+1 for this mysql server
[00:36]<mcz>Carrera: a few extra millisecond to dynamically load the libraries perhaps, no noticeable difference, and none when running
[00:36]<czzznzz>dkr, great, thanks
[00:38]<tjmjigtx>Hehe... Hi AaronCampbell
[00:38]<yzrvyc>help! I can't find the configuration file thats telling mysql to connect to this socket, i need to change it (or can i create a symbolic link?)? ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[00:39]<mxuv>hello all
[00:39]<mxuv>I am having a problem with one of my mysql instances running on debian stable
[00:39]<mxuv>I am using the dotdeb repository and have mysql 5 installed
[00:39]<tjmjigtx>AaronCampbell: your comment startled me for a sec.. I though I had inadvertently pasted the response from your ticket in here... (TodoInTX en Rackspace)
[00:40]<mcz>iratik: you can change it in your connect statement
[00:40]<drrrj>how to get an intersection of a subset of a table and another subset of the same table in v3.23?
[00:40]<mxuv>the problem is that after running a certain script, I am not able to run insert statements; after issuing the insert, I use the mysqladmin processlist to see that its still running
[00:40]<mxuv>nothing else is running with it
[00:41]<mxuv>the other processes are sleeping
[00:41]<mxuv>how can I find out what is holding up the Insert?
[00:42]<zzzjgczdf2nff>TodoInTX: got it :)
[00:42]<mxuv>I cannot even kill that id with mysqladmin kill, does that make sense?
[00:44]<drvvd2jd>I'm getting this error for my rails app -> ActiveRecord::StatementInvalid (Mysql::Error: Can't create/write to file '/var/tmp/#sql_1fd_0.MYI
[00:44]<tjmjigtx>dhuv: REPAIR and ALTER commands do not respond to 'KILL'
[00:45]<mxuv>TodoInTX: this is an INSERT command
[00:45]<tjmjigtx>the others should kill off.
[00:45]<mxuv>I dont even want to kill it, I want to know why its taking so long
[00:45]<mxuv>would turning on the extra logging help?
[00:46]<mxuv>I use that logging to see which queries are run but dont know if it will show you why something is taking a long time
[00:47]<tjmjigtx>log-slow-queries
[00:47]<tjmjigtx>long_query_time=N
[00:47]<mxuv>will this have info on why its slow, or just log all slow queries

Back to #mysql
Or go to some related logs:
#debian
#suse
#gentoo
#gentoo
#css