Reputation: 2021
Performance issues mysql database
We manage a website for a client for us, this website is dealing with serious performance issues. This website is build within a CMS and is dealing with a maximum of 20 visitors at the same time. As long as the visitors of the website are the only users of the site, everything goes good. The CMS builds cache and the use of mysql query's is kept to a minimum.
The performance starts to lack when the employees of our client start to work on the site themselves. The whole workflow goes trough the site, this includes updating records in the database and inserting new ones. When records get update, deleted or inserted cache gets deleted so actions by visitors of the website will leed to query's.
My first question is should mysql be able to handle these requests ? Even though the design of the database is not good (see below). When i watch the processlist in mysql, i see simple querys taking up to 15 seconds. these querys only seem to take so long when there are update or insert querys going on at the same time.
below the big table that is holding all the records. everything used by the frontend of the website is stored in this one table. including binarys. The table holds a total of 8,676 records. The frontend has a lot of querys that uses a WHERE on for example Variabele5 or Variabele6 The value of these Variabele fields depend on the contenttype.
The WHERE is for example:
WHERE Variabele5 > 500 AND Variabele6 =2 AND contenttype = 35
The environment where all the employees work in does not get cached. In this environment there are a few lists with order and search options. These querys are memcached as much as possible, but when a record gets updated, deleted or inserted the memcache is cleared. This in combination with the insert, update and delete actions is probably the bottleneck.
So my question is should mysql be able to handle this all or is the design of the database to bad to handle the requests.
+---------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+----------------+ | nr | mediumint(80) | NO | PRI | NULL | auto_increment | | hidden | int(1) | NO | | 0 | | | Title | varchar(255) | NO | MUL | | | | Body | text | NO | | NULL | | | Description | text | NO | | NULL | | | user_id | mediumint(80) | NO | | 0 | | | addate | varchar(255) | NO | | NULL | | | moddate | varchar(255) | NO | | NULL | | | contenttype | mediumint(80) | NO | MUL | 0 | | | parent | mediumint(80) | NO | | 0 | | | Status | mediumint(80) | NO | MUL | NULL | | | CODE | varchar(255) | NO | MUL | NULL | | | menu | mediumint(80) | NO | | NULL | | | filetype | mediumint(80) | NO | | NULL | | | folder | int(11) | NO | | NULL | | | sortIndex | bigint(255) | NO | | NULL | | | servercache | int(1) | NO | | 1 | | | futurecache | int(1) | NO | | NULL | | | publishFrom | date | YES | | NULL | | | publishUntil | date | YES | | NULL | | | pinned | int(11) | YES | | NULL | | | keywords | text | NO | | NULL | | | latestversion | int(11) | NO | | NULL | | | permalink | text | NO | | NULL | | | Integer1 | int(12) | NO | | 0 | | | Integer2 | int(4) | NO | | 0 | | | Integer3 | int(4) | NO | | 0 | | | Integer4 | int(4) | NO | | 0 | | | Variabele1 | varchar(255) | NO | | | | | Variabele2 | varchar(255) | NO | | | | | Variabele3 | varchar(255) | NO | | | | | Variabele4 | varchar(255) | NO | | | | | Date1 | varchar(255) | NO | | | | | Date2 | varchar(255) | NO | | | | | Text1 | text | NO | | NULL | | | Text2 | text | NO | | NULL | | | Text3 | text | NO | | NULL | | | Binary1 | longblob | NO | | NULL | | | Binary1Type | varchar(255) | NO | | NULL | | | Binary2 | longblob | NO | | NULL | | | Binary2Type | varchar(255) | NO | | NULL | | | Binary3 | longblob | NO | | NULL | | | Binary3Type | varchar(255) | NO | | NULL | | | browseAccess | varchar(255) | NO | | NULL | | | Binary4 | longblob | NO | | NULL | | | Binary4Type | varchar(255) | NO | | NULL | | | Binary5 | longblob | NO | | NULL | | | Binary5Type | varchar(255) | NO | | NULL | | | Binary6 | longblob | NO | | NULL | | | Binary6Type | varchar(255) | NO | | NULL | | | Integer5 | int(11) | NO | | NULL | | | Integer6 | int(11) | NO | | NULL | | | Variabele6 | varchar(255) | NO | | NULL | | | Binary7 | longblob | NO | | NULL | | | Binary7Type | varchar(255) | NO | | NULL | | | Binary8 | longblob | NO | | NULL | | | Binary8Type | varchar(255) | NO | | NULL | | | Binary9 | longblob | NO | | NULL | | | Binary9Type | varchar(255) | NO | | NULL | | | Binary10 | longblob | NO | | NULL | | | Binary10Type | varchar(255) | NO | | NULL | | | Binary11 | longblob | NO | | NULL | | | Binary11Type | varchar(255) | NO | | NULL | | | Binary12 | longblob | NO | | NULL | | | Binary12Type | varchar(255) | NO | | NULL | | | Binary13 | longblob | NO | | NULL | | | Binary13Type | varchar(255) | NO | | NULL | | | Binary14 | longblob | NO | | NULL | | | Binary14Type | varchar(255) | NO | | NULL | | | Binary15 | longblob | NO | | NULL | | | Binary15Type | varchar(255) | NO | | NULL | | | Text4 | text | NO | | NULL | | | Text5 | text | NO | | NULL | | | Text6 | text | NO | | NULL | | | Text7 | text | NO | | NULL | | | Text8 | text | NO | | NULL | | | Variabele5 | varchar(255) | NO | | NULL | | | Variabele7 | varchar(255) | NO | | NULL | | | Variabele8 | varchar(255) | NO | | NULL | | | Variabele9 | varchar(255) | NO | | NULL | | | Variabele10 | text | NO | | NULL | | | Variabele11 | varchar(255) | NO | | NULL | | | Variabele12 | varchar(255) | NO | | NULL | | | Variabele13 | varchar(255) | NO | | NULL | | | Variabele14 | varchar(255) | NO | | NULL | | | Variabele15 | varchar(255) | NO | | NULL | | | Variabele16 | varchar(255) | NO | | NULL | | | Variabele17 | varchar(255) | NO | | NULL | | | Variabele18 | varchar(255) | NO | | NULL | | | Variabele19 | varchar(255) | NO | | NULL | | | Variabele20 | varchar(255) | NO | | NULL | | | Variabele21 | varchar(255) | NO | | NULL | | | Variabele22 | varchar(255) | NO | | NULL | | | Variabele23 | varchar(255) | NO | | NULL | | | Variabele24 | varchar(255) | NO | | NULL | | | Variabele25 | varchar(255) | NO | | NULL | | | Variabele26 | varchar(255) | NO | | NULL | | | Variabele27 | varchar(255) | NO | | NULL | | | Variabele28 | varchar(255) | NO | | NULL | | | Variabele29 | varchar(255) | NO | | NULL | | | Variabele30 | varchar(255) | NO | | NULL | | | Variabele31 | varchar(255) | NO | | NULL | | | Variabele32 | varchar(255) | NO | | NULL | | | Variabele33 | varchar(255) | NO | | NULL | | | Variabele34 | varchar(255) | NO | | NULL | | | Variabele35 | varchar(255) | NO | | NULL | | | Variabele36 | varchar(255) | NO | | NULL | | | Variabele37 | varchar(255) | NO | | NULL | | | Variabele38 | varchar(255) | NO | | NULL | | | Variabele39 | varchar(255) | NO | | NULL | | | Variabele40 | varchar(255) | NO | | NULL | | | Variabele41 | varchar(255) | NO | | NULL | | | Variabele42 | varchar(255) | NO | | NULL | | | Variabele43 | varchar(255) | NO | | NULL | | | Variabele44 | varchar(255) | NO | | NULL | | | Variabele45 | varchar(255) | NO | | NULL | | | Variabele46 | varchar(255) | NO | | NULL | | | Variabele47 | varchar(255) | NO | | NULL | | | Variabele48 | varchar(255) | NO | | NULL | | | Variabele49 | varchar(255) | NO | | NULL | | | Variabele50 | varchar(255) | NO | | NULL | | +---------------+---------------+------+-----+---------+----------------+
indexes:
+-------------+--------------+--------------+-------------+------+ | Key_name | Seq_in_index | Column_name | Cardinality | Null | +-------------+--------------+--------------+-------------+------+ | PRIMARY | 1 | nr | 8675 | | | Status | 1 | Status | 5 | | | Status | 2 | publishFrom | 8675 | YES | | Status | 3 | publishUntil | 8675 | YES | | CODE | 1 | CODE | 4337 | | | CODE | 2 | Title | 4337 | | | contenttype | 1 | contenttype | 30 | | | Title | 1 | Title | 2891 | | +-------------+--------------+--------------+-------------+------+
SERVER Information: 4x Intel(R) Xeon(R) CPU L5630 @ 2.13GHz
+---------------------------------------------------------------------------+ | free -mt | +---------------------------------------------------------------------------+ | total used free shared buffers cached | | Mem: 4096 3841 254 0 44 1223 | | -/+ buffers/cache: 2573 1522 | | Swap: 1023 422 601 | | Total: 5119 4263 856 | +---------------------------------------------------------------------------+
If i need to provide any additional information please let me know.
EDIT:Some example querys
The querys vary but here are some examples:
Search query for an employee:
EXPLAIN SELECT profiel.nr as nr, CONCAT(profiel.title,' ',profiel.Variabele49) as naam,profiel.Variabele3 as tel, profiel.Variabele44 as huurprijs, profiel.Variabele5 as inkomen, profiel.Variabele39 as personen, profiel.Variabele46 as perdatum, profiel.addate as inschrijving, profiel.text1 as opmerkingen, medewerker.Title as m_naam,profiel.Variabele48 as lang FROM site_content as profiel left join vw_activeContent as medewerker on medewerker.nr = profiel.Variabele9 WHERE profiel.contenttype =26 AND (profiel.Status=3 OR profiel.Text8='Nee') AND ( profiel.nr LIKE '%Rem%' OR profiel.title LIKE '%Rem%' OR ' ' LIKE '%Rem%' OR CONCAT(profiel.title,' ',profiel.Variabele49) LIKE '%Rem%' OR profiel.Variabele49 LIKE '%Rem%' OR profiel.Variabele3 LIKE '%Rem%' OR profiel.Variabele44 LIKE '%Rem%' OR profiel.Variabele5 LIKE '%Rem%' OR profiel.Variabele39 LIKE '%Rem%' OR profiel.Variabele46 LIKE '%Rem%' OR profiel.addate LIKE '%Rem%' OR profiel.text1 LIKE '%Rem%' OR medewerker.Title LIKE '%Rem%' OR profiel.Variabele48 LIKE '%Rem%' OR profiel.Variabele1 LIKE '%Rem%' OR profiel.Variabele3 LIKE '%Rem%' ) ORDER BY profiel.sortIndex
+----+-------------+-------+--------------+--------+----------------+--------------------+-------------+---+---------+--------------------+---+-------------+-----------------------------+-------+ | id | select_type | table | | type | possible_keys | | key | | key_len | ref | | | rows | Extra | +----+-------------+-------+--------------+--------+----------------+--------------------+-------------+---+---------+--------------------+---+-------------+-----------------------------+-------+ | 1 | SIMPLE | | profiel | | ref | Status,contenttype | contenttype | 3 | const | | | 1700 | Using where; Using filesort | | | 1 | SIMPLE | | site_content | eq_ref | PRIMARY,Status | | PRIMARY | | 3 | profiel.Variabele9 | 1 | Using where | | | +----+-------------+-------+--------------+--------+----------------+--------------------+-------------+---+---------+--------------------+---+-------------+-----------------------------+-------+
Search query visitor
EXPLAIN SELECT nr, title AS adres, Description AS description, Binary3 AS bin, Variabele2 AS
TYPE , Text3, Text2 AS verhuurd, Integer2 AS kamers, Integer3 AS personen, Variabele4 AS inclusief, Text1 AS oplevering, Integer5 AS huurpijs, Variabele6 AS wijk, moddate
FROM vw_activeContent
WHERE contenttype =22
AND Integer2 >=2
AND Integer3 >=1
AND Integer5 >380
AND Integer5 <770
ORDER BY Integer5 ASC
+----+-------------+-------+--------------+------+--------------------+-------------+-----+-------+---------+-----------------------------+------+-------+ | id | select_type | table | | type | possible_keys | | key | | key_len | ref | rows | Extra | +----+-------------+-------+--------------+------+--------------------+-------------+-----+-------+---------+-----------------------------+------+-------+ | 1 | SIMPLE | | site_content | ref | Status,contenttype | contenttype | 3 | const | 696 | Using where; Using filesort | | | +----+-------------+-------+--------------+------+--------------------+-------------+-----+-------+---------+-----------------------------+------+-------+
query that is widely used:
EXPLAIN SELECT DISTINCT Variabele2
FROM site_content
WHERE contenttype =22
AND STATUS =1
ORDER BY Variabele2 ASC
+----+-------------+-------+--------------+------+--------------------+-------------+-----+-------+---------+----------------------------------------------+------+-------+ | id | select_type | table | | type | possible_keys | | key | | key_len | ref | rows | Extra | +----+-------------+-------+--------------+------+--------------------+-------------+-----+-------+---------+----------------------------------------------+------+-------+ | 1 | SIMPLE | | site_content | ref | Status,contenttype | contenttype | 3 | const | 696 | Using where; Using temporary; Using filesort | | | +----+-------------+-------+--------------+------+--------------------+-------------+-----+-------+---------+----------------------------------------------+------+-------+
Upvotes: 0
Views: 230
Answers (2)
Reputation: 29629
Firstly, your database is a mess, as you said. However, given your hardware, and the relatively small size of the data, I would expect the queries to be pretty fast.
If the queries are slow, it's likely because of the wildcards on the "where" criteria - profiel.title LIKE '%Rem%' for instance. You could replace that with full text searching.
Another design issue is storing binaries in the database; depending on the size of the binaries, this can have a big impact on disk IO performance, and - usually - the encoding/decoding of the binaries (which often requires the database connection to be kept open) can be significantly slower than just reading text/numbers from the result set. Consider caching the binaries on the file system, and only go to the database for them if you know they've changed.
However, before you dig into the database, I think you need to find a way of nailing down the performance problem, so you know you're fixing the right problem. Consider using a profiler to find the bottleneck, and then work on the specific problem.
Upvotes: 0
Reputation:
From the only query example you have given, Variabele5 and Variabele6 are not indexed and there are probably lots of records with contenttype = 35, so queries are probably not using indexes very efficiently. More queries examples might help us get a better picture.
However, the table size doesn't seem very large, that makes me think the actual rebuilding of the cache might be the actual cause. So you might want to look at the caching policies used in the application. I don't know how much testing you might be able to do, but I'd try testing your scenario with caching turned off.
Upvotes: 1
Related Questions
- Database speed optimisation (multiple queries)
- MySQL Query Optimization?
- MySql performance issues in queries
- MySQL and PHP performance of multiple queries
- PHP and MySQL: optimize database
- Performance Issue On MySQL DataBase
- php mysql optimization
- MySQL performance issue
- mysql performance question
- Need Help regarding Optimization