?
Solved

mySQL 5.1 performance bad after upgrade

Posted on 2009-04-21
22
Medium Priority
?
476 Views
Last Modified: 2013-12-12
i've upgraded to mysql 5.1 today...now the load on my box is high...can't figure out why it is like this.
moreover a table is "lost"...it's there but if i wanna repair it it says "table not found"

box is debian etch 64bit
0
Comment
Question by:KilianSSL
  • 10
  • 7
  • 3
  • +1
22 Comments
 
LVL 21

Expert Comment

by:K V
ID: 24192385
To start with analyze your  -  using different tools available like Mysqlreport or Mysqltuner
** http://hackmysql.com/mysqlreport
** http://wiki.mysqltuner.com/MySQLTuner

These will analyze and provide real good overview of your mysql for improving performance.

These are scripts help you out in understanding present status and also will suggest you the required changes.
Along with this -

In particular tuning you must watch out for variables:
- table_cache
- query_cache_size
- key_buffer_size
0
 

Author Comment

by:KilianSSL
ID: 24192422
the box has 4 GB of ram...this is what worked in 5.0 but appearently nomore in 5.1

safe-show-database
key_buffer              = 512M
max_allowed_packet      = 32M
thread_stack            = 128K
thread_cache_size       = 128
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 1000
table_cache            = 1024
thread_concurrency     = 8
low_priority_updates    = 1
0
 

Author Comment

by:KilianSSL
ID: 24192423
query_cache_limit       = 2M
query_cache_size        = 128M
query_cache_min_res_unit = 4K
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 29

Expert Comment

by:fosiul01
ID: 24193261
Hi ya
after upgrading , have you check if the file permission has been change in table ??

cd /var/lib/mysql

ls -l
check the fiel permission of the table .. table should be permitted by mysql
0
 

Author Comment

by:KilianSSL
ID: 24193288
have rechecked it now and the missing table is innodb with the corret permissions...
0
 
LVL 29

Expert Comment

by:fosiul01
ID: 24193302
which one is corrent

1. you dont have innoDB file ??

2. permission is not correct ??
0
 

Author Comment

by:KilianSSL
ID: 24193337
1. myisam tables are all there, 1 innodb table is there but not accessible (says "table does not exist), all other innodb tables work
2. permission is set correct on all db related files
0
 
LVL 21

Expert Comment

by:K V
ID: 24193361
do you have <tablename>.frm file existing under data directory?
0
 

Author Comment

by:KilianSSL
ID: 24193388
nope that one is missing... :(

regarding the load issue...
1. first load of the page = fast
2. going to a thread = fast
3. going back to home = 10s of wait (query runs slow)

some queries are using " WHERE threadid = 100101 AND message LIKE '%[url=%][img]%[/img][/url]%'"
but it worked fine with 5.0 so i can't get what's the prob...
0
 
LVL 21

Expert Comment

by:K V
ID: 24193407
well so Lost Frm is one problem.
Now regarding query performance, it'd be good if you post <EXPLAIN query>'s output!!
0
 
LVL 29

Expert Comment

by:fosiul01
ID: 24193412
do you have backp of those frm file ,,,, i am not that fimilier with innodb file , you need to restore those ( i gues it from mysql dump) but @theGhost_k8 might give you more idea ..
0
 
LVL 21

Expert Comment

by:K V
ID: 24193763
well for you recovering I've a link for you:
 http://www.chriscalender.com/?p=28

For performance: I still suggest you to observe - mysqltuner (if you think its config issue)
or Provide output of <EXPLAIN Query> for particular query.
0
 

Author Comment

by:KilianSSL
ID: 24193777
i've found a problem query...i dunno wether it's nice but it worked FINE with 5.0

EXPLAIN SELECT b . * , t.topic, i . * , (

SELECT message
FROM bb1_posts
WHERE threadid = b.lastthreadid
AND message LIKE  '%[url=%][img]%[/img][/url]%'
ORDER BY postid
LIMIT 1
) AS message
FROM bb1_boards b
LEFT JOIN bb1_threads t ON ( t.threadid = b.lastthreadid )
LEFT JOIN bb1_icons i
USING ( iconid )
ORDER BY b.parentid ASC , b.boardorder ASC

+----+--------------------+-----------+--------+---------------------+---------+---------+----------------------------+------+---------------------+
| id | select_type        | table     | type   | possible_keys       | key     | key_len | ref                        | rows | Extra               |
+----+--------------------+-----------+--------+---------------------+---------+---------+----------------------------+------+---------------------+
|  1 | PRIMARY            | i         | system | PRIMARY             | NULL    | NULL    | NULL                       |    0 | const row not found |
|  1 | PRIMARY            | b         | ALL    | NULL                | NULL    | NULL    | NULL                       |   21 | Using filesort      |
|  1 | PRIMARY            | t         | eq_ref | PRIMARY             | PRIMARY | 4       | babes-board.b.lastthreadid |    1 |                     |
|  2 | DEPENDENT SUBQUERY | bb1_posts | index  | threadid,threadid_2 | PRIMARY | 4       | NULL                       |    1 | Using where         |
+----+--------------------+-----------+--------+---------------------+---------+---------+----------------------------+------+---------------------+
4 rows in set (0.00 sec)


0
 
LVL 10

Expert Comment

by:mahome
ID: 24194003
Have a look in your tempdir:
tmpdir        = /tmp/mysql/

If files are there when executing the query, temporary tables are stored on filesystem and this is very slow. That can happen for filesort. If so increase the following variables:
tmp_table_size
max_heap_table_size

I had to adjust both to the same size. But I don't know exactly what's the difference between them. But the temp files were gone

0
 

Author Comment

by:KilianSSL
ID: 24194148
there is no file in that directory while the query is exectuted...

i guess it's about the subquery with the LIKE state %[url=%][img]%[/img][/url]%
without that subquery the page is fast...anyhow, with 5.0 this query didn't influence at all...
0
 
LVL 21

Expert Comment

by:K V
ID: 24194396
Are these all MyISAM tables?
what field length of 'message' ? Have you tried with creating index on that field?
0
 

Author Comment

by:KilianSSL
ID: 24194460
message is the actual "posting" content of a forum and it can be very long...
affraid of creating an index out of this...
0
 
LVL 10

Expert Comment

by:mahome
ID: 24194860
And index would be no help as it the where clause must not start with %. I would add a boolean column which reflects if %[url=%][img]%[/img][/url]% would match. This column can filled with a trigger or in the app.
0
 

Author Comment

by:KilianSSL
ID: 24194896
btw...if have found the reason...of slowing down the query...but it's so much odd...

if i remove ORDER BY postid in the subquery it's friggin fast again...like it was with mysql 5.0
is this a bug or a feature :-P


0
 
LVL 21

Expert Comment

by:K V
ID: 24195109
0
 

Author Comment

by:KilianSSL
ID: 24195901
sort_buffer_size = 64M
0
 
LVL 21

Accepted Solution

by:
K V earned 1500 total points
ID: 24196037
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question