Summary: | mysql 4.0.20 uses just one CPU on Gentoo | ||
---|---|---|---|
Product: | Gentoo Linux | Reporter: | Mike Blazer <blazer> |
Component: | Current packages | Assignee: | Gentoo Linux MySQL bugs team <mysql-bugs> |
Status: | RESOLVED INVALID | ||
Severity: | major | CC: | jstubbs, JWSmythe |
Priority: | High | ||
Version: | unspecified | ||
Hardware: | All | ||
OS: | Linux | ||
Whiteboard: | |||
Package list: | Runtime testing required: | --- |
Description
Mike Blazer
2004-08-20 17:34:37 UTC
i don't have any idea why this is occuring. And I don't have such a fancy box to play with to reproduce it ;-). as a test comparison could get the MySQL amd64 binary package from upstream, and install it in /usr/local/mysql-binary (or somewhere else well isolated) and see if it runs over multiple cpus. Hello, guys! I greatly appreciate that you're working on the bug I reported. I just want to comment it: I guess you can reproduce it on any Dual or Quad machine. You can install mysql standard distribution for AMD64 (though I'm not sure it's AMD64 or anyAMD only bug). It could be something between Gentoo and mysql, or even mysql own bug, though in this case I guess it'd be reported before and by many users. 4.020 is not a freash build. The installation is plain aand simple - you untar it into any directory, run scripts/mysql_install_db - and it's done. After testing you can just remove the whole directory - all files are there (which I like most of with my Windoze background :) Thanks! Mike Blazer I'm telling you to do that on your quadbox and provide the results here. my dual proc boxes are all in production use presently, and too active to try this on, and I don't have any quads. My bugreport starts from the standard distribution. I installed it first and saw this cpu inactivity. And then the same happened with the Gentoo portage mysql-4.0.20. Tell me please what kind of results could help you, or if you have some tests - let me run it on my Quad box. Mike ah, that wasn't clear from your original report. in your workload, all the work is being sent from multiple threads yes? i know mysql is limited in that if all the work is coming from a single thread/connection it will not use all of the power available. you need to have multiple connections to do that. In fact I have two boxes of the same kind. One of them now runs mysql-standard and runs in production using just one cpu. The other one I used for experiments. I started this shell oneliner n=0;while [ $n -lt 1000 ];do n=`expr $n + 1`;mysql -h<host-with-mysql> -u<user> -p<pwd> -e'select * from mysql.user' > /dev/null;done from 3-4 hosts at the same time. And always only cpu0 'user' time grew, cpu1-3 were inactive, only 'idle' time grew up. Same results with the mysql binary distribution and the one installed with portages. Please, let me know if I can be of some help in the bug detection. PS. to your last question: yes, DB server that works in production serves 3 frontend servers with 25-30 apache/mod_perl threads each. And my shell oneliner - I started it from few servers at once. So, definitelly, a lot of requests come from different threads that can't share the same mysql connection, because my apache/mod_perl code don't free threads at all, each apache thread keeps it's own connection for its lifetime. This all worked with the old DB server (a Dual Intel Xeon running Slackware) giving the same load to both CPUs. And this week the only thing I changed is DB server address in my configs. Thanks! Mike Here is what I see in 'top' after '1' command (to split by CPU) top - 16:16:30 up 29 days, 19:14, 2 users, load average: 0.11, 0.05, 0.10 Tasks: 169 total, 1 running, 168 sleeping, 0 stopped, 0 zombie Cpu0 : 1.3% us, 1.3% sy, 0.0% ni, 94.4% id, 1.7% wa, 0.3% hi, 1.0% si Cpu1 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu2 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si it's the production machine that runs nothing but mysqld Mike I've just installed a brand new copy of mysql-4.0.20 standard binary distribution on my other server: Dual Athlon 1400 with Slackware and rather old kernel 2.4.25 - it's a spare server these days, so I could be sure about /proc/stat values. And then I run the same oneliner n=0;while [ $n -lt 1000 ];do n=`expr $n + 1`;mysql -h<host-with-mysql> -u<user> -p<pwd> -e'select * from mysql.user' > /dev/null;done from the other 3 machines in the same network at once. And then - 10,000 cycles. In both cases /proc/stat 'user' values for cpu-0 and 1 increases almost by equal values, so, both CPUs take more or less equal load. This kinda approves that it's something wrong between mysql and Gentoo. Or - the newer kernel - but again, in this case I guess there'd be many reports. Thanks! Mike now that you've tested it on that old Athlon it narrows it down to: kernel - 2.4 vs. 2.6 arch - x86 vs. amd64 glibc - maybe Looks like that. Please let me know if I can help you - probably run some tests, patches etc. Thanks! Mike do what you can to exclude the variables. put gentoo in the slack box's swap diskspace for a mini-install make it a 2.6 kernel and use the upstream mysql, then we can narrow it down to an arch problem OR a kernel problem. Oops, in fact I'm not an admin, I'm perl programmer. I will ask my admin to upgrade kernel on the spare server, but the other things - about swap and mini-install - I don't really understand. On slackware I have glibc 2.3.2 while on Gentoo - 2.3.4. This is what I don't understand: http://directory.fsf.org/glibc.html says 2.3.2 is stable 2.3.3 devel relese. How come Gentoo upgraded it to 2.3.4? Thanks! Mike get your admin to join the CC for this bug please. our glibc is 2.3.3 + snapshot patches from the glibc CVS, as 2.3.4 is too long in the works, and we need stuff it contains already. Contact me via private Email for login information to this server. Just to recap on what we have: The machine is a Quad Opteron 848 with 16Gb RAM, running Gentoo installed from the 2004.1 disk (if I remember right). MySQL either emerged, or the binary from mysql.com, only utilize one CPU, where it should be using all the CPU's. The same front-end program (Apache web sites) on a dual Intel P4 running Slackware, with the mysql binary from mysql.com works as expected, equally using both CPU's. root@voy94:~# cat /etc/gentoo-release Gentoo Base System version 1.4.16 root@voy94:/usr/local/mysql# bin/mysql -V bin/mysql Ver 12.22 Distrib 4.0.20, for unknown-linux (x86_64) Linux voy94.voyeurweb.com 2.6.7-gentoo-r11 #2 SMP Fri Jul 16 17:13:15 PDT x86_64 AMD Opteron(tm) Processor 848 AuthenticAMD GNU/Linux root@voy94:~# cat /proc/cpuinfo | egrep -e '(processor|MHz|name)' processor : 0 model name : AMD Opteron(tm) Processor 848 cpu MHz : 2191.090 processor : 1 model name : AMD Opteron(tm) Processor 848 cpu MHz : 2191.090 processor : 2 model name : AMD Opteron(tm) Processor 848 cpu MHz : 2191.090 processor : 3 model name : AMD Opteron(tm) Processor 848 cpu MHz : 2191.090 root@voy94:~# cat /proc/meminfo | grep Total MemTotal: 15428060 kB HighTotal: 0 kB LowTotal: 15428060 kB SwapTotal: 128512 kB VmallocTotal: 536870911 kB HugePages_Total: 0 OK, guys, let's keep writing through the bugzilla interface, there are other subscribers from the gentoo team. Pete, voy94 is ready, so you can put Robin's pubkey or create a new user for him. Robin, please note that /raid1 and /raid2 on that machine are RAID50 arrays, though you won't need it in fact. I have 2 copies of mysql on this machine. 1. 4.0.20 standard binary distribution for x86_64 installed in /host/mysql you can start it with cd /host/mysql bin/mysqld_safe --user=mysql --skip-bdb --skip-innodb & and stop bin/mysqladmin shutdown 2. 4.0.20 built with emerge - installed in standard places: /usr/include/mysql/ - .h files /usr/share/mysql/ - languages, tests, benchmarks Libraries: /usr/lib/mysql/* and /usr/lib/libmysqlclient.so.12.0.0 /usr/lib/libmysqlclient_r.so.12.0.0 Binaries: /usr/bin/mysql/* /usr/sbin/mysqld Configs: /etc/mysql/my.cnf First remove /host/mysql/bin from the $PATH - it's before /usr/bin there: PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/opt/bin:/usr/x86_64-pc-linux-gnu/gcc-bin/3.3 to start: mysqld_safe --skip-bdb --skip-innodb --datadir=/host/mysql/data & to stop: mysqladmin shutdown if you want to run my oneliners for testing from the other machines you can alow access to mysql server: GRANT ALL ON *.* TO username@'hostname or ip' IDENTIFIED BY 'your passwd'; FLUSH PRIVILEGES; and then from that machine: n=0;while [ $n -lt 1000 ];do n=`expr $n + 1`;mysql -h64.156.136.94 -uusername -ppwd -e'select * from mysql.user' > /dev/null;done this way cat /proc/stat|grep cpu[0-3] will show pure mysql timing. Just to get it all together and save some time. This is my mysql load to attempt to reproduce the problem. It must be careful of two specific things: 1. mysql query cache - flushed whenever an insert/update happens - so need to constantly insert 2. network bandwidth limitations client - have a test query that is cpu bound and returns very little data. on server: CREATE TABLE `test` ( `i` int(11) NOT NULL auto_increment, `a` float default NULL, `b` float default NULL, `c` float default NULL, `d` float default NULL, PRIMARY KEY (`i`) ); on client 1: INSERT INTO test (a,b,c,d) values (rand(),rand(),rand(),rand()); on client 2: SELECT sum(length(concat(sha1(sha1(sha1(sha1(a)))),sha1(sha1(sha1(sha1(b)))),sha1(sha1(sha1(sha1(c)))),sha1(sha1(sha1(sha1(d))))))) from test.test order by a,b,c,d; this query takes ~10 seconds to run when the test table contains ~200000 rows. from client 1, run the insert query continously from at least 6 threads (network i/o bounded here). from client 2, run the select query continously from at least 12 threads (cpu bounded here). mysql will spawn to handle each of the threads, and the actual computation work will push the load to 100% on all cpus. changes made to my.cnf, all under the mysqld section. datadir = /host/mysql/data #(so I can start mysql with the gentoo scripts) set-variable = key_buffer=16M set-variable = max_allowed_packet=4M set-variable = thread_stack=512K key_buffer=16M max_allowed_packet=4M thread_stack=512K query_cache_size = 128M sort_buffer_size = 8K myisam_sort_buffer_size = 8M all of these just allow mysql to rise to the hardware of the machine. it probably wouldn't hurt to push some of them, and some of the other more obscure ones way up as well. using another much simpler testcase, to check for throughtput instead of actual calculation work: Uptime: 91 Threads: 5 Questions: 1494978 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1 Queries per second avg: 16428.330 Robin, I just run your /root/mysql.pl from 2 different servers and I see the same result: only one cpu works closing this now, since we found it was actually working right, and your test load wasn't enough for it to show up :-). Thanks for temporary use of your spectactullarly fast machine, I did mostly did build tests for amd64 -> ANY cross-compilers in record time. at under 3-6 minutes for each one, it was great (my own machines take over 30 min for each one). |