How to View MySQL Temp Files and Temp Tables in Linux
Below is my MySQL Server's status and configuration. But I can not see anything under /tmpfs/, it is showing empty to me. here is the result of "ls -al" drwxrwxrwx 2 mysql mysql60 Apr 7 17:43 tmpfs Could anyone tell me how to check the status of temp files and temp tables on disk? Thanks. mysql> SHOW global STATUS LIKE 'created_tmp%'; +-+---+ | Variable_name | Value | +-+---+ | Created_tmp_disk_tables | 4682 | | Created_tmp_files | 291 | | Created_tmp_tables | 10997 | +-+---+ mysql> SHOW variables LIKE '%tmp%'; +---+--+ | Variable_name | Value| +---+--+ | bdb_tmpdir| /tmpfs/ | | max_tmp_tables| 32 | | slave_load_tmpdir | /tmpfs/ | | tmp_table_size| 67108864 | | tmpdir| /tmpfs/ | +---+--+
More CPU or More RAM?
I have a 1.5G database which feeds a CMS web application (Drupal). Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. IO and CPU are high. So I am planning to upgrade it to a dedicated serer. Here are two choice of my server: 1. Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM 2. Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM. I know the best way to do this is to benchmark the two servers, but I can't do that, can only pick one. Could anyone of you tell me which one is better for higher MySQL performance, based on your experience? Thanks.
Re: More CPU or More RAM?
Thanks Johan, Unfortunately I run into all 4 issues you have mentioned. And the views is my huge part of my site. I got about 50k-60k page view per day, about 40k nodes. It is really a pain to make drupal run fast. I feel drupal query the db tooo much. I understand I can get some performance boost by writing my own code, find replacement etc, but that costs me too much if I can use hardware to solve the same problem. and Yes I know I will run to same problem again when the site grows to some point. I will definitely try your advice, such as memcache. Thanks! If anyone else has more advice on the 2 server options, please let me know. On Wed, Apr 21, 2010 at 9:43 AM, Johan De Meersman wrote: > Switch CMSes, you'll be better off. I have the pain of running Drupal, too. > > Your DB host is probably good enough, unless you're doing insane amounts of > page views. What you need is Drupal optimisations. Here's just a few: > > - drupal keeps both it's sessions and cache in the DB. Change to memcache > - the views module is horrible. Get rid of it and write your own queries > - for pete's sake don't turn on the watchdog module, especially on debug. > That, too goes in your db > - avoid taxonomy - it does evil hiearchical queries > > et cetera ad nauseam :-) > > > > On Wed, Apr 21, 2010 at 4:31 PM, [email protected] wrote: > >> I have a 1.5G database which feeds a CMS web application (Drupal). >> >> Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. >> IO >> and CPU are high. So I am planning to upgrade it to a dedicated serer. >> >> Here are two choice of my server: >> >> 1. Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM >> 2. Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM. >> >> I know the best way to do this is to benchmark the two servers, but I >> can't >> do that, can only pick one. Could anyone of you tell me which one is >> better >> for higher MySQL performance, based on your experience? >> >> Thanks. >> > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel >
Re: More CPU or More RAM?
It seems all Johans are interested on this topic. :D Thank Johan. My web server (apache 2) is on the same server. And it looks good to me. I have no custom module. The most often used modules are CCK, Views and WebForm. OK my server does send out some mails every day, about 10K. I tried to enable slow query logs before, but the slow queries are not limited to some particular queries, it is pretty ramdom, sometimes a single "select url from url_alias where nid = xxx" took long time. My current host uses XEN VPS. It is not bad, but I feel the DISKIO is my bottle neck. this is a common top output from my server top - 11:01:09 up 18 days, 16 min, 5 users, load average: 2.06, 1.97, 2.07 Tasks: 137 total, 1 running, 135 sleeping, 0 stopped, 1 zombie Cpu(s): 8.8%us, 4.2%sy, 0.0%ni, 86.9%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 1452792k total, 1175924k used, 276868k free,67220k buffers Swap: 524280k total, 6652k used, 517628k free, 738012k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 18347 mysql 20 0 412m 145m 4556 S 47 10.3 30:19.96 mysqld 22141 apache20 0 162m 28m 18m S0 2.0 0:00.84 httpd 22067 apache20 0 162m 27m 17m S0 1.9 0:01.15 httpd 22110 apache20 0 162m 26m 17m S0 1.9 0:00.87 httpd and this is from iostat Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn xvda623.50 0.00 6.12 0 12 xvdb 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 19.480.00 16.93 20.320.08 43.18 Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn xvda590.50 0.00 7.95 0 15 xvdb 0.00 0.00 0.00 0 0 avg-cpu: %user %nice %system %iowait %steal %idle 25.220.00 27.61 29.430.19 17.55 Device:tpsMB_read/sMB_wrtn/sMB_readMB_wrtn xvda468.00 0.0013.11 0 26 xvdb 0.00 0.00 0.00 0 0 On Wed, Apr 21, 2010 at 9:47 AM, Johan Gant wrote: > Hi, > > Have you looked at tuning Drupal first? What processes are slowing your > server down and are there any other applications sharing the machine that > might be contributing to the problem? Assuming you haven't got any wacky > contrib modules have you considered improving your application caching, or > if you have any custom modules - look at whether you can add an appropriate > index to any of the tables. Of course, if you want to throw more hardware at > the problem it might help in the short run but it might be masking the > original problem. > > Regards, > > Johan > > -Original Message- > From: [email protected] [mailto:[email protected]] > Sent: 21 April 2010 15:31 > To: [email protected] > Subject: More CPU or More RAM? > > I have a 1.5G database which feeds a CMS web application (Drupal). > > Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. IO > and CPU are high. So I am planning to upgrade it to a dedicated serer. > > Here are two choice of my server: > > 1. Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM > 2. Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM. > > I know the best way to do this is to benchmark the two servers, but I can't > do that, can only pick one. Could anyone of you tell me which one is better > for higher MySQL performance, based on your experience? > > Thanks. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[email protected] > >
Re: More CPU or More RAM?
Thanks Martin, This is my current configuration. mysql> SHOW VARIABLES LIKE 'have_query_cache'; +--+---+ | Variable_name| Value | +--+---+ | have_query_cache | YES | +--+---+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'table_cache'; +---+---+ | Variable_name | Value | +---+---+ | table_cache | 4196 | +---+---+ 1 row in set (0.00 sec) in my.cnf, query_cache_size = 64 M and I have a total of 1281 tables On Wed, Apr 21, 2010 at 10:01 AM, Martin Gainty wrote: > tossing Hardware at it is a microsoft solution to take a implementation > offline while they find the REAL problem > > i would take a hard look at a few things: > 0)increasing table_cache > 1)have_query_cache to true > http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html > > Martin Gainty > __ > Jogi és Bizalmassági kinyilatkoztatás/Verzicht und > Vertraulichkeitanmerkung/Note de déni et de confidentialité > > Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy > jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése > nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi > alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen > megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet > tartalma miatt. > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene > Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte > Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht > dient lediglich dem Austausch von Informationen und entfaltet keine > rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von > E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. > > Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le > destinataire prévu, nous te demandons avec bonté que pour satisfaire informez > l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci > est interdite. Ce message sert à l'information seulement et n'aura pas > n'importe quel effet légalement obligatoire. Étant donné que les email > peuvent facilement être sujets à la manipulation, nous ne pouvons accepter > aucune responsabilité pour le contenu fourni. > > > > > > > > Date: Wed, 21 Apr 2010 09:31:15 -0500 > > > Subject: More CPU or More RAM? > > From: [email protected] > > > To: [email protected] > > > > I have a 1.5G database which feeds a CMS web application (Drupal). > > > > Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. > IO > > and CPU are high. So I am planning to upgrade it to a dedicated serer. > > > > Here are two choice of my server: > > > > 1. Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM > > 2. Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM. > > > > I know the best way to do this is to benchmark the two servers, but I > can't > > do that, can only pick one. Could anyone of you tell me which one is > better > > for higher MySQL performance, based on your experience? > > > > Thanks. > > -- > The New Busy is not the too busy. Combine all your e-mail accounts with > Hotmail. Get > busy.<http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4> >
Re: More CPU or More RAM?
This is my current my.cnf setttings. Could anyone take a quick peek and tell me if I set anything awfully wrong? [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 128M max_allowed_packet = 1M table_cache = 4196 open_files_limit = 1 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 64M query_cache_limit = 4M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_heap_table_size = 128M tmp_table_size = 128M max_connections = 100 join_buffer_size = 16M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout On Wed, Apr 21, 2010 at 10:04 AM, Johan Gant wrote: > I guess this is a DB list, but I strongly disagree with Johan's suggestion > to avoid using Views or Taxonomy. The advantages far outweigh the > disadvantages in most cases. > > > -Original Message- > From: [email protected] [mailto:[email protected]] On Behalf Of Johan De > Meersman > Sent: 21 April 2010 15:44 > To: [email protected] > Cc: [email protected] > Subject: Re: More CPU or More RAM? > > Switch CMSes, you'll be better off. I have the pain of running Drupal, too. > > Your DB host is probably good enough, unless you're doing insane amounts of > page views. What you need is Drupal optimisations. Here's just a few: > > - drupal keeps both it's sessions and cache in the DB. Change to memcache > - the views module is horrible. Get rid of it and write your own queries > - for pete's sake don't turn on the watchdog module, especially on debug. > That, too goes in your db > - avoid taxonomy - it does evil hiearchical queries > > et cetera ad nauseam :-) > > > On Wed, Apr 21, 2010 at 4:31 PM, [email protected] >wrote: > > > I have a 1.5G database which feeds a CMS web application (Drupal). > > > > Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. > IO > > and CPU are high. So I am planning to upgrade it to a dedicated serer. > > > > Here are two choice of my server: > > > > 1. Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM > > 2. Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM. > > > > I know the best way to do this is to benchmark the two servers, but I > can't > > do that, can only pick one. Could anyone of you tell me which one is > better > > for higher MySQL performance, based on your experience? > > > > Thanks. > > > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[email protected] > >
Re: More CPU or More RAM?
OK, let's get back to the original question. for a database like mine (1.5GB), will 4GB or 8GB RAM make any difference performance wise? On Wed, Apr 21, 2010 at 10:04 AM, Johan Gant wrote: > I guess this is a DB list, but I strongly disagree with Johan's suggestion > to avoid using Views or Taxonomy. The advantages far outweigh the > disadvantages in most cases. > > > -Original Message- > From: [email protected] [mailto:[email protected]] On Behalf Of Johan De > Meersman > Sent: 21 April 2010 15:44 > To: [email protected] > Cc: [email protected] > Subject: Re: More CPU or More RAM? > > Switch CMSes, you'll be better off. I have the pain of running Drupal, too. > > Your DB host is probably good enough, unless you're doing insane amounts of > page views. What you need is Drupal optimisations. Here's just a few: > > - drupal keeps both it's sessions and cache in the DB. Change to memcache > - the views module is horrible. Get rid of it and write your own queries > - for pete's sake don't turn on the watchdog module, especially on debug. > That, too goes in your db > - avoid taxonomy - it does evil hiearchical queries > > et cetera ad nauseam :-) > > > On Wed, Apr 21, 2010 at 4:31 PM, [email protected] >wrote: > > > I have a 1.5G database which feeds a CMS web application (Drupal). > > > > Right now I am hosting it with a 1.5G RAM VPS and I feel it is too slow. > IO > > and CPU are high. So I am planning to upgrade it to a dedicated serer. > > > > Here are two choice of my server: > > > > 1. Intel Pentium G6950 (Dual Core), 2xSATA Drive (no RAID), 8G RAM > > 2. Intel Xeon X3210 (Quad Core), 2XSATA drive (no RAID), 4G RAM. > > > > I know the best way to do this is to benchmark the two servers, but I > can't > > do that, can only pick one. Could anyone of you tell me which one is > better > > for higher MySQL performance, based on your experience? > > > > Thanks. > > > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[email protected] > >
Re: More CPU or More RAM?
Thanks Perrin, My web server is on the same box as the database server. I tried to use a mysql tuning-primer.sh to evaluate my server. and the result for key_buffer is KEY BUFFER Current MyISAM index space = 181 M Current key_buffer_size = 128 M Key cache miss rate is 1 : 12507 Key buffer free ratio = 73 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere for TEMP TABLES TEMP TABLES Current max_heap_table_size = 128 M Current tmp_table_size = 128 M Of 19206 temp tables, 31% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. this 31% really caught my eye. On Wed, Apr 21, 2010 at 10:26 AM, Perrin Harkins wrote: > On Wed, Apr 21, 2010 at 11:14 AM, [email protected] > wrote: > > This is my current my.cnf setttings. Could anyone take a quick peek and > tell > > me if I set anything awfully wrong? > > If your tables are MyISAM (not InnoDB), then 128MB is much too small > for your key_buffer. You should look at the sample my.cnf files that > come in the mysql documentation. Those will give you a better > starting point. > > And to answer your original question, in general, RAM is more useful > than anything else for a database. Whatever is using your CPU might > be work that could be pushed off to your web layer, but the data > access can't be. > > - Perrin >
